Removing previously selected items from drop down list

[celito]

Is it possible not to show anymore in the drop down list items already selected in previous records? Or may be at least dim it, to indicate that it is no longer a choice.

[Rod]

I think you will find that selectively changing the format of list items is well nigh impossible although I do have at the back of my mind that I read about some method to do this many years ago. Anyway I have never used it and thus forgotten what I read.

However eliminating list items should be straightforward provided that you can easily and explicitly identify which items are to be dropped. So what exactly do you mean by:

items already selected in previous records

What I have in mind is that you have to identify an appropriate event in your processing cycle and at that point requery your combo/list box. The Row Source of the combo/list box has to be written such that it drops previously used values. For example if you are using an SQL statement as your row source it would have the general pattern of

SELECT ID, ....
FROM ....
WHERE ID NOT IN(IDs of items already selected in previous records);

[celito]

Thanks Rod,
I have for example a drop down list of Room Numbers from 1 to 10. Once I assign Room No. 1, I want it to be excluded the next time I assign Room Numbers. The drop down list should now show only Room Nos. 2 to 10, or at least have Room No. 1 dimmed, if possible. I hope you could give me the exact expression or code. Thanks.

[Rod]

For me to give you the exact code, I shall need to see your accdb.

IMO it's not enough to drop the list row when you select it. What if you change your mind? How long does a room remain occupied? Should the room reappear on the list when the room becomes vacant?

As I warned in my first post, forget trying to dim the row.

[celito]

I'm sorry but that's a valid concern, once the room is again vacant. Would it have been easier to just change the status of the room, say from "Available" to "Not Available" based on a condition involving two tables? This is what I wanted in the first place. I previously posted the following in the general access forums.

I have two tables, Activity and Space. Space contains two fields, Number and Status. The default value of Status is "Available". The table Activity also contains a Field called Number, which is related to the same field in the table Space. How can the value in Status (table Space) change to "Closed" once the fields Number in both tables match (meaning the number in Space has been chosen in the Number drop down list in the table Activity)?

Any help will be greatly appreciated.

[orange]

There is a video tutorial at http://www.datapigtechnologies.com/f...tboxtrick.html that shows a technique to put entries into a listbox, and to remove them. It's free and well worth watching.
If it isn't what you need,please describe your situation in more detail with some data.

Good luck.

[celito]

Thanks so much for the lead. It looks promising and I will study it. Will let you know.