Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Pembroke Pines, Florida, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying new records in a sub form (Access 2002 SP-1)

    Hi all,

    I read a few recent messages on this, but could not find exactly what I needed.

    I have a main Company form, with a tabbed control containing several sub forms (Employees, Products, etc). Each sub form has a command button (Add New) which calls a different form to add a new child record.

    My problem is that once the new child record is added (say a new Employee), the main form (Company) does not display the newly added child record in the tabbed control. I have tried using DoCmd.Refresh on the Got_Focus event of the form, on the After Change event of the tabbed control, on the On Enter event of the subform control. All to no avail.

    The newly added child record DOES display once I exit the main record and return to it, but for the life of me I cannot figure out what event (or even what code since I'm not sure that DoCmd.Refresh is it) to add code to to display the new record.

  2. #2
    New Lounger
    Join Date
    Jan 2003
    Location
    Pembroke Pines, Florida, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying new records in a sub form (Access 2002 SP-1)

    Hi Cecilia,

    Thanks for the prompt reply. To answer your questions:

    I have not tried Recalc or Repaint. I can try but under what event?

    I believe I am refreshing the main form since I added DoCmd.Refresh to the Got_Focus event of the main form. Is this not a correct assumption?

    I have not tried the Before Update nor After Update events. Should these be for the subform or the main form?

    Thinking through it some more, there's really 3 forms involved: A main form (single), a tabbed control with an imbedded (continuous) subform, and a Data Entry (single) form that is called by a command button within the subform. So the gist of the problem is, how do I update/refresh the subform to display all records (including the new ones) once the Data Entry form exits?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying new records in a sub form (Access 2002 SP-1)

    I always get these confused, too. I would think that Refresh would work, but have you tried Recalc or Repaint? Also, are you sure you're refreshing the main form, not the subform (and is the appropriate form triggering the event)? As a final try, have you tried either Before Update or After Update?

    HTH,

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Displaying new records in a sub form (Access 2002 SP-1)

    Try a Requery of the main form (I think it would be the Main form), else try a Requery on each of the SubForms.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    New Lounger
    Join Date
    Jan 2003
    Location
    Pembroke Pines, Florida, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying new records in a sub form (Access 2002 SP-1)

    Pat,

    Tried the DoCmd.Requery on the Got_Focus event of both the main form and the sub form at separate times. Unfortunately, neither attempt worked. [img]/forums/images/smilies/sad.gif[/img]

  6. #6
    New Lounger
    Join Date
    Jan 2003
    Location
    Pembroke Pines, Florida, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying new records in a sub form (Access 2002 SP-1)

    Cecilia,

    I think it is boiling down to which event to use.

    As I mentioned to Pat, I have tried the Got_Focus event on both the main form and the subform to no avail.

    Your understanding of the process is correct.

    Another way to approach this: The subform correctly displays all records linked to the main form when the main form first opens; so, how about forcing a trigger of this event (whatever it may be) when the data entry form closes? If that sounds plausible, then how can I call this event from the data entry form close event (ie. what is the proper syntax for it)?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying new records in a sub form (Access 2002 SP-1)

    I think you might be right--it may be the event. Are you triggering a got focus event for the form you're trying to refresh? Even so, I have noticed that when I try to work off this event, it works a little differently in code than it does in practice.

    Here's some info from Help on the requery thing:
    The Requery action does one of the following:
    Reruns the query on which the control or object is based.
    Displays any new or changed records, and removes any deleted records from the table on which the control or object is based.

    The Refresh method immediately updates the records in the underlying record source for a specified form or datasheet to reflect changes made to the data by you and other users in a multiuser environment.

    Re: Before update and after update, it sounds like I was referring to your data entry form. From what I understand:

    User opens main form and sees tab control, one page of which has a subform. The subform has a button that a user clicks to add a record. Then the enters the data on the data entry form, and closes that form (could the data entry form's close event possibly be a triggering event for the refresh of the subform?). When the user closes the data entry form, the new record should appear in the subform.

    As for how to do it, my suggestion would be to see if you can come up with a way to simplify that form design. It sounds _really_ complicated. Then make sure you're using an event that you're triggering (ie. if you're doing work on a subform, then the event that triggers will be on the subform) and recalculating the correct main form.

    HTH,

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying new records in a sub form (Access 2002 SP-1)

    >>The subform correctly displays all records linked to the main form when the main form first opens; so, how about forcing a trigger of this event (whatever it may be) when the data entry form closes? If that sounds plausible, then how can I call this event from the data entry form close event (ie. what is the proper syntax for it)?<<

    In the Data Entry form, Close Event, try something like this:

    Forms!frmMainForm!frmSubform.Refresh (if you're using requery, it sounds like you need a DoCmd. at the beginning)

    Post 46648 has similar information. I'm not sure, however, how you reference that subform when it's within a tab control.

    HTH,

  9. #9
    New Lounger
    Join Date
    Jan 2003
    Location
    Pembroke Pines, Florida, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying new records in a sub form (Access 2002 SP-1)

    Eureka!

    Added the following code to the btnSave_Click event of the Data Entry form (because I only wanted the code to execute if the user actually saved a new record)

    Forms!MainFormName!subformControlName.Requery

    When I returned to the mainform, the subform had been updated with the new record.

    Thank you much! [img]/forums/images/smilies/smile.gif[/img]

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying new records in a sub form (Access 2002 SP-1)

    Congrats! Identifying the problem always seems to be 80% of the solution. Good luck with your project,

Posting Permissions

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