Populating a combo box from a query

[Peter Mac]

I have a database I'm working on where I need to populate a combo box from the values in a query.

The control source on m form is a box called "County"

The row source for County is: SELECT [Counties Query].CountyName FROM [COUNTIES QUERY];

Inside the Counties Query I have the criteria:

[Forms]![Customers]![State]

My goal is to have the County field look up County choices based on the state.
For example, if the State selected in the State Field is "California" it would only show California counties in the combo box on the form.

It works great for the first record. However, it appears the criteria is permanently bound to that first record.

How do I tell Access to change that criteria every time someone changes the state? I have a feeling it will require some VBA so I'm not sure of the steps.
It seems to me it should just be an easy step to refresh the query with the new form data for the state.

I can upload the database if needed.

 

[CyberLynx]

Use the query WHERE clause. Start there.


.

 

[Peter Mac]

Use the query WHERE clause. Start there.
.

You must have a lot of faith in me that I can figure out how to solve this problem from that comment

Interestingly, you did indeed send me in the right direction and I solved the problem.

I got to thinking that I could set the value using a macro and a sql statement and that worked. Here's what I did:

1. I created a row source sql statement using WHERE to use the STATE field in my form as the criteria.
2. I built a "setvalue" macro to update the form.field.rowsource value with the new SQL statement
3. I set the "on change" event in my STATE field to run the macro every time someone changed the state.

So in essence it runs a new query to find new counties for the record source every time the STATE is changed--just like magic

 

[CyberLynx]

I do have a lot of faith in you. And here is another short comment:

............


.

 

[coastguard]

Cyberlynx,

I like your style. Having just spent a while trying to sort out three combo boxes I eventually did by following some pointers and a sample data base.

It's good to find out yourself but helps to have direction.