Open form to a subform record

  • Thread starter Thread starter Nick Mirro
  • Start date Start date
N

Nick Mirro

When using an open form command, I would like the new form to open to
(display a specific record) in its subform. The originating form and the
destination form's subform have the same recordset, so I can link the
primary keys.

How can I get the new form to open so that its "subform" displays a specific
record?
 
Nick Mirro said:
When using an open form command, I would like the new form to open to
(display a specific record) in its subform. The originating form and
the destination form's subform have the same recordset, so I can link
the primary keys.

How can I get the new form to open so that its "subform" displays a
specific record?
Click to expand...

Is the subform linked via Link Master/Child Fields to its parent form?
That will naturally constrain the contents of the subform. And do you
want to *locate* a record in the subform, or filter the subform to show
only the matching record?

You can use code like this to manipulate the subform after opening its
parent form:

'----- Warning: AIR CODE -----

Dim frm As Form

DoCmd.OpenForm "ParentForm"

Set frm = Forms!ParentForm!SubformControl.Form

'*** OPTION 1: Locate subform record
With frm.RecordsetClone
.FindFirst "ID=" & Me.ID
If Not .NoMatch Then
frm.Bookmark = .BookMark
End If
End With

'*** OPTION 2: Filter the subform
With frm
.Filter = "ID=" & Me.ID
.FilterOn = True
End With

' Needed for either opton:
Set frm = Nothing

'----- end of AIR CODE -----

Note: in the above, "ParentForm" is the name of the form you open that
contains the subform, and "SubformControl" is the name of the subform
control on that form that displays the subform.
 
Thanks for the help with this. The destination parent form cycles Patients,
while the subform cycles patient Visits.

In the origin form, there is no subform, so all Patients and Visits (subform
in destination form) are displayed in one recordset. (This will change)


The idea is to display a patient and visit record that was already opened in
the previous form, so I suppose 'locate' is better than 'filter.'

When I run the command with your code (much appreciated), the new form
opens, but neither the parent record nor the linked (by PatientID) subform
record match the previous form record.



Private Sub btnCollections2_Click()
On Error GoTo Err_btnCollections2_Click

Dim frm As Form

DoCmd.OpenForm "frmCollections"

Set frm = Forms!frmCollections!sbfrmCollections.Form

With frm.RecordsetClone
.FindFirst "VisitID=" & Me.VisitID
If Not .NoMatch Then
frm.Bookmark = .Bookmark
End If
End With

Set frm = Nothing

Exit_btnCollections2_Click:
Exit Sub

Err_btnCollections2_Click:
MsgBox Err.Description
Resume Exit_btnCollections2_Click

End Sub
 
Nick Mirro said:
Thanks for the help with this. The destination parent form cycles
Patients, while the subform cycles patient Visits.

In the origin form, there is no subform, so all Patients and Visits
(subform in destination form) are displayed in one recordset. (This
will change)


The idea is to display a patient and visit record that was already
opened in the previous form, so I suppose 'locate' is better than
'filter.'

When I run the command with your code (much appreciated), the new form
opens, but neither the parent record nor the linked (by PatientID)
subform record match the previous form record.



Private Sub btnCollections2_Click()
On Error GoTo Err_btnCollections2_Click

Dim frm As Form

DoCmd.OpenForm "frmCollections"

Set frm = Forms!frmCollections!sbfrmCollections.Form

With frm.RecordsetClone
.FindFirst "VisitID=" & Me.VisitID
If Not .NoMatch Then
frm.Bookmark = .Bookmark
End If
End With

Set frm = Nothing

Exit_btnCollections2_Click:
Exit Sub

Err_btnCollections2_Click:
MsgBox Err.Description
Resume Exit_btnCollections2_Click

End Sub
Click to expand...

I believe you'll need to filter frmCollections when you open it, to show
the current PatientID. Change this line:
DoCmd.OpenForm "frmCollections"
Click to expand...

to this:

DoCmd.OpenForm "frmCollections", _
WhereCondition:="PatientID=" & Me.PatientID

and see if it works.
 
Wow, that's quite nice to see working. Thanks as usual! Now to use it all
over the place : )

Nick
 
Back
Top