Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2003
    Location
    China
    Posts
    54

    Unanswered: Reload (refresh, requery, recalc) records in a subform

    Hi,

    I have a form with 2 subforms in it (they are at the same level, one subform is not a subform of the other) as follows:
    Parent: StudentForm
    Subform: OrderSub (record source is a table of orders)
    Subform: OrderTotSub (record source is a query showing totals of the orders table).

    Basically when records in the OrderSub are changed, added or deleted, I need the OrderTotSub to reload (or refresh, requery, recalc) the records so that the new totals can be displayed without having to close and open the whole form.

    I can successfully use the following code linked to AfterUpdade event in controls in the StudentForm (i.e. Parent form), but when I try a similar code linked to the OrderSub (i.e. the other subform - which is where I need this) nothing seems to happen:
    ----------------------------
    Private Sub GivenName_AfterUpdate()
    Forms![StudentForm].[OrderTotSub].Form.Requery
    End Sub
    -----------------------------
    Can anyone help me please? I have searched for ages with no luck.

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Try this:
    In MainForm
    .................................................. ............................
    Private Sub GivenName_AfterUpdate()
    GetName
    End Sub
    .................................................. .........................
    Sub GetName()
    [Forms]![StudentForm]!...........................
    Exit Sub
    End Sub
    .................................................. .........................
    In subform (VBA) put

    Me.Parent.GetName

  3. #3
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    Thanks for the suggestion, but that doesn't work either.

    Do you have any idea why the other subform event can't properly run the code that the parent can run? Is it possible that I may have somehow done something to the controls on the subform that could affect the running of the code do you think?

    Richard.

  4. #4
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    Actually, thinking more about this... the 2 subforms share the same core data source so would the focus first need to be moved away from the first subform to make the requery?

  5. #5
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Are you sure that the code (VBA) in subform reach the point
    where you put "me.Parent.GetName" ???

  6. #6
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    I am not 100% (not too good at Access and VBA)...I put the following in the subform AfterUpdate event:
    -----------------------
    Private Sub Uniform_AfterUpdate()
    Me.Parent.GetName
    End Sub
    -----------------------
    Wouldn't this make sure that GetName was run from the Parent?

  7. #7
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    I can't knwo that, you must know that.

  8. #8
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    OK, MStef, it doesn't look like your suggestion works.

    Now I am doing a work-around which works as follows (but leaves the focus set on the Parent form's Control1 rather than back at the original subform's ControlA:
    -----------------------
    Private Sub ControlA_AfterUpdate()
    Me.Parent.Control1.Locked = No
    Me.Parent.Control1.SetFocus
    Forms![StudentForm].[OrderTotSub].Form.Requery
    End Sub
    -----------------------
    I tried inserting....
    Forms![StudentForm].[OrderSub].ControlA.SetFocus
    .... after the requery, but I keep getting error messages.
    Any ideas?

  9. #9
    Join Date
    Jun 2007
    Posts
    33
    Hi there,
    i had the same problem one day...
    it seems that, some times (with huge table's), access take some time to update the record that are displayed in the main form and some times this action generate errors.

    What i did was to creat a cicle that would refresh the subform until the "change" was comited.
    Example:
    i change in Mainform some text to "newtext", then in a button not on an event, run the follwing code:

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,acMenuVer70 'i save the record in main form

    do until subform![textbox].text="newtext"
    subform.requery 'or repaint or refresh
    exit do
    Sorry my English

  10. #10
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    PedroF,
    sorry for slow reply - have been travelling.
    thanks for this... it is great to hear I am not the only one with this problem.
    As I am a weekend Access user I am not familiar with your coding, however I could copy and paste no problem, however it looks like for this I would need to click the button to run the code... is there anyway it could just run from an event (to automate it better)?
    At the moment I have the following:
    ----------------------------------------
    Private Sub Uniform_AfterUpdate()
    Me.Parent.GivenName.Locked = No
    Me.Parent.GivenName.SetFocus
    Forms![Student].[QStudentOrderTotal].Form.Requery
    Forms![Student].[QStudentOrderSub]!Uniform.SetFocus
    Forms![Student].GivenName.Locked = Yes
    End Sub
    ------------------------------------------
    This is updating the record from the event but the cursor is ending up back on the control in the mainform, which means have to reclick the subform to change EACH part of the record.

    Richard.

  11. #11
    Join Date
    Jun 2007
    Posts
    33
    Hi
    The AfterUpdate() event end's when the form that triggers AfterUpdate() get the focus again.
    Private Sub Uniform_AfterUpdate()
    Me.Parent.GivenName.Locked = No
    Me.Parent.GivenName.SetFocus
    Forms![Student].[QStudentOrderTotal].Form.Requery
    Forms![Student].[QStudentOrderSub]!Uniform.SetFocus
    Forms![Student].GivenName.Locked = Yes
    End Sub '<= focus go to the form that triggers the AfterUpdate()

    For example, if you insert in the code some like:
    Private Sub Uniform_AfterUpdate()
    docmd.close 'MainForm
    End sub
    it will generate an error because in End Sub the focus wil go to the form that is closed...

    Is the same thing in your code, if you use AfterUpdate() the focus will always go to the form the triggers the event not to other form.

    Hope it helps

  12. #12
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    PedroF,
    Great info! I will try using another event to trigger the code and get back to you on how it goes.

Posting Permissions

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