Hi

I have a table where a customer creates a new order. I have a subform in that order form containing a combo where the data comes from a table containing all the items they can choose from, then choose the quantity they want and then hopefully choose another item from the new combo box (the subform is in datasheet view). What I would like to do is if the user has chosen one of the items, that particular item would disappear from the dropdown, meaning they couldn't choose it again - unless they create a new order.

At the moment, if the user chooses the same item twice, a textbox appears saying they can't do that as it would create duplicate values in the primary key...... etc

Is this possible? Or is there a different workaround.

Many thanks

Mark.

Jack,

You 'could' do that by adding a 'yes/no' field into your table, and in the After-Update event of your combo-
box selection, have an update query make the yes/no = to Yes (-1).

In your code of the combobox selection add the code to only select items which are currently at 'No/0'.

I have utilized something like this in the past for a few clients, works well so far.

Good luck,

Aqua

Hi Aqua

Thanks for that, I'll have a go.

Jack.

Hi Aqua

OK, I had a go but am not sure how to do the on change bit. I've put a yes/no field in my order but am not sure how to go about the update the yes/no field on change. I've zipped the db and attached it for info.

If you could point me in the right direction, it would be most appreciated.

Thanks

Mark.

Hi

Put this question out there yesterday and had a flurry of interest but only one reply - anyone else come across this little dilemma before?

Cheers

M.

Jack,

Sorry for the delay, running around with a 4 yr. old can get hairier then programming!
In the last line of code in you combobox's 'After-Update' event have:

Docmd. openquery "YourUpdateQueryNameGoesHere"


That will update the checkbox from 'No' to 'Yes' provided you set up the update query correctly.
I use it all the time.

Good Luck,

Aqua

Hi Aqua

Thanks for your reply - programming and 4 year olds, a scary mixture!

I think I'm missing something in my query, I tried this but nothing seemed to happen. I tried changing the query for the combo but it started to return multiple entries of data, which wasn't right at all. I put the openquery command in the after update. I've attached the db again, modified from the last one, with the changes in you suggested. The combo is actually part of a subform called Order Details.

There are a number of db's I've designed and am designing where this feature would be excellet.

Would you mind having a quick look and point me in the right direction please?

Many thanks

J