double click on data sheet form and go to specific record in a form

[cookitup]

I am new to Access, and am trying to go from a datasheet that is based on a query to a form that is based on a table. I want it to pull up the specific line I am double clicking on in the datasheet. PLEASE HELP
Here is the code I am using:

Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_RFQ_Datasheet_Click
If IsNull(Me![OIMOS Key]) Then
MsgBox "Enter OIMOS Key before viewing DataSheet."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Estimating", , , Me.OIMOS_Key.Value = Me.OIMOS_Key


End If
Exit_RFQ_Datasheet_Click:
Exit Sub
Err_RFQ_Datasheet_Click:
MsgBox Err.Description
Resume Exit_RFQ_Datasheet_Click
End Sub

This code takes me back to the "Estimating" form, But not to the specific record

 

[pbaldy]

This should help with the syntax:

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

 

[cookitup]

Thank you sooooooo much! it took me to the syntax problem and made it work perfectly!!!!

 

[pbaldy]

Happy to help, and welcome to the site by the way!

 

[RickK]

This should help with the syntax:

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

Paul,

I found this thread while researching my next challenge. In the link you provided , there is another link for including all records and that is what I need to use.
I want to double click on a certain record in one form named "OpenAlert" and there is a field named WorkorderID in each record. I want to open the second form named "Workorders" and go to the record that I double clicked. The field name WorkorderID is also in this form. Using the info you provided in your link, I can get it to open the second form but it always goes to the first record and not to the record I double clicked. The record I want it to go to IS available.

Here is what I have under DoubleClick

Private Sub Form_DblClick(Cancel As Integer)
Dim rs As Object
Dim OpenAlert As Long

'set a variable to the current record
OpenAlert = Me.WorkorderID
'open the new form
DoCmd.OpenForm "Workorders"

'take it to the selected record
Set rs = Forms!Workorders.RecordsetClone
rs.FindFirst "WorkorderID = " & OpenAlert

'the lines marked as optional can be included if there's a chance the record won't exist in the form being opened
'If rs.NoMatch Then   'optional - if no match, go to a new record
 ' DoCmd.GoToRecord acForm, "Workorders", acNewRec   'optional
  'Forms!Workorders.txtEmpID = Me.txtEmpID  'optional - copy the employee ID from this form
'Else   'optional
 ' Forms!Workorders.Bookmark = rs.Bookmark
'End If   'optional
'Set rs = Nothing
End Sub

For now I have the optional stuff rem out just so I can get the first part working. What am I doing wrong?

Thanks for any help
Rick

 

[pbaldy]

Two of the lines in the last section weren't optional. The others said optional.