Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Location
    Canada
    Posts
    2

    Unanswered: Sync two Subforms based on one Query

    I have a form with three subforms, each one of them based on a query. Subforms two and three are based on the same table/query.

    Why not use just one subform if the record source for subform2 and subform3 is exactly the same? Because I am copying the paper form we have to fill during a home visit, making it more user friendly for other volunteers to fill it up. Also subform2 has financial assessment data and subform3 the type and amount of help we are providing to the family with comments in a memo field, so different type of data for the same visit day.

    Main table/query primary key is set OK and one-to-many relation using “link child field” and “link master field” is working fine. I am using an Option Group control in the main form to toggle between the three subforms, leaving name, address, phone, etc... unchanged on the main form all the time, and this is working fine as well.

    The problem arises when I move between the records using the navigation buttons in the lower-left corner of the subform. If we are looking record#3 in subform2 and we toggle to subform3 we start looking record#1, which is a different visit day. I have to advance to the same record/day using the navigation buttons again. I actually have the field “Event_Date” on both subforms.

    Can anyone please give an idea, what is the easier and fastest way to sync both subforms?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A couple of options I can think of offhand:

    1. Consider making one of the subforms a listbox, especially if no data is being updated and you simply want to view the data.
    You could then use criteria in the 2nd subform to equal the 1st column of the listbox (which would have the field that is common between the subform and listbox such as an ID type field.)

    2. Possibly use criteria in the subforms so that the key ID type field is equal to an unbound field on the main form. Then when you go to a record in one of the subforms, you populate the mainform's unbound textbox with the ID value and issue a requery command to the other subform.
    ex (when going to a record on one of the subforms):
    Forms!MyMainFormName!MyUnboundRecordIDField = me!MyRecIDField
    Forms!MyMainFormName!MyOtherSubformName.requery

    and the recordsource of the 2nd subform (and maybe the 1st subform) would have criteria in it under the MyRecIDField such as =Forms!MyMainFormName!MyUnboundRecordIDField. You could make it so record navigation in the subforms actually takes place in a listbox or combobox on the mainform.

    Otherwise, this may be a bit tricky to do if you're actively using both subform's to navigate through records but you could make it work (you'd probably want to put the code in the OnCurrent event of the subform) But I would again, suggest making the navigation of the subforms happen on the mainform itself and use criteria in both of the subforms as stated above.

    3. Or you can also possibly just 'reset' the sourceobject of the subform (again, with criteria in the recordsource of the subform):
    Forms!MyMainFormName!Subform1.SourceObject = "Subform1Name"

    4. Or you can use the docmd.FindRecord command to find the ID type field on the 2nd subform.
    ex:
    dim varMyIDField as variant
    varMyIDField = me!MyIDField
    Forms!MyMainFormName.setfocus
    Forms!MyMainFormName!My2ndSubformName.setfocus
    Forms!MyMainFormName!My2ndSubformName!MyIDField.se tfocus
    Docmd.FindRecord varMyIDField

    5. Consider making one of the subforms a 'popup' form instead of an actual subform.

    Remember though that if you're actively updating data in one of the subforms, you'll want to issue a Refresh command and then requery the 2nd subform (otherwise you'll get the infamous 'copy data to clipboard' type message.)
    Last edited by pkstormy; 04-18-10 at 22:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2010
    Location
    Canada
    Posts
    2

    Trying docmd.FindRecord to Sync subforms

    Thank you very much for your help pkstormy. Since we are going to update or enter data to the subforms I implemented your option #4.

    It is working just one way. When I move between the records on subform3 and toggle to subform2 works fine. Subform2 displays exactly the same day we were at subform3. But when I try to change from subform2 back to subform3 there is a run-time error 438 – Object doesn’t support this property or method. Why do you think works only one way?

    The code is:
    Option Compare Database
    Dim stRecNr As Variant

    Sub DisplaySubform()

    DoCmd****nCommand acCmdSaveRecord

    If Frame41 = 1 Then
    FamilyMembers_subform.Visible = True
    FamilyFinancial_SubForm.Visible = False
    FamilyAssistance_SubForm2.Visible = False

    ElseIf Frame41 = 2 Then
    FamilyMembers_subform.Visible = False
    FamilyFinancial_SubForm.Visible = True
    FamilyAssistance_SubForm2.Visible = False
    stRecNr = Forms!FamilyAssistance_Form2!FamilyAssistance_SubF orm2!Event_Date
    Forms!FamilyAssistance_Form2.SetFocus
    Forms!FamilyAssistance_Form2!FamilyFinancial_SubFo rm.SetFocus
    Forms!FamilyAssistance_Form2!FamilyFinancial_SubFo rm!Event_Date.SetFocus
    DoCmd.FindRecord stRecNr

    ElseIf Frame41 = 3 Then
    FamilyMembers_subform.Visible = False
    FamilyFinancial_SubForm.Visible = False
    FamilyAssistance_SubForm2.Visible = True
    stRecNr = Forms!FamilyAssistance_Form2!FamilyFinancial_SubFo rm!Event_Date
    Forms!FamilyAssistance_Form2.SetFocus
    Forms!FamilyAssistance_Form2!FamilyAssistance_SubF orm2.SetFocus
    Forms!FamilyAssistance_Form2!FamilyAssistance_SubF orm2!Event_Date.SetFocus
    DoCmd.FindRecord stRecNr

    Else
    FamilyMembers_subform.Visible = False
    FamilyFinancial_SubForm.Visible = False
    FamilyAssistance_SubForm2.Visible = False

    End If

    End Sub

    Private Sub Form_Current()

    DisplaySubform

    End Sub

    Private Sub Frame41_AfterUpdate()

    DisplaySubform

    End Sub

    Private Sub Command228_Click()
    On Error GoTo Err_Command228_Click

    If Me.Dirty Then
    Me.Dirty = False
    End If

    DoCmd.Close

    Exit_Command228_Click:
    Exit Sub

    Err_Command228_Click:
    MsgBox Err.Description
    Resume Exit_Command228_Click

    End Sub

    Public Function OpenOptionsDialog() As Boolean

    On Error GoTo Error_OpenOptionsDialog

    DoCmd****nCommand acCmdOptions
    OpenOptionsDialog = True

    Exit_OpenOptionsDialog:
    Exit Function

    Error_OpenOptionsDialog:
    MsgBox Err & ": " & Err.Description
    OpenOptionsDialog = False
    Resume Exit_OpenOptionsDialog

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •