Designing a Query inside a Macro

[highbulp]

I have a macro that I would like to open a Query in design view (so that the user may edit the query), and then run the query after the user has edited it (like when the user closes the design window). However, when I use the OpenQuery command to open the Query in design view, the macro just moves on to the next step without waiting for the Query to be modified!

I think that it may be possible to use a Condition to check if the Query is being designed, but I don't know what that check would be.

Any help would be greatly appreciated, thank you!

 

[Ziggy1]

what are you editing in the query? If it is criteria then you can easily modify criteria via a form. I'll explain that more if that is what you are doing?

 

[highbulp]

what are you editing in the query? If it is criteria then you can easily modify criteria via a form. I'll explain that more if that is what you are doing?

That is exactly what I am doing. I didn't think I could have the actually criteria statement "field1<=condition" as an element of a form. But please explain, I'd really appreciate it!

 

[Ziggy1]

You could probably find a million examples here by searching filter by form, but simply heres what you do...

you may have an existing form already, so if you understand what I am saying you can use that form, or else create a new form and for the sake of testing make a copy of your query.

So with the form add a Button to open the query

then add a text control name it eg txtCity (what ever is applicable to the field)

Save the form


now go to the query design, click in the Criteria field for City (or whatever) and place the following Syntax...

[forms]![YourFormName]![txtCity]

of course replace with your form name and control name from form


a better way is

Like ([forms]![frmStocklist]![cobGeneric]) & "*"

with this criteria go back to the form in design and place the * as the default value in the properties of the text control. what this does is returns everything if nothing is selected. you will want to use this if you add multiple criteria


Ok so now you are ready to run the query. open the form in "Form view" and type in the criteria you would normally edit and then click the button to open the query. The query will come up restricted based on the text box.

Just remember that the query needs to be opened by that form from now on, you can add additional controls for different fields, but remember that the criteria needs to correspond ( same as if you did manually).


This will work with a combo (better than text control) also all you need it the name of the control in the criteria

 

[highbulp]

This method allows me to add custom criteria for a particular field, correct? So that if someone enters '="New York" ' in the textbox, it will add a WHERE City="New York" to the query. Is that right? Is this the same for the second method (using LIKE), or does that create a pattern-matching filter rather than a value-comparison filter?

What I want is for the user to be able to enter the equivalent of 'City="New York" ', or 'ProductID=3', or 'Quantity<10', any of which would then be added as a filter. I'd also like them to be able to submit multiple criteria (so to be able to enter 'City="New York" AND Quantity<10'). And it would be best to actually use Access' UI, since the people who will be using this database do not know SQL very well.

Will filter-by-form be able to do this (namely being able to specify multiple criteria based on multiple, user-specified fields)? I didn't think it would, which is why I asked.

You have given me the idea to just add a button to the form that opens up the Query in Design view. So they can hit the button, set the Query, and then hit another button to run the Macro script. This sounds like it would be an easy way of getting the effect I want.

 

[Ziggy1]

I am describing the design view, not SQL view. You put the criteria into the form fields and then click the button and the query will be filtered.

The Control name reference as i describe gets placed into the Criteria field in the query design grid. when you opn the query via the form, the query looks at the form fo it's criteria.

 

[Ziggy1]

to clarify on the SQL it would look like this


WHERE (((query1.City)=[forms]![YourFormName]![txtCity])

the method I describe is very user friendly, and I prefer to use combo boxes. I create seperate queries to create "lookups" for the fields I plan to use as criteria. This eliminates data entry errors.

 

[highbulp]

I think I'll just set it up as a separate button. I don't believe that what your suggestion will do what I want. Thanks anyway for your response

 

[Ziggy1]

well it lets you input via a form so you don't have to edit your query.

filter by form works for multiple criteria also, just right click on field, you can type in to the filter (even with <> =)

 

[edolan]

multiple criteria in combo box

Hi, I'm using a form to run a query in access with multiple criteria listed in a combo box. it works fine if I have a single value in my combo box but i get a blank result if i have multiple criteria. any thoughts on why this is happening?

my query is WHERE (((item.ID) Like ([forms]![frmlist]!

• ) & "*"));
  
  i paste in a list of ids in my combo box and the query comes back empty. if only one id is in then it works fine - is there something I need to do for the query to accept a list?