Preventing duplicate selection of combo box values

[swb31763]

I am working on a database to track IT assets with third parties. I have a table called ‘Equipment’ that includes info like model, serial numbers, purchase price, date, location, and ‘Asset ID’. I have a second, single field table called ‘asset tag’ that is just a list of asset ID tags, ‘XYZ1000’, XYZ1001’, ‘XYZ1002’…..


I created a one to one relationship between the two tables on the following fields: ‘equipment.assetID’ and ‘asset tag.asset ID’


Once an ‘asset ID’ is used, I would like it to either be grayed out or disappear from the list of available ID tags. Basically, I want it so that each ‘asset tag. Asset ID’ can only be used once.



thanks for any information or recommendations
swb31763

[June7]

You save assetID into Tag table? Why not the tag into Equipment table?

Not sure the tag table is even needed. Next available tag can be determined by searching for the last used tag in Equipment table. That's how I manage generation of unique identifier. What do you want to do when you reach 9999 assets?

[swb31763]

You save assetID into Tag table? Why not the tag into Equipment table?

Not sure the tag table is even needed. Next available tag can be determined by searching for the last used tag in Equipment table. That's how I manage generation of unique identifier. What do you want to do when you reach 9999 assets?

I have a duplicate selection problem figured out, simply indexed the field without duplicates

I use the asset tag table as a combo box lookup.

shouldn't have to worry about going over 9999 items

the more I work on this and research, there should be a way to create a filter or query to strip out the previously used asset tags when listing a new piece of equipment.

[June7]

If the number is programmatically generated, there is no need for combobox to select next ID

As comparison, in my db I programmatically assign an ID to each sample submitted to our lab. This ID is unique in the SampleLogin table in the form of yyyyA-####. I do not have a separate table of just the sample IDs.

However, for what you want, create a query that joins the two tables on the tag fields. Join type "Include all records from Tags and only those from Equipment that match". Use filter criteria: Equipment.Tag Is Null