Run query from a Form

[Master Klick]

I have a query to sort different data on things. The criteria fields in the query reference combo boxes in a form. The idea is to select an option in one combo box and that selection will be set as criteria in the query narrowing down the choices in the next combo box. I just need to get the query to run automatically when a change is made to the combo box. I'm assuming I need just a simple VBA code to do this but I don't know where to start. Hoping it will be simple. Just something like, on change run "import" query. Nothing fancy.

[pbaldy]

Is this what you're trying to do?

http://www.baldyweb.com/CascadingCombo.htm

[Master Klick]

Is this what you're trying to do?

http://www.baldyweb.com/CascadingCombo.htm

That's close, but it works in the wrong direction. I would the "city" combo box to show all cities at first and when you select a state narrow the list down by that state.
But I don't want to do the whole thing with vba. I already the database set up to that. I just need to know the vba code to make the query run.

[pbaldy]

If the query is the source of a combo or form, you don't "run" the query. You'd requery the combo or form.

[Master Klick]

If the query is the source of a combo or form, you don't "run" the query. You'd requery the combo or form.

How do I do that?

[June7]

If you select the City first, why would you even need a combobox to select State? Include the State as a column in the City combobox. Are you saving an ID value for the city/state/zip record or are you saving each part?

Here's another tutorial for cascading comboboxes http://datapigtechnologies.com/flash...combobox2.html

And multi-column combobox http://datapigtechnologies.com/flash...combobox3.html

[pbaldy]

How do I do that?

It was demonstrated in the sample.

[Dal Jeanis]

1) In the AfterUpdate event of control1, after you change the filter settings, you will have a VBA line that says (path)!control2.Requery. (that's one way)

2) The exact path will vary depending on the location of the two controls. Save this reference page - you'll need it over and over again, to figure out the path syntax - http://access.mvps.org/access/forms/frm0031.htm

3) Whether or not you really need to requery will depend on various things. For example, replacing a control's rowsource is supposed to cause an automatic requery.

[Master Klick]

I'm using that as an example. I can't say what's actually in the database. But yes, I HAVE to be able to narrow the choices by any and all criteria.

[Dal Jeanis]

What I often do in this situation, where you're going to have multiple controls that each limit the form's/subform's selection criteria, is that each control, in its AfterUpdate event, will do its own logic (including setting any cascades) then call a single routine that will build the SQL for the query based upon all the selected options. This allows you to have all the code in one place, and clearly delineate the interactions.

Trying to have two controls that each limit the other is risky, and very situation-dependent. A city that limits state and a state that limits city would be a situation I don't have a good model for. Hierarchy is safer and easier to debug.

On the other hand, if you are sure you want to have the controls mutually interacting, then infinite loops are likely, so you'll need to deal with it using standard recursion escape procedures (set a flag on which control's being handled, etc). Also, be sure you have a reset button of some sort so that you can restore the controls to the entire field of possible values, rather than a mutually-limiting death spiral.

[Master Klick]

Thanks for the help guys. I somewhat have it working now. What I'm doing is:

On the list and combo boxes, for criteria I'm using a formula like [Forms]![Form1]![City] or like [Forms]![Form1]![City] is null
Then a macro: Private Sub Edition_AfterUpdate()
Me!Lst.Requery
End Sub
Then to get rid of repeats in the combo boxes I set Unique Values to yes.

All this worked, perfectly, as long as I had only 2 combo boxes and 1 list. Once I added another combo box then I saw some issues.
First issue is after I set the criteria, access likes to break it up and move parts of it to other locations. The code still seems to work but it makes an awful mess that can't be trouble shot.
Second, the cascading stops working after adding the third combo box. This may have just been me putting something in the wrong place by mistake. Not sure yet.
And third, I wiped everything out, started a new database, imported the data again and tried starting from scratch. Now I can't get the Unique Values to work at all.

Any suggestions?

[Dal Jeanis]

As I said before, my strategy to deal with that situation is to create a single routine that builds the SQL based upon the states of the various controls. If you want the controls to affect other controls, then you need to set a hierarchy.

Example 1: a reset button restores the query to allow everything. Each time the query is rewritten, each control is reset to be limited to whatever came back from the query. So, if control X limits the query to one state, then the query is rewritten, brings back results with only one state, and the control, when reloaded, will only have that one state in it. If control Y first limits itself to one city, but the city has two possible states, then control X will be loaded with those two potential states. Each control limits the possibilities for the main query, which reflexively limits all the controls.


Example 2: Each control has an order in which they will be used, and changing a higher-level control ressets all the lower-level ones. (Classic Cascade)

[Master Klick]

Are you saying to set it up like, combo 1 limits combo 2, combo 2 limits combo 3 and so on, rather than having all combo boxes limit each other and the list? And then a reset button that essentially clears the combo boxes therefore resetting the list and query?

[Dal Jeanis]

Pretty much.

Either you can have feedback like this

Control 1 -> limits control 2 , clears control 3 and rebuilds query
Control 2 -> limits control 3 and rebuilds query
Control 3 -> rebuilds query


Or you can have feedback like this

all controls -> build query -> run query -> query results limit all controls



When I say "rebuilds query", I mean that it runs through a VBA procedure that builds the SQL for the form based upon the settings of the controls.

When I say "the query results limits all controls", I mean that the row source for each control is limited to items that were returned in the query that had been built. If a control had contained a selected item, then that item logically must be only item returned.

It's a little more complicated than anything I've done before, but it seems feasible.

[Master Klick]

What if I used multiple queries? Something like have one small query for each combo box. Then base to list on one query that draws from all the other ones.