Apply Query Criteria in VBA

[el zorro]

I have a select query that used to have a parameter for "month" built into
the query. In the query by design window under the field named "month," there
was a reference to a list on a form: [Forms]![SelectDateForm]![monthlist].
WHen the user selected a mOnth from the list and then clicked the control to
run the query, the following VBA code was executed (in part):

stDocName = "GrantQuery"
'Run query as Read Only
DoCmd.OpenQuery stDocName, acNormal, acReadOnly

I would like to take the reference to the form out of the query, and have
the VBA statement that opens the query apply the filter. I have been able to
do this with the OpenReport command by supplying a criteria on the line that
opens the report(DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria),
but I can't seem do apply the same filter when using DoCmd.OpenQuery. Is
there a way to do this? Thanks in advance!

 

[Jeanette Cunningham]

Hi,
if you have a form with the query as its recordsource, then you can use
something like this to open the form showing the grant query for the month
the user selects from the combo for months.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmGrants"

stLinkCriteria = "[Month]=" & "'" & Me![cboMonth] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acReadOnly

Jeanette Cunningham

 

[el zorro]

Thanks for your response, Jeanette. It is not a form that I want to open, I
just want to run the query when the user clicks the command button using
DoCmd.OpenQuery. The strange thing is that the OpenQuery statement apparently
does not allow for the application of query criteria as you have suggested.
THere is no place to insert the stLinkCriteria... at least, not that I can
see.

Hi,
if you have a form with the query as its recordsource, then you can use
something like this to open the form showing the grant query for the month
the user selects from the combo for months.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmGrants"

stLinkCriteria = "[Month]=" & "'" & Me![cboMonth] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acReadOnly

Jeanette Cunningham

I have a select query that used to have a parameter for "month" built into
the query. In the query by design window under the field named "month,"
there
was a reference to a list on a form: [Forms]![SelectDateForm]![monthlist].
WHen the user selected a mOnth from the list and then clicked the control
to
run the query, the following VBA code was executed (in part):

stDocName = "GrantQuery"
'Run query as Read Only
DoCmd.OpenQuery stDocName, acNormal, acReadOnly

I would like to take the reference to the form out of the query, and have
the VBA statement that opens the query apply the filter. I have been able
to
do this with the OpenReport command by supplying a criteria on the line
that
opens the report(DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria),
but I can't seem do apply the same filter when using DoCmd.OpenQuery. Is
there a way to do this? Thanks in advance!