Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-30-2016, 07:47 PM   #1
terrytek
Newly Registered User
 
Join Date: Aug 2016
Posts: 42
Thanks: 30
Thanked 2 Times in 2 Posts
terrytek is on a distinguished road
Requery a subform when another subform's control is updated

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.

__________________
Fumbling through with Access 2007; thank you to everyone here who is helping me learn
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
terrytek is offline   Reply With Quote
Old 09-30-2016, 08:39 PM   #2
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,053
Thanks: 237
Thanked 135 Times in 123 Posts
NauticalGent will become famous soon enough
Re: Requery a subform when another subform's control is updated

We would need to see you AfterUpdate code to be able to help...
NauticalGent is offline   Reply With Quote
Old 10-01-2016, 06:30 PM   #3
terrytek
Newly Registered User
 
Join Date: Aug 2016
Posts: 42
Thanks: 30
Thanked 2 Times in 2 Posts
terrytek is on a distinguished road
Re: Requery a subform when another subform's control is updated

Quote:
Originally Posted by NauticalGent View Post
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".

__________________
Fumbling through with Access 2007; thank you to everyone here who is helping me learn
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
terrytek is offline   Reply With Quote
Old 10-01-2016, 06:49 PM   #4
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,490
Thanks: 358
Thanked 958 Times in 929 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Requery a subform when another subform's control is updated

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.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 10-01-2016, 06:57 PM   #5
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,490
Thanks: 358
Thanked 958 Times in 929 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Requery a subform when another subform's control is updated

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.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 10-01-2016, 11:49 PM   #6
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,053
Thanks: 237
Thanked 135 Times in 123 Posts
NauticalGent will become famous soon enough
Re: Requery a subform when another subform's control is updated

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.
NauticalGent is offline   Reply With Quote
Old 10-03-2016, 11:12 AM   #7
terrytek
Newly Registered User
 
Join Date: Aug 2016
Posts: 42
Thanks: 30
Thanked 2 Times in 2 Posts
terrytek is on a distinguished road
Re: Requery a subform when another subform's control is updated

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?

__________________
Fumbling through with Access 2007; thank you to everyone here who is helping me learn
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
terrytek is offline   Reply With Quote
Old 10-03-2016, 11:17 AM   #8
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,053
Thanks: 237
Thanked 135 Times in 123 Posts
NauticalGent will become famous soon enough
Re: Requery a subform when another subform's control is updated

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...
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
terrytek (10-03-2016)
Old 10-03-2016, 11:22 AM   #9
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,490
Thanks: 358
Thanked 958 Times in 929 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Requery a subform when another subform's control is updated

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.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
The Following User Says Thank You to sneuberg For This Useful Post:
terrytek (10-03-2016)
Old 10-03-2016, 12:21 PM   #10
terrytek
Newly Registered User
 
Join Date: Aug 2016
Posts: 42
Thanks: 30
Thanked 2 Times in 2 Posts
terrytek is on a distinguished road
Re: Requery a subform when another subform's control is updated

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.

__________________
Fumbling through with Access 2007; thank you to everyone here who is helping me learn
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
terrytek is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Requery a subform on a tab control wrek Forms 6 08-06-2013 02:39 AM
Requery through a control within a subform Steven811 Forms 2 09-15-2004 12:23 AM
Requery a subform on a tab control RedSkies Forms 3 03-17-2003 01:06 PM
Requery control on subform Shawny Macros 1 10-14-2002 08:32 PM
Requery control on subform protocol Forms 2 04-27-2001 02:32 PM




All times are GMT -8. The time now is 09:27 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World