I want the user to select 1 item per record from the
listbox (example: 10 items in the list, 10 records
total.) If the user re-selects the same item again as new
record 11, there should be a warning message saying the
item has already been selected.
One way to *ensure* this limit is to put a multi-field unique Index on
the subform's table: if the DatePeriod, OfficeNumber and whatever
field the listbox fills have a three-field unique index the user won't
be able to add a duplicate.
Alternatively, you can put VBA code in the Listbox's BeforeUpdate
event to detect an attempt to add a duplicate:
Private Sub lstMyListbox_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[fieldname]", "
", <criteria>) Then
MsgBox "Please select each item only once",vbOKOnly
Cancel = True
End If
End Sub
where <criteria> searches for a duplicate, with
[Forms]![mainform]![subform].Form!lstMyListbox
as one of the criteria.
If you want to get fancy, you can instead base the Listbox on a query
which excludes values already selected, and requery it in its own
afterupdate event.