How not to select duplicate records in a listbox.

[F. N'Jie]

I have a listbox where the user selects only one value at
a time. The problem is that once the user selects the
value and the record is updated in the table, they can
reselect the same value again. I do not want this to
happen. Your input is greatly appreciated. Thanks

 

[John Vinson]

I have a listbox where the user selects only one value at
a time. The problem is that once the user selects the
value and the record is updated in the table, they can
reselect the same value again. I do not want this to
happen. Your input is greatly appreciated. Thanks

What is the Form's Recordsource, and the listbox's Rowsource? How are
the tables related? Do you mean that you don't want the user to select
the value in the same record (which would seem to be a do-nothing
operation), or to select a value from the lookup table (the listbox)
only once and then never again?

 

[Fatou N'Jie]

The form is a subform and the Recordsource is a query.
The listbox is in the subform and the row source is a
select query (lookup table).
For each 1 record in the main form, there should be 10
records in the subform and the linking fields are date
period and office number.

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.

I hope this has clarified my question. Thanks for you
help.

 

[John Vinson]

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.