469,637 Members | 1,619 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,637 developers. It's quick & easy.

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

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

Jun 9 '06 #1
2 10825
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

Jun 9 '06 #2
Thanks Allen Works great!!

Jun 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Support4John | last post: by
3 posts views Thread by Simone | last post: by
5 posts views Thread by BD | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.