Stumped on Checking for Duplicates across many Comboboxes

[6afraidbecause789]

Hello - there are 5 comboboxes in a subform from which users select
multiple staff involved in an incident. When a user selects a staff
person for one of the comboboxes, I'd like the name to be excluded or
filtered out of the other comboboxes. Or--and possibly better yet--
would be a way to check for duplicate StaffIDs right before saving the
subform record (becuase 1 staff person may have 2 titles or roles in
the incident) and msgbox the user if the duplicate Staff is
legitimate. Note that any one of the fields may be null.

cboStaff1
Control Source = Staff1ID

cboStaff2
Control Source = Staff2ID

cboStaff3
Control Source = Staff3ID

cboStaff4
Control Source = Staff4ID

cboStaff5
Control Source = Staff5ID


Staff1ID through Staff5ID are separate fields in the underlying table
named "Incidents", and are foreign keys to StaffID in a table named
"Staff."

But, the row source of each combobox is currently occupied with some
interesting code that I found online that puts an "Add New Staff"
option at the top of each combobox's list, reducing the number of
buttons needed on the form. Row Source = SELECT -999 as StaffID,
'<Add New Staff>' as StaffName, 'aaa' as StaffLastName, 'x' as
StaffFirstName, 'Ext.' as StaffExtension FROM qryStaff UNION SELECT
StaffID, StaffName, StaffLastName, StaffFirstName, StaffExtension FROM
qryStaff ORDER BY StaffLastName, StaffFirstName
Column Count = 5
Bound Column = 1
(This fires in the afterupdate when a user selects the "Add New
Staff")

Hope this makes sense. Thanks for any insights.

 

[Allen Browne]

The table you describe is not set up correctly.

It sounds like one incident can have several staff involved. In a relational
database that should be modelled as a one-to-many relationship. (In all
probability, one staff member could be involved in multiple incidents to, so
it's really a many-to-many relationship.)

Presumably you already have a Staff table, with a StaffID primary key.
Now you have an Incident table, with fields such as:
IncidentID AutoNumber Primary key
IncidentDate Date/Time
IncidentDescrip Text Description of the incident

You now need a 3rd table, where you link up the staff to the incident, so
this IncidentStaff table will have fields like this:
IncidentID Number Relates to Incident.IncidentID
StaffID Number Relates to Staff.StaffID
So, if there are 3 staff in an incident, there are 3 *records* in this table
for that incident number.

For a primary key, select both fields in this table, and click the Key icon.
The key is the combination of the 2 fields, and so you have solved your
problem of not allowing the same staff member 2ce in the one incident.

For an interface you will want to create a main form bound to the Incident
table, with a subform bound to the IncidentStaff table. The subform will be
in continuous view, so you can add as many *records* as you neede staff in
the incident. Use a combo in the subform for the StaffID.

Hopefully this will get you going with core functionality of a relational
database. It's *so* much easier to do it right. For example, to find all the
incidents that Jim Smith was involved in, you have just *one* field to
search instead of 5.

Other examples of the same principle:
http://allenbrowne.com/casu-06.html
http://allenbrowne.com/casu-23.html

 

[6afraidbecause789]

Thanks for responding Allen,

I do appreciate this info, but am aware of the flat structures,
different norms and relationships--trust me on this--the rest of the
dbase is relational. There will not be much data in this table--only
numeric and binary, so it will not use much space. I am still
interested in how to solve the above described problem. Thanks
again. Nick

 

[Allen Browne]

To do the job badly, add a validation rule to the table, such as:

([Staff1] <> [Staff2]) AND
([Staff1] <> [Staff3]) AND
([Staff1] <> [Staff4]) AND
([Staff1] <> [Staff5]) AND
([Staff2] <> [Staff3]) AND
([Staff2] <> ...

That gets messy where you have to handle Nulls as well.

Alternatively, use the BeforeUpdate event procedure of the Form (not
controls) to run code and compare the values.

 

[6afraidbecause789]

hmmmm, maybe I will take the time to make a whole new join. It's not
fun to add to dbases once they're already made. Thanks again.