Combo box filter

[torquay]

Hope you can help

I have a combo box on a form based on a field (serialnumber) in a table as below. All numbers are unique and once chosen will never be used again. How can i hide the numbers in the combo box that have been used before? i. e. when the code 1001 has been used all you see the next time the combo box is clicked is 1002 thru 1005

1001
1002
1003
1004
1005 etc

Thanks

 

[DCrake]

Create an unmatched query between the table that holds the numbers and the table that has the numbers issued against them. Then use this as your underlying rowsource for your combo box.

But what happens if the combo box lists

1002
1003
1004
1005
etc

and the user picks 1003? Surely they should be picking 1002? Do you think that your approach is correct under these circumstances? The program should be selecting the number not the user.

 

[gemma-the-husky]

you could have a yesno flag in the serial number table, identifying "used" numbers, and just have the cbo box show those that are not marked as used.

 

[torquay]

Thanks you for your replies.

Each one of the numbers 1002, 1003 etc are the short serial number of products at an auction so the products are sold in lot sequence not serial number sequence.

Each product sold has a manufacturing model number which can be the same as in 2 pieces of furniture from the same manufacturer but we have to uniquely identify each piece so we are giving it our own unique number which will keep going up in value.


If the product isn't sold in this weeks auction it will go into a future auction but will still carry the same unique number.

 

[stopher]

It sounds like you are wanting to assign a new unique number. In which case, as David said, do you really need to leave it to the user to select the next number?

Instead, when a new record is created, set the lot number to be the next number after the last one used. This is easily achieved using DMAX to find the previous highest number and adding 1.

hth
Chris

 

[torquay]

The unique number is assigned before the auction starts as each product has to have this attached due to some products even with the same manufacturers code or model number are slightly damaged and this makes them unique plus we have to make specific notes on each product so each one has this unique number.
When the auction is over we know item they have purchased by serial number.
You may say you would know this by the lot number....and yes we do, but the lots start at 1 thru how ever many and again next week they start at 1 again.
The issue I am having is when the customer buys the product from us and we are adding this to an invoice the products are listed by our serial number (1002, 1003 etc) but what I would like to do is when the next customer comes to pay for thier goods the previous serial numbers are not showing up in the combo box.
By the way this is a physical auction, not on line.
Thanks