By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,560 Members | 2,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,560 IT Pros & Developers. It's quick & easy.

Filtered dropdown list

P: 3
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!
Nov 15 '13 #1
Share this Question
Share on Google+
4 Replies

P: 8
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.
Nov 15 '13 #2

P: 3
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}]
Nov 18 '13 #3

P: 8
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.


Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Seating.TableID, tbl_Seating.Tablenum
  2.    , tbl_Attendee.AssignID
  3. FROM tbl_Seating 
  4.    LEFT JOIN tbl_Attendee 
  5.       ON tbl_Seating.AssignId = tbl_Attendee.AssignID
  6. WHERE (((tbl_Attendee.AssignID) Is Null));
Nov 19 '13 #4

P: 3
Thanks! That worked; most helpful!
Nov 19 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.