DoCmd OpenQuery with Where clause

[Hotwheels59]

I am trying to use a DoCmd.OpenQuery command to open a query referencing an employee ID from a form. Opening a report and using a macro, the "Where Condition" code looks like this:

[Forms]![frmMainInput]![frmSummary_Subform].[Form]![Job Code]=[qryCheckResponsibilities]![Job Code].

I have a module that looks like this:

Private Sub Command50_Click()
DoCmd.OpenQuery "qryCheckResponsibilities", acViewNormal, acEdit
End Sub

How can I get the VB code to pull the correct data based on the employee ID in the frmSummary_Subform?

Any help is much appreciated.

 

[dcb]

What do you mean by "Pull the correct data"
Your code opens the query?

 

[Hotwheels59]

Correct... it opens the query, but what I want it to do is open the query referencing a specific parameter... in this case the employee ID which is embedded in the form on which the button calling the code exists. Thanks!

 

[MSAccessRookie]

Correct... it opens the query, but what I want it to do is open the query referencing a specific parameter... in this case the employee ID which is embedded in the form on which the button calling the code exists. Thanks!

You can try modifying the Query instead of the Open call. A Control on a Form can be compared to in SQL. As an Example, the Following WHERE Statement will Qualify the SQL Statement based on a particular Control located on a particular Form.

[FONT=Courier New]       [B][COLOR=red]{Table Name}.{Column Name}         Forms!   {Form Name}   ! {Control Name}[/COLOR][/B][/FONT]
[FONT=Courier New]WHERE tblProduction.tblProd_BaseBatchID = Forms!frmProductionBase!TB_BaseBatchID[/FONT]

 

[dcb]

AhHa - I did not read correctly.....

Put the [Forms]![frmMainInput]![frmSummary_Subform].[Form]![Job Code] in the criteria of your querys design grid under the "Job Code" Column

Have a look at the help file on parameter querys.

Of course the form must be open to read the parameter

 

[Hotwheels59]

Thanks much for the response. I've used forms that have calls like this in the past and oddly, it didn't dawn on me to try that. I'll give that a try. Thanks again!

 

[Hotwheels59]

Thank you! I'll try that as well. I meant to say "Job Code" as opposed to "Employee ID" throughout. Thanks for catching that. Really appreciate it.

 

[Hotwheels59]

DCB, that worked like a charm. Excellent. Thanks again.