Trouble with Subfrom Validation Rule

O

OmahaAccess

I'm not for sure if I will explain this well enough to understand but I will
try.
I am making a sewer bypass record database. I have many bypasses, each
bypass has many details. One of the details is Mitigation Steps. There are 3
possible Mitigation Steps. Each bypass can have: jet line, saw line, or
remove debris. The bypass may have just 1 Mitigation step or it can have 2
or 3. It cannot have the same step twice or cannot have an option that is
not in the list. I have a subform within a form through a many to many
relationship. I have the junction table to set that relationship up. I get
the subform to work fine, other than getting some validation rules set up to
make it so one option cannot equal another option and only 3 options can be
selected. Maybe its not a validation rule that I need? Anyone have any
ideas? Thanks
 
K

Ken Sheridan

You can control this at least in part at the level of the logical model. If
the Mitigation Steps table has just the three rows then make the primary key
of the table which models the many-to-many relationship between this and the
Bypasses table (the 'junction' gable) a composite one made up of the two
foreign key columns which reference the keys of the two referenced tables.
This will then prevent duplicate entries by virtue of the consequent key
violation, which in turn will limit the number of rows per bypass to three as
any more would mean there has to be a duplicate.

If the Mitigation Steps table has more than three rows, i.e. it also has
rows which don't apply to bypasses but to some other entity type, then you
can restrict the items in the list of the mitigation steps combo box on the
subform to the three which do apply by means of a suitable criterion in the
control's RowSource query's WHERE clause, e.g. ….WHERE [Mitigation Step] IN
("jet line", "saw line", "remove debris"). Setting the combo box's
LimitToList property to True (Yes) will prevent the user typing in a value
not in the list.

If a user tries to enter duplicate a row in the subform this will raise a
data error. You can either accept the default error message, or can
customize it by means of some code in the subform's Error event procedure,
popping up your own message box and setting the return value of the event's
Response argument to acDataErrContinue to suppress the system generated
message.

Ken Sheridan
Stafford, England
 
Top