Adding a Value to a Combo Box

[JerSand]

I'm attempting to build a fairly simple two-part set of cascading combo boxes. I have constructed two tables -- agencies and subagencies -- with the following SQL (creating a query called "qryAgencies&quot:

SELECT DISTINCT [Agencies].[Agency], [Agencies].[AgencyID], [SubAgencies].[SubAgency]
FROM Agencies INNER JOIN SubAgencies ON [Agencies].[AgencyID]=[SubAgencies].[AgencyID];

This query yields 133 combinations of agencies and subagencies.

Then, I constructed a form to write to a table I've named "DataRepository" and which holds an agency field, a subagency field, and some other descriptors of each observation. To enter a value in the agency field, I've constructed a combo box called "ComboAgencies" and used the following SQL as the row source:

SELECT DISTINCT [QryAgencies].[Agency] FROM QryAgencies;

This renders 12 agencies from which to choose. Based on the selection in that first combo box, a second combo box (called "ComboSubs&quot then restricts the possible choices to the selected agency's subagencies with the following SQL as the row source:

SELECT [QryAgencies].[SubAgency], [QryAgencies].[Agency] FROM QryAgencies WHERE ((([QryAgencies].[Agency])=[ComboAgencies]));

So far, this works. My problem is that I can't figure-out how to make a Requery macro work to add values to the combo boxes when the user enters an agency or subagency that was not originally defined in the query "qryAgencies" or in the tables on which that query is based. The basic problem, I guess, is that the new values are being written not to the original tables, but to the table constructed to receive input ("DataRepository&quot.

I'd be grateful for suggestions.

Thanks.

JerSand

 

[jitter]

You could use the NotInList event of the combo box. Here you could use code to add their value to respective table and anything else you need to do.

Hope this helps....

 

[JerSand]

Thank you, jitter. My VBA skills are weak, but your idea seems on the money, so I'll give it a try. I'm grateful for your response.

JerSand

 

[JerSand]

Thank you, jitter. My VBA skills are weak, but your idea seems on the money, so I'll give it a try. I'm grateful for your response.

JerSand