You can prevent the same employee from being chosen twice by applying a
unique index to each of the fields Clerk, Door, Clean-up, and Charge. The
employee's name would still appear in the combo box, but you wouldn't be
allowed to select the name. You don't say (that I can see) if you are
storing an EmployeeID number rather than the name, but you should be storing
an unchanging number in Clerk, Door, etc., using something like the table
structure for tblEmployee that Steve suggested. The following assumes you
are storing an employeeID number in the four fields.
One possibility for a new record is to enable only one combo box (e.g. Clerk)
so that it must be selected first. To do this, in the form's Current event:
Me.cboDoor.Enabled = Not Me.NewRecord
Me.cboCleanUp.Enabled = Not Me.NewRecord
Me.cboCharge.Enabled = Not Me.NewRecord
If it is a new record, NewRecord is True, so Not Me.NewRecord is the same as
False.
The combo box cboClerk is always enabled. Its Row Source is whatever you are
using now. In its After Update event:
Dim strSQL as String
Dim lngClerk as Long
lngClerk = Me.cboClerk
strSQL = "SELECT [EmployeeID], [FirstName],[LastName] " & _
"FROM tblEmployee " & _
"WHERE [EmployeeID] <> " & lngClerk & _
" ORDER BY [LastName], [FirstName]"
Me.cboDoor.RowSource = strSQL
Me.cboDoor.Enabled = True
In the After Update for cboDoor you need to add another variable:
Dim strSQL as String
Dim lngClerk as Long, lngDoor as Long
lngClerk = Me.cboClerk
lngDoor = Me.cboDoor
strSQL = "SELECT [EmployeeID], [FirstName],[LastName] " & _
"FROM tblEmployee " & _
"WHERE [EmployeeID] <> " & lngClerk & _
" AND [EmployeeID] <> " & lngDoor & _
" ORDER BY [LastName], [FirstName]"
Me.cboCleanUp.RowSource = strSQL
Me.cboCleanUp.Enabled = True
This would not change the Row Source for an existing record, so if you want
to go back to a record and change a name it would be different coding. It
seems to me it would be more difficult then, in that if you want to swap
names (Clerk and Door, for instance) you would first need to clear cboClerk,
then change the name in cboDoor, then update cboClerk.
Also, if you change the RowSource you need to change it to something else
when you go to another record, or the list will remain as you set it. It
depends on what you want to see in the list when you go to an existing record.
If you want the list exclude the selected people in that record you would
need to set the Row Source in the form's Current event. However, then you
would not be able to swap two of the names as described above unless you make
allowance for that in the coding. It is manageable enough for a new record,
but for an existing record you need to make a number of choices about how to
proceed.
There may be a cleaner approach than this, but this is what I came up with.
Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
door, clean-up, and charge ( a total of 4 fields) that I want to share the
same list (my list of employees). So if I choose an employee for field one
(clerk), when I go to field two - door, that employee's name won't appear on
the list any longer? Wow? I looked up combobox in my access book and it
doesn't tell me how to remove that employee from the row source...this is
great news if I can really do this, but can you explain a little bit more?
Is there an option on combobox that I choose to make this happen?
Thanks, Buddy
You need to use a form for choosing employees and their positions. On your
form use a combobox to select an employee and setup the combobox so that
[quoted text clipped - 18 lines]
