Filtered dropdown list

[acrumbacker]

I have two tables: attendee and seating.
The seating table has the fields:
[TableID], [Tablenum], [Seatnum], [AssignID]
As tables are set up, the records for each seat are populated in the seating table.

This is for a fundraiser where the amount of tables that are set up will depend on the number of attendees that preregister and the possibility of others showing up the night of the event. The more people, the more dining tables will be set up.

With that background, to make assignments, I would like to use the seating table and then have a dropdown of non-assigned attendees show up as potentials in my list. So,in the seating table, [tablenum]= 1, [seatnum] = 1 I click on the dropdown and (lets say) I have 20 options. I select person A. I proceed to the next record and click on the dropdown of attendees who are potential assignees. This time, person A is not visible because they have been assigned leaving me with 19 options. I hope this makes sense.

I am using Access 2010. There might be a better way to do this and any suggestions welcome! Conceptually I know what needs to happen but I rarely work in Access and things have gotten <very> rusty. Thanks!

[Patrickwwevans]

This should be pretty easy by using a query for your data source on the combo box / drop down. Just use the event After Update to do a me.ComboBox.requery for the combo box of attendees. SO the query feed the combo box has the criteria where their ID is NOT in the table for folks selected. You could do this with an outer join from your main table that gets the Table number and Assignee number then on the Assignee to Assignee ID do where is null (meaning there record does not exist) in the main table - Hope that is not confusing.

[acrumbacker]

Is there an example out there to demonstrate this? I created two queries: one showing tables without assignees and another that included the two tables that then displayed only those individuals unassigned. How to automatically link this in a datasheet view is leaving me stumped.
UPDATE: The dropdown I have works in the query mode but there is not an option to apply an action. I have to hit the F5 key every time I enter the new value. Here is a link the the file:
[z{Moderator Edit{Redacted External link to cloud drive - External links to such files are not stable to the thread; thus not helpful in the long term}]

[Patrickwwevans]

Here is the sql statement based on those two tables. I set up the first exactly like you have it. The second table, the Assignee table I assume would be a "person or people" table and have a unique ID for each person and then stuff like name, etc.

[code]

SELECT tbl_Seating.TableID, tbl_Seating.Tablenum
   , tbl_Attendee.AssignID
FROM tbl_Seating 
   LEFT JOIN tbl_Attendee 
      ON tbl_Seating.AssignId = tbl_Attendee.AssignID
WHERE (((tbl_Attendee.AssignID) Is Null));

[acrumbacker]

Thanks! That worked; most helpful!