Unanswered: Refreshing multiple subforms in a master form
I know theres been several posts about this subject before, and ive had a look through them but I havent really found an answer which covers what im trying to achieve. One was helpful, but in my instance isnt the most suitable.
I have a form which contains in it several other subforms. They are all unique but all reference the same main table. Therefore if a record is modified/updated in one form, it would affect the results of the other subforms. At present, when updating a record, the only way I can see updated forms is by closing the master form/database and reopening it, which then shows the updated results in each subform.
What I would like to achieve is a Refresh button that runs a Requery/and or Refresh on each subform, which would, in theory, show updated forms.
Ive attached a view of the form to explain it easier....
If I have a record in 'ongoing work', if I changed one of the items on it to 'complete' it would dissapear from the query. I have achieved this by using 'Me.Requery' on 'After Update' which is fine for the single form.
However; there might be a record which is "ongoing", but the deadline is approaching therefore the record might appear in both subforms ('approaching deadline' and 'ongoing work'). If I changed the record field in one of the subforms from 'ongoing' to 'complete', after the 'Me.Requery' has automatically run, the record is still visable in the other subform (until obviously I close the form/database and reopen it).
Also I have the subform at the top which is just a SQL query that counts the number of records that are 'complete' and 'ongoing', which ideally would update upon changes in the other subforms to.
Then theres the unbound textboxes that are all SQL count queries (the big bold numbers)
I tried adding a button that carried the code:
Private Sub refresh_button_test_Click()
...but it always brings back the error it cant find 'ongoing'. I know this code isnt right, but im just guessing really now!
I havent a clue how I could tie all those queries/SQL together to update upon changes. Im basically at the limit of what I can do without some guidance, so any help is appreciated. Sorry for blabbering too!
A list box (a combo box too) is a control that has a Requery method, so you can write Me.Form.Requery or Me.Combo.Requery. Seen from the "main" form, a subform is a control of type SubForm, not another form, and this control does not have a Requery method, though the underlying form has.
When you open a form as a subform, it is not added to the Forms collection of Access, this is why you cannot use the Forms!Subform syntax: SubForm is not a member of the Forms collection (Forms!MainForm!SubForm.Form would be ok, though).
The confusions comes from the fact that the control of type subform has the same name as the form that it represents. In your case, the Requery method concerns the form Subform, not the control Subform.
You can easily verify both assertions.
1) Open the "main" (parent) form in design view, select the subform (just click once on the border) and open the properties window. In the "Others" tab, you can see the "Name" property. This indicates that you are dealing with a control and that you can rename it. Now select the form itself (click on the square in the upper left corner inside the frame that represents the subform): there is no "Name" property in the "Others" tab of the properties window. This is because you cannot rename an open form, even if it's open in design mode.
2) Create an independent module and type in (or paste) this code in it:
Dim frm As Form
For Each frm In Forms
Now open the main form in normal mode, go back to the independent module, open the immediate window and run the function ListOpenForms (hit F5 when the cursor is somewhere inside the function). The name of the subforms that are included in the main form you opened are not present is the list of open forms.