Navigate to a specific record in a subform

  • Thread starter Thread starter Kurt
  • Start date Start date
K

Kurt

I'm trying to open a form that has a subform and make it
navigate to a specific record on the subform, based on a
value in the form that I'm coming from. The form I'm
coming from shows a few fields from a query, and the users
want to click on any record and go to a form/subform
showing more details of the record they are on.

The record they click on is actually a record in a subform
of the form that opens, so I think I need to open the
form, navigate to the correct form record, then navigate
to the correct subform record (unless there's a better
way?)
I can open a form and navigate to a form record using:
Dim varRecord1_id As String
varRecord1_id = Me![Record1_ID]
stDocName = "View Details"
stLinkCriteria = "[Record1_ID]=" & "'" & varRecord1_id
& "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

My problem is that I can't get the subform to then
navigate to a specific record ( I can pass the id,
record2_id, for it). I tried this (below) and get the
error "Run-time error '438' Object doesn't support this
property or method", it stops on the "Set rs" line:

'Find the subform record.
Dim rs As Object
Set rs = Forms![View Details]![View Details
Subform1].Recordset.Clone
rs.FindFirst "[SubRecord_ID] = '" & varSubRecord_id & "'"
Me.Bookmark = rs.Bookmark

I'm not an experienced VBA programmer. I copied and
modified the above from a wizard-created combo box that
navigates to the selected record.

I think I'm close, can anyone please help?

Thanks,

Kurt
 
How about a simpler approach... set the LinkMasterFields and LinkChildFields
of the Subform Control to the field that identifies the record in the main
form and to the corresponding field in the RecordSource of the Form embedded
in the Subform Control, and when you select a particular record in the main
form, the proper record willl be display automatically?

Larry Linson
Microsoft Access MVP
 
Larry,

I'm not sure what you mean. Do you mean set the property
of the subform "on the fly"? The form I'm opening is also
used as a data entry form. They want to go to the
particular record, but still be able to navigate to any of
the rest of the related records in the subform. Would
this approach allow this?

By The Way, I did figure how to make it work (with help
from "CD" on this NG) with the called form - I can open
it, and make it go to the correct record on the form.
When I try to make it go to the subform, it trips up and
says that it can't find the subform. I think I just need
to figure out the syntax for referring to a subform within
a form - I need quotes or ! or ' or . or something. Here's
the code, it stops when I refer to the subform in the "set
frm" statement (see my comment next to it). The forms are
named "manage sets complex" and "manage sets complex
subform". The error is that it can't find the subform.
I'm pretty sure I've just got the syntax wrong.

Anybody?


Private Sub Member_id_DblClick(Cancel As Integer)

Dim rs As DAO.Recordset, Frm As Form, crit$, frmNm$
frmNm$ = "Manage Sets Complex"
If Not IsLoaded(frmNm$) Then
DoCmd.OpenForm frmNm$
End If
Set Frm = Forms(frmNm$)
Set rs = Frm.RecordsetClone
crit$ = "[SET_ID]=" & CStr(Me.Set_id.Value)
rs.FindFirst crit$
If Not rs.NoMatch Then
Frm.Bookmark = rs.Bookmark
' now navigate to the subform
frmNm$ = "Manage Sets complex Subform"
Set Frm = Forms(frmNm$) 'here is where it stops
Set rs = Frm.RecordsetClone
crit$ = "[member_ID]=" & CStr(Me.Member_id.Value)
rs.FindFirst crit$
If Not rs.NoMatch Then
Frm.Bookmark = rs.Bookmark
Frm.SetFocus
End If
End If
Set rs = Nothing
Set Frm = Nothing
End Sub
-----Original Message-----
How about a simpler approach... set the LinkMasterFields and LinkChildFields
of the Subform Control to the field that identifies the record in the main
form and to the corresponding field in the RecordSource of the Form embedded
in the Subform Control, and when you select a particular record in the main
form, the proper record willl be display automatically?

Larry Linson
Microsoft Access MVP

Kurt said:
I'm trying to open a form that has a subform and make it
navigate to a specific record on the subform, based on a
value in the form that I'm coming from. The form I'm
coming from shows a few fields from a query, and the users
want to click on any record and go to a form/subform
showing more details of the record they are on.

The record they click on is actually a record in a subform
of the form that opens, so I think I need to open the
form, navigate to the correct form record, then navigate
to the correct subform record (unless there's a better
way?)
I can open a form and navigate to a form record using:
Dim varRecord1_id As String
varRecord1_id = Me![Record1_ID]
stDocName = "View Details"
stLinkCriteria = "[Record1_ID]=" & "'" & varRecord1_id
& "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

My problem is that I can't get the subform to then
navigate to a specific record ( I can pass the id,
record2_id, for it). I tried this (below) and get the
error "Run-time error '438' Object doesn't support this
property or method", it stops on the "Set rs" line:

'Find the subform record.
Dim rs As Object
Set rs = Forms![View Details]![View Details
Subform1].Recordset.Clone
rs.FindFirst "[SubRecord_ID] = '" & varSubRecord_id & "'"
Me.Bookmark = rs.Bookmark

I'm not an experienced VBA programmer. I copied and
modified the above from a wizard-created combo box that
navigates to the selected record.

I think I'm close, can anyone please help?

Thanks,

Kurt


.
 
Back
Top