Override Query with VBA

[theclosh]

I need a way to override the query setup for a form (frm1) using VBA. The query has criteria that is based solely on a separate form (frm2). I now need to be able to open the same form (frm1) but using criteria from another form (frm3). I'm trying the following

DoCmd.OpenForm rstAllSubs![FN], acNormal, , Client_ID = Me.Client_ID And Batch_Number = Me.Batch_Number

but the form is based on Client_ID = [Forms]![Click Here to Search for or Add Cookbook]![Cookbook Query1]![Client ID] and Batch_Number = [Forms]![Click Here to Search for or Add Cookbook]![Cookbook Query1]![Batch Number] so it asks me for these criteria again before it will open.

Any suggestions on how I can modify this to work?

 

[gemma-the-husky]

base the query on global variables instead of form references (you have to use functions to dereference the global variables)

then you can use it from anywhere

 

[theclosh]

Thank you for the input. By Global variables, though, are you referring to using Me.Client_ID in the Query rather than Form!...?

 

[gemma-the-husky]

how do you select the items to display in the first form? a query, yes?

so in the query, you need to have something to select the rows you want - so there will be some qualifier in the criteria row (a where clause) (maybe)

so one way to make the query easily reusable is to make this criteria dependent on a variable refernece rather than a form reference