Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Problem with refreshing a subform from a subform.

    Hello,

    I need help with my requery statement. I have two subforms on a mainform. I am trying to perform an update from the control “cboAssignedDuty”, on the “Duty” form and need to refresh or requery another subform called “MasterDutyForm”.

    I was trying to use this requery statement, but it is not working.

    Forms![Duty]![ MasterDutyForm].Requery

    Appreciate your time and help.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Well, I was able to get the Requery function to partly work. Thank you Sinndho for the Syntax document. Now, I have a new problem.

    In my “MasterDutyForm” subform, I have 3 other subforms (Server, Driver and Decorator). There are 3 fields in the individual subforms (LastName, FirstName and AssignedDuty). Each individual subform is attached to a query and filtered on “AssignedDuty”. So, for example, the three subform queries are filtered as follows.

    ServerSubform:
    field: AssignedDuty / criteria: “Server”

    DriverSubform:
    field: AssignedDuty / criteria: “Driver”

    DecoratorSubform:
    field: AssignedDuty / criteria: “Decorator”

    The Requery code (Forms![Duty]![MasterDutysubform].Form.Requery) will update the data in the subforms, but it will not move the updated data to the correct subform.

    Example:
    I update the last duty assigned to Joe by selecting the item “Server” from the “cboAssignedDuty” list on subform1. So, Joe is no longer assigned to “Drive” and is now assigned to “Server”. I need the updated information from “DriverSubform” to repopulate in the “ServerSubform” located on the “MasterDutyForm” (subform2).

    Whew! I tried to be as detailed as possible, but may have caused more confusion than intended.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could go on using the same syntax, but the is a more elegant way of doing it by using the Current event of the MasterDutyForm subform, like this:

    Code:
    Private Sub Form_Current()
    '
    ' This is the Current event of the MasterDutyForm subform
    '
        Me.ServerSubform.Form.Requery
        Me.DriverSubform.Form.Requery
        Me.DecoratorSubform.Form.Requery
    
    End Sub
    This works because when you requery a form (be it a subform or not) it triggers the Current event of this form, so the chain of events is like this:

    [MainForm] --(Requery)--> [SubForm:Current Event] --(Requery)--> [SubSubForm: Current Event]
    The same is true for the MainForm/MasterDutyForm: as the code is to be executed in the module of a fom, you can reference this form using the Me.Subform syntax in place of the Forms!Subform one. According to the Access documentation it's supposed to be faster (not sure about that!) but it's more readable, at least to me.

    As far as performance is in cause, in theory it would be more efficient to use ListBox objects instead of sub-subforms, at least if you don't need to edit data in them. Nesting forms on several levels degrades the performances of an application because the Form object (here used as a SubForm control) is "heavier" than a ListBox, but it's generally not an issue.

    Have a nice day!

  5. #5
    Join Date
    Sep 2008
    Posts
    150
    Hello again Sinndho.

    I placed the code (below) in the second subform (MasterDutyForm) with the multiple subforms but it is not working. I get a compile error "Method or Data member not found". I attempted to modify the code with no success.

    Private Sub Form_Current()

    Me.Serversubform.Form.Requery
    Me.Driversubform.Form.Requery
    Me.Decoratorsubform.Form.Requery

    End Sub

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Drop the .Form and it should work, have a look at the attached file.

    Have a nice day!
    Attached Files Attached Files

  7. #7
    Join Date
    Sep 2008
    Posts
    150
    Sinndho, I was able to get the requery code to work (thank you). The data in the sub-subforms does update based on my combo selection, but the data does not move to the appropriate filtered sub-subform

    If you don’t mind, can you please take a look at the attached db to see what I am trying to do?

    If you search by employee name the LastName, FirstName and Assignment fields are auto populated with the selected employee name.

    If you select an item from the Assigned Duty combo box to reassign an employee, the employee’s assignment in one of the sub-subforms will update with the new Assignment. This is all good. Now, how can I get the new assignment data to move to the appropriate sub-subform?
    Attached Files Attached Files

Posting Permissions

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