Select record on a subform from a second subform

C

chavnick

I have a form with 3 tabs. In the second tab there is a subform named
"PatientWorks" with the primary key "workid". In this subform there is a
second subform named "bform_PatientWorks". The two subforms are joined with
the field "PatientCode" so the "bform_PatientWorks" shows all the records
related with the current patient.When I double-click on a record on
"bform_PatientWorks" I want the main subform "PatientWorks" to show the same
record. How I can do this? Thank you.
 
N

NKTower

Not absolutely sure I understand your description, but let me take a stab at
what I think you have.

frm_Main - record source is tblPATIENT_INFO

on 2nd tab (although it really doesn't matter) you have two subforms. I'm
going to call them sfm_Left and sfm_Right for simplicity. The one on the
left displays detail for one tblWORKS record. It is bound to the works
table, but via Child/Parent constraint in form design restricted to only
those records for the particular patient.


The one on the right is a continuous form showing ALL of the tblWORKS
records, again via Child/Parent constraint only for the selected patient.

If you double-click on one of the rows in the right subform, you want to
repostion the left subform to that record where you show more of the fields
than are available in the list view.

Solution 1:

In sfm_Right's Double Click event...

Private Sub Double_Click()
Dim frm_Parent As Form
Dim sfrm_Left As Form
Dim rs_Left As DAO.Recordset

Set frm_Parent = Forms![frm_Main].Form
set sfm_Left = frm_Paent![sfm_Left].Form
Set rs_Left = sfm_Left.RecordsetClone
rs_Left.FindFirst "[WorkID] = " & Me.WorkID
If Not rs_Left.NoMatch Then
sfm_Left.Bookmark = rs_Left.Bookmark
End If
Set rs_Left = Nothing
Set sfm_Left = Nothing
Set frm_Parent = Nothing
End Sub

If you are using ADO you will have to change the definition of rs_Left and
perhaps use a different method to position the record set clone.

------
Solution 2:
Private Sub Double_Click()
Dim frm_Parent As Form
Dim sfrm_Left As Form
Dim SQL As String

Set frm_Parent = Forms![frm_Main].Form
set sfm_Left = frm_Paent![sfm_Left].Form
SQL = "SELECT * FROM tblWORKS " & vbCrLf & _
"WHERE ( ( [patientid] = " & Me.PID & ") & _
" AND ( [WorkID] = " & Me.WID & ")" & _
" )"
rs_Left.RecordSource = SQL
rs_Left.Requery
Set sfm_Left = Nothing
Set frm_Parent = Nothing
End Sub
 
C

chavnick via AccessMonster.com

NKTower said:
Not absolutely sure I understand your description, but let me take a stab at
what I think you have.

frm_Main - record source is tblPATIENT_INFO

on 2nd tab (although it really doesn't matter) you have two subforms. I'm
going to call them sfm_Left and sfm_Right for simplicity. The one on the
left displays detail for one tblWORKS record. It is bound to the works
table, but via Child/Parent constraint in form design restricted to only
those records for the particular patient.

The one on the right is a continuous form showing ALL of the tblWORKS
records, again via Child/Parent constraint only for the selected patient.

If you double-click on one of the rows in the right subform, you want to
repostion the left subform to that record where you show more of the fields
than are available in the list view.

Solution 1:

In sfm_Right's Double Click event...

Private Sub Double_Click()
Dim frm_Parent As Form
Dim sfrm_Left As Form
Dim rs_Left As DAO.Recordset

Set frm_Parent = Forms![frm_Main].Form
set sfm_Left = frm_Paent![sfm_Left].Form
Set rs_Left = sfm_Left.RecordsetClone
rs_Left.FindFirst "[WorkID] = " & Me.WorkID
If Not rs_Left.NoMatch Then
sfm_Left.Bookmark = rs_Left.Bookmark
End If
Set rs_Left = Nothing
Set sfm_Left = Nothing
Set frm_Parent = Nothing
End Sub

If you are using ADO you will have to change the definition of rs_Left and
perhaps use a different method to position the record set clone.
Click to expand...

I've tried the first solution and it worked perfectly. So there is no need to
try the 2nd solution also.
Thank you very much for your great help.
 
Top