How To Synch One Subform With Another

[MikeC]

I'm developing a form that has multiple subforms and I
need to synchronize one subform with another subform. I
have already attempted to do this, but with limited
success. I have managed to *link* subform2 to subform1 by
entering subform1's text box control as the "Link Master
Fields" property in subform2. Here's the expression I
entered in the "Link Master Fields" property of subform2:

Forms!frmSIPMaintenance!Subform1!txtAppNbr

(I've tried this with and without brackets.)


The problem is that although these two subforms
are "linked", they are not *synchronized*. As I navigate
through the records one at a time, *subform2* is always
lagging behind subform1 by one record. Subform2 will only
display the record that is related to the one that was
*previously* displayed in subform1.

I have also tried adding a text box control to the *main*
form and referencing that instead. I get the same
results. In addition, I have tried adding a filter to the
record source query for subform2 that references the text
box on the main form. Again, the results are the same. I
can link, but I am unable to synchronize.

I believe I am unable to synchronize due to the late
timing of when the subform1's data becomes available to
subform2.

Does anyone know of a way that I can successfully
synchronize one subform with another? I am open to
virtually all options, including writing a new ADO
procedure.

Thank you in advance for any helpful tips.

 

[Sandra Daigle]

Hi Mike,

You need a current event in subform1 that requeries subform2 - this is from
Northwind (I think).

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.

There is an example of this on my website at
http://www.daiglenet.com/msaccess.htm - look at the Synch Combo Continuous
Sample database

 

[MikeC]

Thanks Sandra. The requery solved the problem. The
subforms are working perfectly now.

-----Original Message-----
Hi Mike,

You need a current event in subform1 that requeries subform2 - this is from
Northwind (I think).

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.

There is an example of this on my website at
http://www.daiglenet.com/msaccess.htm - look at the Synch Combo Continuous
Sample database



--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I'm developing a form that has multiple subforms and I
need to synchronize one subform with another subform. I
have already attempted to do this, but with limited
success. I have managed to *link* subform2 to subform1 by
entering subform1's text box control as the "Link Master
Fields" property in subform2. Here's the expression I
entered in the "Link Master Fields" property of subform2:

Forms!frmSIPMaintenance!Subform1!txtAppNbr

(I've tried this with and without brackets.)


The problem is that although these two subforms
are "linked", they are not *synchronized*. As I navigate
through the records one at a time, *subform2* is always
lagging behind subform1 by one record. Subform2 will only
display the record that is related to the one that was
*previously* displayed in subform1.

I have also tried adding a text box control to the *main*
form and referencing that instead. I get the same
results. In addition, I have tried adding a filter to the
record source query for subform2 that references the text
box on the main form. Again, the results are the same. I
can link, but I am unable to synchronize.

I believe I am unable to synchronize due to the late
timing of when the subform1's data becomes available to
subform2.

Does anyone know of a way that I can successfully
synchronize one subform with another? I am open to
virtually all options, including writing a new ADO
procedure.

Thank you in advance for any helpful tips.

.