We help IT Professionals succeed at work.

Need MS Access VBA code to requery subform while open in a main form.

98 Views
Last Modified: 2018-11-30
I need the correct syntax for a Microsoft Access VBA set of code that would allow a main form - requery a subform's information.
This used to work for me in the past (Access 2000-2013) but I'm using Office 365 now.

Forms!fMainMenuTimeSheetMonthly!sfTimeSheetWeekly.Form.Requery
Forms!fMainMenuTimeSheetMonthly!sfTimeSheetWeekly.Visible = True

Open in new window


Primarily I have a lot of Me.Controls("txtBox1") in the subform to control from the main form.

This works within the subform itself:

Me![sfTimeSheetWeekly].Requery
Me![sfTimeSheetWeekly].Visible = True

Open in new window


But I need to manipulate it from outside the main form.
Comment
Watch Question

President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
Come to think of it, I think I remember an issue between using the bam (!) and using the dot (.) between form and control names.  (A change?)
I've also had this work fine:
Forms("fMainMenuTimeSheetMonthly").sfTimeSheetWeekly.Form.Requery

Author

Commented:
You know what it actually was.  I was calling this from within the subform only..... and thus the main form wasn't open... thus the error... can't find the main form.

My apologizes.  But if it comes up - at least this will be in the search

Author

Commented:
Appreciate the replies.  Sorry for the confusion.  Should have posted (and read) the error message - not just say it wasn't working.  LOL.

Author

Commented:
Hey Mark,

I see how you could pass a variable name as the form with that above code doing:

Dim sFormName as String
sFormName = "fMainMenuTimeSheetMonthly"
Forms(sFormName).sfTimeSheetWeekly.Form.Requery 

Open in new window


But how do you also pass the subform name along with the formname in as a variable with the above?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<But how do you also pass the subform name along with the formname in as a variable with the above>>

  You want control name, and it would be:

Forms(sFormName)(ssfFormName).Form.Requery

and a small fact; under the hood, Access converts all references to this format, which it then executes.   So using this format is actually a tad faster as it won't have to translate.

and a lot of objects will support that syntax.

Jim
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
If you want to combine them as one name, you could set the subform as an object variable.  For example:
Dim objSubform as form
Set objSubform = Forms("fMainMenuTimesheetMonthly").sfTimesheetWeekly.form
Then all you have to do is use the object variable:
objSubform.Requery

I've tested it and it works!