Solved Combobox to return the values from a table that haven't already been used and exclude the values that have been used

[klsblues]

I have a table that stores rooms (i.e. Bedroom1, Bedroom2, Living Room, Bathroom, etc.) it has the primary key as the room number (autonumber) and I have 2 tables to store information about the room.

tblEstMain holds the main room information
tblEstSub holds the individual items listed for the room
tblRoomDesc holds the room descriptions

I have a form that holds the tblEstMain information with a subform that lists the items for each room. it all works well, but...

The question I have is that the dropdown box on the main form that selects the room (from the tblRoomDesc), lists all rooms, but I want it to exclude any room numbers already used.

So, if I've already added items to rooms 1, 3 and 7, I want to omit them from the dropdown box. Is this possible?

Thanks in advance

 

[Gasman]

Not sure of your logic here?
What happens if you want to add another item to a room that alreday has items?
You could use a sub query and NOT IN ()

 

[klsblues]

Hi Gasman,

Thanks for the reply. I have 2 dropdowns - 1 labelled 'Add a room' and 1 labelled 'Edit a room' and if the user selects edit a room, it only lists the rooms already added (I did this because there are 28 variations to rooms and it saved them scrolling down).

It's actually the opposite of edit a room that I need the Add a room to list!

If I use NOT IN () what do i put in the brackets?

Thanks again.

 

[jdraw]

Here's a link to a youtube video show a listbox trick for adding/removing items from listbox. Iy may offer a method or at least some insight to a solution.

 

[Gasman]

SELECT AutoNumberID from tblRoomDesc

 

[klsblues]

Hi both,

I have solved it using 2 queries.

1st query (query1) links tblRoomDesc to tblEstSub (using the room number) I used the form to populate the criteria [SubMainEstID] with the ID Number for the record I want and this returns the 6 rooms used for that record.

2nd query links query1 to tblRoomDesc (again using the room number) and this lists all the rooms with the [SubMainEstID] field completed if the room has been used or if it hasn't been used, the field is empty .

In the [SubMainEstID] criteria I added Is Null.

This then returned all rooms that didn't have [SubMainEstID] used and I used that query for my drop down.

Hope this makes sese and thanks again for your help.