Stumped on Checking for Duplicates across many Comboboxes

  • Thread starter Thread starter 6afraidbecause789
  • Start date Start date
6

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.
 
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
 
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
 
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.
 
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.
 
Back
Top