Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a main form which contains two sub-forms A and B which are linked.
    A has multiple employment history records for each staff member including job title.
    B has multiple records detailing how the current job is to be allocated to different budgets for accounting purposes.
    When the user clicks anywhere in a record field in sub-form A, the record(s) in sub-form B associated with the current record in A are displayed.

    The problem is about deleting records in sub-form A.
    When the user deletes a record in sub-form A, sub-form B's records are not updated to be those of the (new) current record in sub-form A.

    How can I make sub-form B refresh to show the correct, corresponding records for the (new) current record in sub-form A, please?

    The form current event routine for sub-form A already contains a Me.Parent![B].Requery statement.
    Thanks
    Silverback
    Silverback

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='silverback' post='793198' date='13-Sep-2009 11:05']I have a main form which contains two sub-forms A and B which are linked. ...[/quote]
    By linked, do you mean that both subforms are contained in the same Main form? If subform B were nested within subform A, then subform B should automatically update when the record being displayed in subform A is deleted. If they are not nested, then you must have some code that synchronizes subform B with subform A. I believe you should be able to run code similar to what you use to synchronize subform B from the OnDelete or the AfterDelConfirm events to resynch to the currently displayed record.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes - both sub-forms are part of a main form - the second sub-form is NOT nested within the first.
    They are linked using the 'Link Child Fields' and 'Link Master Fields' data properties of the subforms; I copied this from the Northwind DB where yjr Customer Orders form uses this method.

    So there isn't actually any code that I've written to sync the sub-forms. Access does it all for me because I've told it that these form fields are linked.

    LATER
    I got this working by putting a requery in the AfterDelConfirm routine, but this then doesn't work if the user switches off Record Changes on the
    Tools | Optons | Edit/Find tab, because the AfterdelConfirm event doesn't happen when this option is switched off. It doesn't error, but sub-form B is not refreshed.

    Also, trying a requery in the OnDelete event does cause an error - No Current Record. I think this is because this event happens before the delete occurs.

    So, nearly there, but not quite. Is there a solution that works in all user circumstances, please?
    Silverback
    Silverback

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Interesting technique which I've never used, linking a subform to another subform, even though they aren't nested. And sorry about the delay in responding - as usual, there were some unexpected things that needed attending to. One of the things you can do is to dynamically set the linking properties of the second subform, and you could try doing that with the OnCurrent event. We try to use that event sparingly however, as in many cases it will fire multiple times on a record change. It also seems like overkill to solve the problem of a record being deleted. Since the OnDelConfirm event seems to work, another approach would be to make sure the Confirm Deletes is always turned on - I haven't confirmed that property can be manipulated with VBA or macros, but the VBA help files indicates that it can.
    Wendell

Posting Permissions

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