Query criteria from code

[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.

 

[IfButOnly]

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.