Unanswered: Problem with refreshing a subform from a subform.
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.
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.
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.
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.
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:
Private Sub Form_Current()
' This is the Current event of the MasterDutyForm subform
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.
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.
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?