Query criteria from code (1 Viewer)

[Ali Edwards]

Hello all,

Is it possible to write code that will enter an expression into a query's criteria, overwriting what's already there? I want to attach the code to a command button on a form. Then I could have several command buttons which enter different expressions into the same query.

Many thanks for any advice.

 

[pottera]

Ali,

I assume you are populating a list box with the queries, what you need to do is this:-

Have the 'Row Source Type' as Table/Query

The code behind each button will then be along this line

Sub Button1_Click()
me.listboxname.RowSource = SQL1
End Sub
Sub Button2_Click()
me.listboxname.RowSource = SQL2

..... etc

This inserts the appropriate SQL statement into the source of list box and Bob's your uncle.

 

[Ali Edwards]

Peter - Thanks.

In fact what I'm trying to do is have several reports based on only one query.

Rather than have a separate query for each report I'd like to use the same query but insert different criteria in the criteria pane of the query for each report. This way I could get the cmd button to first insert the expression in the criteria pane in the query, then open the report based on that query. If I want different information i could click another cmd button which would insert a different expression into the query's criteria pane, run a different report etc.. In each case the expression would be based on a field in the same form that the cmd button is located but there will be several forms.

Many thanks

Ali

 

[Ithilien]

If you are running the reports from a form, then enter the criteria in an unbound field on the form, and reference that field in the query criteria.

 

[Pat Hartman]

Leave the query as a stored querydef with NO selection criteria. Use the where argument of the OpenForm Method to supply the selection criteria. There are many posts here to help you figure out how to create the where argument if help is insufficient. Simply search for OpenForm and where.