Looping Duplicate Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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