Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syncronising records on sub forms (2000)

    Do you ever wish you hadn't started something!!

    I have a form with two continuous sub forms both sub forms are child/master linked to the main form by a PO No.

    At the moment if I select a PO Number then boht forms display the approprite sub form data.

    However, if I select an record on one sub form I have to scroll to the matching record on the second form.

    They both contain a mathing field called Item no which originates from the table the first sub form is based upon. I Don't really want to make the second sub form a child of the first sub form because I want to see all the records associated with the PO number.

    Is there anything I can do to make them scroll in a syncronised fashion?

    Thanks

    Roberta

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syncronising records on sub forms (2000)

    In design view create an unbound text box the main form. Set its data source equal to the PO number on the first subform using this reference (your own form name needs to go into this!): =[sfrmPO].Form![PO Number] . Now set the second subform to link to this text box. Open it in design view and link the master field to this text box and the child to the [PO Number]

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Syncronising records on sub forms (2000)

    I'm not sure Preston's answer will do what you want. Just to be sure I'm correct in my assumptions, you have the side-by-side subforms, which each contains multiple records. When you select a record in subform1, you want the same record to be highlighed in subform2, correct? (rather than only just displaying 1 record in subform 2).

    If this is the situation, then I think you'd need some code in the OnCurrent event on subform1. It would be something like this (you will need to fill-in the specific details):
    Me.parent.subform2.form.recordsetclone.findfirst ".... your info here..."
    Me.Parent.subform2.form.bookmark = me.parent.subform2.recordsetclone.bookmark
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syncronising records on sub forms (2000)

    If the two subforms match record for record, is there a specific reason not to combine them into one subform?

    What you want can be done using a variation on the technique described in ACC 2000: Using Code to Dynamically Synchronize Two Forms. An extra twist is needed because the subforms can't communicate with each other when the main form is first opened. Here are the steps:
    <UL><LI>Define a public variable in a standard module (not in one of the form modules):

    Public fReady As Boolean

    <LI>Write code in the On Current event of the first subform to synchronize the second subform with it:

    Private Sub Form_Current()
    ' Dimension variables.
    Dim F As SubForm
    Dim rs As Object

    ' If the other subform is not ready, then exit.
    If Not fReady Then
    Exit Sub
    End If

    ' If the Item is blank, then exit.
    If IsNull(Me.[Item]) Then
    Exit Sub
    End If

    ' Define the Subform object and Recordset object for
    ' the other subform.
    Set F = Me.Parent!NameOfSecondSubform
    Set rs = F.Form.Recordset.Clone

    ' Try to find the Item.
    rs.FindFirst "[Item] =" & Me.[Item]

    'If a record exists in the other subform, move to it.
    If rs.EOF Then
    MsgBox "No match exists!", vbExclamation
    Else
    F.Form.Bookmark = rs.Bookmark
    End If

    ' Clean up
    Set rs = Nothing
    Set F = Nothing
    End Sub

    Replace "Item" and "NameOfSecondSubform" by the actual names of the field to match on and of the second subform (as a control on the main form.)

    <LI>Write code in the On Current event of the second subform to indicate that it's ready:

    Private Sub Form_Current()
    fReady = True
    End Sub

    <LI>Write code in the On close event of the main form to set fReady to False for the next time:

    Private Sub Form_Close()
    fReady = False
    End Sub[/list]I hope that this will do what you want.

Posting Permissions

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