Connect data from 1 sub form to another

[mattc66 via AccessMonster.com]

I am not sure this can be done. I have a main form with 2 sub forms. Lets
call them subform1 and subform2.

Subform1 is linked to the main form and is a datasheet..I want to link the
subform2 to the subform1 ControlID.So when a user clicks on the controlID on
subform1 it displays the data linked in subform2. Subform2 is a Datasheet
also. There is a 1 to many relationship between the 2 tables.

Can this be done and if so, can anyone give me some examples of how I can do
this?

Thanks

 

[Jeanette Cunningham]

Hi matt66,
Here is some code I use to do this.
There is one main form with 2 subforms.
The code below is attached to a button on the first subform.
However it could be put on the current event of the first subform or the
double click event of controls on the first subform.

In the code below 'sfrmClientHistoryDets' is the name of the second subform
which displays the related data for the record chosen in the first subform.
Note: replace 'sfrmClientHistoryDets' and 'IssueID ' with your own
objects.

----------------------------
Public Function DoubleClickAction()
On Error GoTo Err_Handler


Dim frm As Access.Form
Dim rs As DAO.Recordset

If Me.RecordsetClone.RecordCount > 0 Then
If Len(Me.IssueID & vbNullString) > 0 Then
Me.Parent.sfrmClientHistoryDets.Visible = True
'Debug.Print Me.Parent.Name
'Debug.Print Me.Parent.sfrmClientHistoryDets.Form.Name
Set frm = Me.Parent.sfrmClientHistoryDets.Form

'set the bookmark for the second subform
Set rs = frm.RecordsetClone
If frm.RecordsetClone.RecordCount > 0 Then
rs.FindFirst "IssueID = " & Me!IssueID & ""
If Not rs.NoMatch Then
frm.Bookmark = rs.Bookmark
End If
End If
End If

Else
Me.Parent.sfrmClientHistoryDets.Visible = False
End If

Exit_Handler:
On Error Resume Next
Set rs = Nothing
Set frm = Nothing
Exit Function
Err_Handler:
Call fnFormErrHandler(pstrProc, pstrMdl, Err)
Resume Exit_Handler
End Function

 

[mattc66 via AccessMonster.com]

What would be the code to execute the function?

Hi matt66,
Here is some code I use to do this.
There is one main form with 2 subforms.
The code below is attached to a button on the first subform.
However it could be put on the current event of the first subform or the
double click event of controls on the first subform.

In the code below 'sfrmClientHistoryDets' is the name of the second subform
which displays the related data for the record chosen in the first subform.
Note: replace 'sfrmClientHistoryDets' and 'IssueID ' with your own
objects.

----------------------------
Public Function DoubleClickAction()
On Error GoTo Err_Handler

Dim frm As Access.Form
Dim rs As DAO.Recordset

If Me.RecordsetClone.RecordCount > 0 Then
If Len(Me.IssueID & vbNullString) > 0 Then
Me.Parent.sfrmClientHistoryDets.Visible = True
'Debug.Print Me.Parent.Name
'Debug.Print Me.Parent.sfrmClientHistoryDets.Form.Name
Set frm = Me.Parent.sfrmClientHistoryDets.Form

'set the bookmark for the second subform
Set rs = frm.RecordsetClone
If frm.RecordsetClone.RecordCount > 0 Then
rs.FindFirst "IssueID = " & Me!IssueID & ""
If Not rs.NoMatch Then
frm.Bookmark = rs.Bookmark
End If
End If
End If

Else
Me.Parent.sfrmClientHistoryDets.Visible = False
End If

Exit_Handler:
On Error Resume Next
Set rs = Nothing
Set frm = Nothing
Exit Function
Err_Handler:
Call fnFormErrHandler(pstrProc, pstrMdl, Err)
Resume Exit_Handler
End Function
-------------------------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am not sure this can be done. I have a main form with 2 sub forms. Lets
call them subform1 and subform2.

[quoted text clipped - 10 lines]

Thanks

 

[mattc66 via AccessMonster.com]

I got it show the subform. However I only want to see the records that are
linked to the ID fld. How would I do that?

Hi matt66,
Here is some code I use to do this.
There is one main form with 2 subforms.
The code below is attached to a button on the first subform.
However it could be put on the current event of the first subform or the
double click event of controls on the first subform.

In the code below 'sfrmClientHistoryDets' is the name of the second subform
which displays the related data for the record chosen in the first subform.
Note: replace 'sfrmClientHistoryDets' and 'IssueID ' with your own
objects.

----------------------------
Public Function DoubleClickAction()
On Error GoTo Err_Handler

Dim frm As Access.Form
Dim rs As DAO.Recordset

If Me.RecordsetClone.RecordCount > 0 Then
If Len(Me.IssueID & vbNullString) > 0 Then
Me.Parent.sfrmClientHistoryDets.Visible = True
'Debug.Print Me.Parent.Name
'Debug.Print Me.Parent.sfrmClientHistoryDets.Form.Name
Set frm = Me.Parent.sfrmClientHistoryDets.Form

'set the bookmark for the second subform
Set rs = frm.RecordsetClone
If frm.RecordsetClone.RecordCount > 0 Then
rs.FindFirst "IssueID = " & Me!IssueID & ""
If Not rs.NoMatch Then
frm.Bookmark = rs.Bookmark
End If
End If
End If

Else
Me.Parent.sfrmClientHistoryDets.Visible = False
End If

Exit_Handler:
On Error Resume Next
Set rs = Nothing
Set frm = Nothing
Exit Function
Err_Handler:
Call fnFormErrHandler(pstrProc, pstrMdl, Err)
Resume Exit_Handler
End Function
-------------------------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am not sure this can be done. I have a main form with 2 sub forms. Lets
call them subform1 and subform2.

[quoted text clipped - 10 lines]

Thanks

 

[Jeanette Cunningham]

What is the name of the primary key field that is the link between the 2
subforms?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I got it show the subform. However I only want to see the records that are
linked to the ID fld. How would I do that?

Hi matt66,
Here is some code I use to do this.
There is one main form with 2 subforms.
The code below is attached to a button on the first subform.
However it could be put on the current event of the first subform or the
double click event of controls on the first subform.

In the code below 'sfrmClientHistoryDets' is the name of the second
subform
which displays the related data for the record chosen in the first
subform.
Note: replace 'sfrmClientHistoryDets' and 'IssueID ' with your own
objects.

----------------------------
Public Function DoubleClickAction()
On Error GoTo Err_Handler

Dim frm As Access.Form
Dim rs As DAO.Recordset

If Me.RecordsetClone.RecordCount > 0 Then
If Len(Me.IssueID & vbNullString) > 0 Then
Me.Parent.sfrmClientHistoryDets.Visible = True
'Debug.Print Me.Parent.Name
'Debug.Print Me.Parent.sfrmClientHistoryDets.Form.Name
Set frm = Me.Parent.sfrmClientHistoryDets.Form

'set the bookmark for the second subform
Set rs = frm.RecordsetClone
If frm.RecordsetClone.RecordCount > 0 Then
rs.FindFirst "IssueID = " & Me!IssueID & ""
If Not rs.NoMatch Then
frm.Bookmark = rs.Bookmark
End If
End If
End If

Else
Me.Parent.sfrmClientHistoryDets.Visible = False
End If

Exit_Handler:
On Error Resume Next
Set rs = Nothing
Set frm = Nothing
Exit Function
Err_Handler:
Call fnFormErrHandler(pstrProc, pstrMdl, Err)
Resume Exit_Handler
End Function
-------------------------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am not sure this can be done. I have a main form with 2 sub forms. Lets
call them subform1 and subform2.

[quoted text clipped - 10 lines]

Thanks

 

[mattc66 via AccessMonster.com]

I got it to work.

What is the name of the primary key field that is the link between the 2
subforms?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I got it show the subform. However I only want to see the records that are
linked to the ID fld. How would I do that?

[quoted text clipped - 59 lines]