Query: Have content of one drop down column depend on another column

[Tetol]

I have a States table and a Cities table with a one to many relationship.

Can I have a query such that when I choose a state in the states' drop down column, it will show only the cities for that state in the cities' drop down column.

I know how to do this in a form but i would like to do this in a query.

Thanks for your help in advance.

 

[PHV]

I know how to do this in a form but i would like to do this in a query
How would you choose anything in a drop down column in a query ???

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

This is technically doable, but very problematic and not worth attempting. You can choose a combo, listbox, textbox, for your query field input. Even in a continous form (which you have more control over) using cascading combos requires a work around. When you limit the rowsource of the second combo, the rows filtered out will disappear in the records bound to these rows. So assume you pick CA in a record. Then all other records that have cities not in CA will seem to dissappear. In a form you can fix this by laying a textbox over top of the combobox.

 

[MajP]

Also I think the only way to do this would require Access 2010 which has data macros. There needs to be an event to requery the second combo. This is normally done in the form on the on enter event of the second combo. There are no table level events prior to 2010 data macros.

 

[Tetol]

MajP...Thanks you, that's what I thought also. I have Access 2007 and I could not figure a way to do this except if I set it up on an Access form.

What I was really trying to do is create a query/table that I could base an InfoPath form on. Then the form can be emailed to recepients who have InfoPath installed but not necessarily Access, and after filling out the form, the data can save into Access directly. Any ideas??

Thanks

 

[MajP]

I do not have any experience with Infopath forms, but it appears you could do this without making the query with cascading combos. From what I read you can add code to a form. There is a sharepoint forum and maybe that is a good place to ask.