Opening a form based on a query with VBA-set parameters

[dipique]

I have a form in a database that runs on a query. I would like to be able to limit what records appear on the form by setting query criteria based on parameters that I pass in code. But when I pass the parameter in VBA, the form still prompts for the query parameter. How can I make the parameter "stick"?

The code I use currently to set the parameter:


Private Sub Command0_Click()
'Set query parameter values
Dim qdf As DAO.QueryDef

'Set query parameter(s)
Set qdf = CurrentDb.QueryDefs("qryBricks")
With qdf
.Parameters("parColor") = "*" 'Usually this would be "Green" but I'm just trying to get the darn thing to run right now.
End With

'Launch form
DoCmd.OpenForm "frmBricks" 'At this point it asks me for the parameter even though I've already set it.
End Sub


Any help would be appreciated.

Thank you,

Dan

11609

[GP George]

The problem is that setting the parameter for the query in this procedure has no effect on the query as the recordsource for the called form. It is not persistent between the two instances of the query. There is more than one way to filter the recordset for the called form. You can either use a combo or list box on your calling form to select the value you want to use for the filter and reference that form control in the query instead of the parameter currently in it. Or you can pass that value to the called form via OpenArgs and then, in the open event of the called form, rewrite the SQL in the query dynamically to use that OpenArgs value.

[dipique]

The problem is that setting the parameter for the query in this procedure has no effect on the query as the recordsource for the called form. It is not persistent between the two instances of the query. There is more than one way to filter the recordset for the called form. You can either use a combo or list box on your calling form to select the value you want to use for the filter and reference that form control in the query instead of the parameter currently in it. Or you can pass that value to the called form via OpenArgs and then, in the open event of the called form, rewrite the SQL in the query dynamically to use that OpenArgs value.

Thanks, I think I can use one of these methods to solve the problem. What you said made sense--that they are different instances of the query.

Much appreciated!

Dan