SQL Where Clause

[ledgerr.rob]

Good afternoon

Windows 7
Access 2013

I've been trying to work up a where clause that is generated by a button click event on a report. The workflow that i'm trying to obtain is as follows: 1) A report is run to determine the remaining work orders that need to be processed. 2) A button that is placed on that report is to be clicked, taking the user to the form associated with that work order, so it can be processed.


What i've been able to do so far is capture the unique ID for the work order and then print that in a message box. I can then open the form.

What i haven't been able to accomplish thus far is to open the form to the correct work order.


Things I've tried
I started trying to use the macro with the search for record option and using the where clause. Not successful. I am a little more comfortable in using vba so i switched to that pretty quickly.

Private Sub btnJobEntry_Click()
'GOAL: open the work order form to the correct entry
'METHOD: store the uniqueID to a variable, then use that in the open command's where clause

Dim strJobID As String

'store the unique ID in the variable
strJobID = Me.JobID.Value

'Display message box with job id
MsgBox ("Here is the job id: " & strJobID)

'open the form frmJob and use the where clause to select the correct job
DoCmd.OpenForm "frmJob", acNormal, , strJobID

End Sub

[CODE]

I've put the strJobID variable in both the filter and where clause sections of the DoCmd but it just opens the form to the first entry.  I'm fairly confident i'm not applying the filter/where clause correctly by using the incorrect syntax.

Anyone have any suggestions on correct syntax or application of code to solve my problem?

Thanks,
rob

 

[pbaldy]

You're forgetting the field to filter:

http://www.baldyweb.com/wherecondition.htm

 

[ledgerr.rob]

And that Pbaldy is why this is the best forum on the web. Your link provided the information to solve my problem. Thank you. My working code is below. I've commented out the section where I've saved the value to a variable, and then printed. It was nice to have to help walk through the problem, but the remaining ONE LINE OF CODE at the very end is all that is required.


Thanks again!!

rob

Private Sub btnJobEntry_Click()
''GOAL: open the work order form to the correct entry
''METHOD: store the uniqueID to a variable, then use that in the open command's where clause
'
'Dim strJobID As String
'
''store the unique ID in the variable
'strJobID = Me.JobID.Value
'
''Display message box with job id
'MsgBox ("Here is the job id: " & strJobID)

'open the form frmJob and use the where clause to select the correct job
DoCmd.OpenForm "frmJob", acNormal, , "JobID = " & Me.JobID

End Sub

 

[pbaldy]

Happy to help Rob!