Full Version: Filtered ComboBox Options
UtterAccess Forums > Microsoft® Access > Access Forms
EAnderson
Questions, questions, questions. This should hopefully be my last relating to forms, then I'll need to start learning about more advanced Queries and Reports!
I have a table with a composite key:
Client_ID
Support_Worker_ID
A sub form displays all records who's Client_ID matches that being displayed in the main form.
One client may have many support workers.
Problem: A record exists such as Client_ID = 1 and Support_Worker_ID = 1
If a user attempts to create a duplicate record, perhaps by mistake, by using a combo box containing all support worker ID's then errors occur regarding duplicate records, not allowing NULL primary key values or referential integrity problems if an invalid Support_Worker_ID is selected.
Is it therefore possible to filter the available options in the ComboBox? I.e. if a record already exists then the Support_Worker_ID is either removed from the ComboBox or is unselectable.
I am guessing I will need to use a query of some sorts but I'm really not sure where to start.
dashiellx2000
Actually, the rowsource of a combo box is really already a query. You just need to figure out the criteria to eliminate those records you don't want.
On form design view, select your combo box. Then on the Data Property tab, you will see a line for rowsource. Click into this line. A Builder ICON will appear on the right of the rowsource line. Click this and the rowsource will be opened into the query design grid. Design your query, close it, click yes to save and you'll have it.
You may need to requery the combo box after adding a new record in the subform so that any recently added support workers will not show in the combo.
HTH.
EAnderson
Ah great, a lot simpler than I'd thought. Thanks.
dashiellx2000
You're welcome. Glad I could help.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.