Link to home
Create AccountLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

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

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.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
Avatar of stephenlecomptejr

ASKER

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
Appreciate the replies.  Sorry for the confusion.  Should have posted (and read) the error message - not just say it wasn't working.  LOL.
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?
<<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
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!