Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SubForm Synchronization Variation (A2K)

    I have, as always, a variation on a standard problem. Here goes:
    I have a main form used to input Purchase Order information and controlling on PO number.
    I have one subform called subPOForm, which is a Single Form, with qryPODetails as the datasource.
    I have a second subform called subPODataSheet, which is a Datasheet form, also with qryPODetails as the datasource.
    Both these forms are linked to the parent form via PO Number and they work very nicely.
    What I am try to do is:
    When I move from one detail record to the next on subPOForm, I want to synchronize to the same detail record on subPODataSheet based on PartNo and vice versa.
    That is, if I click on one of the detail records on subPODataSheet, I want that record with the matching PartNo to appear on the subPOForm.
    I'm aware of the old hidden text box on the main form trick to control the relationship between the subforms and the main form, but that doesn't apply in this case as I'm trying to synch the two subforms to each other on PartNo, which in turn are already synched to the main form on PO Number.
    As always, thanks in advance for any help,
    Cheers,
    Andy

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubForm Synchronization Variation (A2K)

    I would *try* using the OnCurrent event of the first subform to capture the PrimaryKey of that subform into a variable. Then set the Focus to the correct field of the second subform, and use DoCmd.FindRecord (is that the correct method...?? you get the idea) to move to the desired record based on the value that you saved from the first subform. Finally, you could set the focus back to the first subform.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubForm Synchronization Variation (A2K)

    Andy, when synchronizing between subforms, establishing the relationship shouldn't be hard, and in your On Current event you should be able to use the code:

    Me!SubFormName.Form!Control.Requery

    To refresh your subforms. Since the data source is a query, you can set your query criteria to

    [forms]![mainForm]![subPOForm]![PartNo]

    so that it will match.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubForm Synchronization Variation (A2K)

    Jack & Jeremy,

    Thank you both for your quick replies. I've got to take off for a couple of hours to do some shopping. Apparently Christmas is coming up some time soon and I have numerous children, grandchildren, ex-wife, current wife and assorted others to shop for, so I'll be back! Probably after lunch and when I'm finished with the Christmas baking,
    Cheers,
    Andy

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

    Re: SubForm Synchronization Variation (A2K)

    See the attached demo, based on the Categories and Products tables from the Northwind database. The On Current event of the subforms uses a recordsetclone of the other subform to synchronize.

    Notes:
    1) In Access 2000, you may have to set a reference to the Microsoft DAO 3.6 Object Library in Tools | References... for the code to work.
    2) The On Current event procedures exit without a message if an error occurs. This is necessary because of timing problems that occur when the main form is opened.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubForm Synchronization Variation (A2K)

    Hans,

    Me back with interesting situation. Firstly, thanks for the attachment. I thought that I had followed it to a T, but.......
    If I click on the Next Record key in the first subform, there is no record movement in the 2nd subform. However, when I do the same on the 2nd subform, the first subform merrily follows along, record by record.

    The example which you attached works perfectly. Just to make sure, I imported the tables and forms into my database just to makie sure that it wasn't something missing in mine.

    I'm going to go back to this again, but I just thought that I'd let you know that I was following up,

    Part Deux: also made sure that both forms looked at All Records not just Current Record. Made no difference.
    Cheers,
    Andy

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

    Re: SubForm Synchronization Variation (A2K)

    Did you put code in the On Current event for both subforms?
    Did you make sure that you used the name of the subforms as controls on the main form? This is not necessarily the same as the name of the subforms in the database window.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubForm Synchronization Variation (A2K)

    Hans,

    I have the most wonderful off-colour description with respect to my ability to check and double-check. However, 'tis the season to be kind even to myself.
    In spite of the fact that I thought that I had double checked for the presence of the On Current in both subforms, they alluded me. Again.
    Hans, thanks again for your help and I do wish you and yours a very Merry Christmas and a Happy New Year.

    Same goes to all of the others that have helped this year,
    Cheers,
    Andy

Posting Permissions

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