linked subforms

[osti]

Posted once before on this issue. Still having an issue
though.

I just want to link two subforms that are based on tables
that have a master detail relationship based on a common
key field. One key issue to note is that I do not want
the second (detail) subform created on the first subform.
These are two subforms on a main form. I'm doing this
because I want both the subforms to be datasheet views
where when I click on a record on the master subform, it
automatically queries the second detail subform. The
wizard does not work in this instance because the second
subform is not on the first (master) subform.

I have tried setting the Link Child Fields and Link Master
Fields properties. The result is that it displays the
master data sheet with all the records, but seems to link
the detail just to the 1st record of the master. If I
click on other rows of the master, the detail subform does
not update with the related records.

When this didn't work, I attempted to apply a criteria
value to the key field of the query that I had associated
with subform2. The criteria referenced the form field on
subform1 (master) that I wanted to limit the detail based
on. When I run the main form, I just get records for the
master datasheet and no records at all for the detail.

Anyway, I know this should be easy to resolve... I must
just be missing something basic.

Thanks,

Dave

 

[Sandra Daigle]

Hi Osti,

There is an example of this in Northwinds and also in my
SynchComboContinuous example on http://www.daiglenet.com/MSAccess.htm.

The method I like is uses a hidden textbox on frmA. This textbox on the main
form has it's ControlSource set to refer to the control you want to use to
link the two subforms. Name this textbox txtOrderId (or something more
meaningful).

Controlsource =sfrmB.Form.Orderid
visible=false

Note that 'sfrmB' above must be the name of the subform control on the
mainform - this is not necessarily the same name as the name of the form
object that you see in the database window. To be sure, select the subform
control, then look at the Name property under the Other tab. This is the
name that should be used in all references to the subform.

To synchronize, set the LinkMasterfield property of sfrmC to txtOrderid
(skip out of the linking wizard because this control will not be offered in
the list - however you are not limited to the field list that the wizard
offers, you can type in the name of any control on the main form. Set the
linkchildfield property of the subform to Orderid.

Another similar method is shown in the Northwind sample database. Look at
the Customer Orders form in Northwind, [Customer Orders Subform1] and
[Customer Orders Subform2] are subforms of [Customer Orders] and they are
synchronized so that the order details in Subform2 belong to the current
order in Subform1. The key to synchronizing
[Customer Orders Subform1] and [Customer Orders Subform2] is that the
linkMasterfields on [Customer Orders Subform2] refers to a control on
[Customer Orders Subform1]

[Customer Orders Subform2]
LinkChildFields=Orderid
Linkmasterfields=[Customer Orders Subform1].Form![OrderID]

Then you also need a current event on the Subform 1:

Sub Form_Current()
' This code created by Form Wizard.
Dim strParentDocName As String

On Error Resume Next
strParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![Customer Orders Subform2].Requery
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Err.Description
Resume Form_Current_Exit


Another similar way to synchronize two subforms is by using a hidden textbox
on the main form. This textbox on the main form has it's ControlSource set
to refer to the control you want to use to link the two subforms. Name this
textbox txtOrderId (or something more meaningful).

Controlsource =[Customer Orders Subform1].Form.Orderid
visible=false

Note that '[Customer Orders Subform1]' above must be the name of the subform
control on the mainform - this is not necessarily the same name as the name
of the form object that you see in the database window. To be sure, select
the subform control, then look at the Name property under the Other tab.
This is the name that should be used in all references to the subform.

To synchronize, set the LinkMasterfield property of[Customer Orders
Subform2] to txtOrderid (skip out of the linking wizard because this control
will not be offered in the list - however you are not limited to the field
list that the wizard offers, you can type in the name of any control on the
main form. Set the linkchildfield property of the subform to Orderid. Note
that this is essentially the same method as is used in Northwind - it is a
little more indirect but for testing, you can display the value of
txtOrderid and see that it is working.