Prevent new data if record is in a list box

[gravedigger]

On one of my form I have a list box that is filtered, is it possible to prevent new record from being added if the record would create a duplicate in the list box? one option is to index this field however this then applies to the entire database. I would like it to be for the filtered data only.

[HiTechCoach]

On one of my form I have a list box that is filtered, is it possible to prevent new record from being added if the record would create a duplicate in the list box? one option is to index this field however this then applies to the entire database. I would like it to be for the filtered data only.

I would use the Form's Before Update event to test to see if the record already exists in the list box.

You could also use the after update event of the control that would have the data that would cause the duplicate to run the validation code to look for a duplicate.

I would normally open a recordset using the same criteria that filters the listbox plus the data that you want to see if is a duplicate. If the recordset has a record, then it would create a duplicate.

[gravedigger]

What I ended up doing was make an index using the table foreign key and the field I didn't want the duplicate in.

I would use the Form's Before Update event to test to see if the record already exists in the list box.

You could also use the after update event of the control that would have the data that would cause the duplicate to run the validation code to look for a duplicate.

I would normally open a recordset using the same criteria that filters the listbox plus the data that you want to see if is a duplicate. If the recordset has a record, then it would create a duplicate.