Requery a subform when another subform's control is updated (1 Viewer)

terrytek

Registered User.
Local time
Today, 09:24
Joined
Aug 12, 2016
Messages
75
I have a form with two subforms (sbf1 and sbf2). Sbf1 has two purposes (1) to show each record of tutoring hours by date and (2) act as a data entry form for new tutoring hours. Both sbf1 and sbf2 are linked to the main form by StudentID. sbf2 is a form that displays the total tutoring hours.

I would like sbf2 to requery (and therefore update its total hours) as soon as a new entry is made in sbf1. I have tried to write code to requery sbf2 in the AfterUpdate property of txtTutoringHours on sbf1, but can't get it to work. sbf2 shows the correct total once the main form is closed and reopened, but I would like it to show the correct total "on the fly".

Thank you.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 09:24
Joined
Apr 27, 2015
Messages
6,280
We would need to see you AfterUpdate code to be able to help...
 

terrytek

Registered User.
Local time
Today, 09:24
Joined
Aug 12, 2016
Messages
75
We would need to see you AfterUpdate code to be able to help...

I didn't post the code because I had tried several different things, none of which worked.

The latest iteration (in the AfterUpdate event of sbf1):

Private Sub Form_AfterUpdate()

Me.sbfPairHoursSinceLastTest.Form.Requery
End Sub

throws error "Method or data member not found".
 

sneuberg

AWF VIP
Local time
Today, 06:24
Joined
Oct 17, 2014
Messages
3,506
I'm not sure about this but this would be easy if done from the parent form so I wonder if something like:
Code:
Me.Parent.sbfPairHoursSinceLastTest.Form.Requery

would work.
 

sneuberg

AWF VIP
Local time
Today, 06:24
Joined
Oct 17, 2014
Messages
3,506
If my previous suggestion doesn't work try using the fully qualified reference which would be in the form

Code:
Forms!Mainform!Subform1.Form.Requery

if I am interpreting these reference examples correctly.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 09:24
Joined
Apr 27, 2015
Messages
6,280
Sneuburg's second suggestion hits the nail on the head. Referencing sub form controls are tricky and the syntax has to be exact. The "me." Bit only applies to parent form controls and cannot be included when referencing a subform.
 

terrytek

Registered User.
Local time
Today, 09:24
Joined
Aug 12, 2016
Messages
75
I tried
Code:
Private Sub Form_AfterUpdate()
    
    Forms!frmPair!sbfPairHoursSinceLastTest.Form.Requery
End Sub
but am getting error "Microsoft Access can't find the field 'sbfPairHoursSinceLastTest' referred to in your expression.

The main form is frmPair, the subform I want requeried is sbfPairHoursSinceLastTest, and the subform where this VBA code is located is sbfPairHours. If I close the main form and reopen it, sbfPairHoursSinceLastTest then shows the correct value.

I am absolutely positive the form names are spelled/capitalized/spaced correctly. Why is the error referencing a field? Is that just generic, or is Access expecting a field name instead of an object name?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 09:24
Joined
Apr 27, 2015
Messages
6,280
If sbfPairHoursSinceLastTest is indeed a subform, therein lies your problem. Requery only works on a forms control (field, txtbox, combo box, etc). That is why you are getting the error you are getting...
 

sneuberg

AWF VIP
Local time
Today, 06:24
Joined
Oct 17, 2014
Messages
3,506
The Subform1 in the following model has to be the subform control name not the name of form in the control.
Code:
Forms!Mainform!Subform1.Form.Requery

Is sbfPairHoursSinceLastTest the name of a form or the name of subform control? If it's the name of a form then that's the problem.
 

terrytek

Registered User.
Local time
Today, 09:24
Joined
Aug 12, 2016
Messages
75
Yep, that was the problem. My subform CONTROL did not have the same name as my subform. (Not sure how/why Access named the control differently from the name of the subform, but anyway). So now all works well thanks to Nautical Gent & sneuberg!!

The take-away here is: make sure you know the name of your subform control on the main form, because it may not have the same name as your subform.
 

doco

Power User
Local time
Today, 06:24
Joined
Feb 14, 2007
Messages
482
The Subform1 in the following model has to be the subform control name not the name of form in the control.
Code:
Forms!Mainform!Subform1.Form.Requery

Is sbfPairHoursSinceLastTest the name of a form or the name of subform control? If it's the name of a form then that's the problem.

It's worthy of note, if you create and embed a subform based on an existing form, the name of that form will not show up in the navigation pane.

EG. If you have a form named f_someformname. Then embed a subform control in a form based on that form and name it sf_someformname. sf_someformname will not show up in the navigation pane. So, if you continue to believe your subform name is f_someformname, forgetting you renamed a clone of that form, no amount of VBA will work. :banghead:

Ask me how I know.

It's interesting that none of the cloned forms show up in the navigation pane. Why?:confused:


doco
 

sneuberg

AWF VIP
Local time
Today, 06:24
Joined
Oct 17, 2014
Messages
3,506
It's worthy of note, if you create and embed a subform based on an existing form, the name of that form will not show up in the navigation pane.

EG. If you have a form named f_someformname. Then embed a subform control in a form based on that form and name it sf_someformname. sf_someformname will not show up in the navigation pane. So, if you continue to believe your subform name is f_someformname, forgetting you renamed a clone of that form, no amount of VBA will work. :banghead:

Ask me how I know.

It's interesting that none of the cloned forms show up in the navigation pane. Why?:confused:

doco

I believe the reason why no additional cloned forms show up in the navigation pane is because there are no additional forms in the situation you describe. I believe I've simulated the situation you describe in the attached database. In this database Form1 contains f_someformname in the subform controls sf_someformname and sf2_someformname. sf_someformname and sf2_someformname are controls and therefore are not listed in the navigation pane. To see these controls you can open the properties sheet for Form1 and you will see them in the drop down.

Also to demonstrate that sf_someformname and sf2_someformname are not forms or clones of forms you can add a control to f_someformname and it will appear in both subform controls.

When you create a subform control in a form using an existing form it defaults the name of the subcontrol to the name of the existing form. In my opinion it is best to leave it that way.
 

Attachments

  • WhereAreTheSubforms.accdb
    364 KB · Views: 124

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:24
Joined
May 21, 2018
Messages
8,463
It's worthy of note, if you create and embed a subform based on an existing form, the name of that form will not show up in the navigation pane.

EG. If you have a form named f_someformname. Then embed a subform control in a form based on that form and name it sf_someformname. sf_someformname will not show up in the navigation pane. So, if you continue to believe your subform name is f_someformname, forgetting you renamed a clone of that form, no amount of VBA will work.

Ask me how I know.

It's interesting that none of the cloned forms show up in the navigation pane. Why?

Sorry that is pretty much all incorrect. You do not understand the difference between a class and an instantiation of an object. You do not understand the difference between a subform control and a source object of the control. There is only one form class that is what shows in the nav window. You can instantiate it in multiple subforms. In fact you can instantiate multiple instances of form. See demo and keep opening the form as many times as you want, but there is one form.
 

Attachments

  • HowManyForms.accdb
    720 KB · Views: 125

doco

Power User
Local time
Today, 06:24
Joined
Feb 14, 2007
Messages
482
I think you missed the point of the comment.

I understand class objects and instancing those objects (at least at a base level). However, in context of my comment, if you reference the original form

Code:
    Forms!f_mainform!f_someformname.Form.Requery

You will have an error. If reference is made to the instance (logical clone) of the physical

Code:
    Forms!f_mainform![B]s[/B]f_someformname.Form.Requery

The desired result happens.

But, thank you for explaining why the logical clone does not appear in the Navigation Pane: its logical not physical.

doco
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:24
Joined
May 21, 2018
Messages
8,463
If you have a form named f_someformname. Then embed a subform control in a form based on that form and name it sf_someformname. sf_someformname will not show up in the navigation pane.

You did not rename the subform (form) you only renamed the subform control.

When you reference a form in a subform control you do it like

Me.SubformControlName.form

That says return the instance of the form within that specific subform control. One of the reasons why is because you can easily have multiple instances of a form open in multiple subform controls. Imagine you drop two subform controls on the same form and put in theses subform controls the same form. This will answer why you cannot call a "subform" directly by name and have to go through the subform control. Which one are you referring to? Also a reason why sometimes you get the subform control with a name like Child2, because your second subform control cannot have the same name as the first subform control which has the same name as the form.
 
Last edited:

Users who are viewing this thread

Top Bottom