Open form to a specific Record, but show all records??

[nkoske]

I have a form with a subform in datasheet view and I have a event on
one of the fields in the subform, so that the user can double click it
and go to expanded information based on that field.

I'm using this code:
Private Sub SpecimenNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "RDDevice", , , "[SpecimenNum] = '" & Me![SpecimenNum] &
"'"
End Sub

It works and goes to that record, but it filtered only to that specific
record. How can I go to that particular record and still be able to
browse the rest of the records.

I tried putting the DoCmd.ShowAllRecords command in that code above,
but then it doesn't go to me specific record, it goes to the first one.

Any info would be great.

Thanks,
Nick

[Allen Browne]

Find the record in the RecordsetClone of the form after you open it:
Dim strWhere As String
Dim rs As DAO.Recordset
strWhere = "[SpecimenNum] = '" & Me![SpecimenNum] & "'"
DoCmd.OpenForm "RDDevice"
With Forms!RDDevice
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
Set rs= Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<nk****@gmail.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...

I have a form with a subform in datasheet view and I have a event on
one of the fields in the subform, so that the user can double click it
and go to expanded information based on that field.

I'm using this code:
Private Sub SpecimenNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "RDDevice", , , "[SpecimenNum] = '" & Me![SpecimenNum] &
"'"
End Sub

It works and goes to that record, but it filtered only to that specific
record. How can I go to that particular record and still be able to
browse the rest of the records.

I tried putting the DoCmd.ShowAllRecords command in that code above,
but then it doesn't go to me specific record, it goes to the first one.

Any info would be great.

Thanks,
Nick

[nkoske]

Thanks Allen Works great!!