Looping Duplicate Validation

[Charlie Brown]

Using Access 2003

I am using a form with 6 combo boxes for selecting names. The controls work
as they should to select the names, but I need to do two things before I save
the record;

1. Check there are a minimum of two names chosen in the first two combo boxes
2. Check that all combo boxes with names selected are not duplicates.

I have not been able to figure out how to loop through these combo box
control types to check them for duplicates. Thanks in advance for any
suggested code for this.

 

[Charlie Brown]

Since the combo box selections are from a query in the Row Source property
which pulls from a table with employee information, I found that if I add <>
to the subsequent combo boxes query to filter the previous box's value, the
user can not select the same name again.

Here's what the code looks like;
- First Box (strApproval1) -
SELECT tblEmployees.strName
FROM tblEmployees
WHERE tblEmployees.strAccess < 3;

- Second Box (strApproval2) -
SELECT tblEmployees.strName
FROM tblEmployees
WHERE tblEmployees.strAccess < 3
AND tblEmployees.strName <> [strApproval1];

The <> adds the additional filter on the list. I continued to extend the
query statement in each combo boxes until [strApproval1] through
[strApproval6] were complete.

- Last Box (strApproval6) -
SELECT tblEmployees.strName
FROM tblEmployees
WHERE tblEmployees.strAccess < 3
AND tblEmployees.strName <> [strApproval1]
AND tblEmployees.strName <> [strApproval2]
AND tblEmployees.strName <> [strApproval3]
AND tblEmployees.strName <> [strApproval4]
AND tblEmployees.strName <> [strApproval5];

To keep the lists updated I added a me.refresh in the afterupdate event of
each combo box.

If anyone has a better solution to this, or if you see any potential
problems, please let me know.