Solved

Requery a subform

Posted on 2013-12-09
15
1,415 Views
Last Modified: 2013-12-13
I have a form with 3 subforms.  There is no recordsource in the main form.  Subform  Master/Child links are linked to text boxes in the main form.  

In general, when I select a record in subform 1, subform 2 updates to show a new set of records based on my subform1 selection.  I then select certain records in subform2 via a checkbox in each record then run an append query via a Button On-click Event to add the records to the table in subform3.  Everything works fine except I cannot requery subform3 to show the new records.  Access does not like my Requery commands any way I put them.

 (if I exit the form then re-open the records are there).  

Can someone please help with a VBA or macro to update subform3.  Current focus is subform2 but I want to requery the records in subform3 using a macro or VBA.

Help is much appreciated.
Charlie
0
Comment
Question by:cwbarrett
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39707757
Here's a sample, attached.
db.mdb
0
 

Author Comment

by:cwbarrett
ID: 39707807
Thanks for replying, your help is most appreciated.  You did reveal my problem.  I was executing the code from a subform2 button.  When I used your code format and placed the button & code in the "form1" form it worked fine.

Is there any way of executing this code from the subform form2 ?   or does code to requery subform3 have to run from form1, the main form?

THANK YOU!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39707880
You can use:

Me.Parent!NameOfSubformControl3.Requery

or:

Me.Parent!NameOfSubformControl3.Form.Requery

/gustav
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39708619
Both forms are fine for Requery, but to reference other controls, you need to use the second format:
Debug.Print Me.Parent!NameOfSubformControl3.Form.ControlName
0
 

Author Comment

by:cwbarrett
ID: 39708843
Worked great, thank you.
Charlie
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39708895
You are welcome!

/gustav
0
 

Author Comment

by:cwbarrett
ID: 39708917
I get stuck on the easiest things...  In subform1 I want to run a query whenever there is a record selection change (I move from one record to the next) in the detail area.  Common sense tells me the "On Selection Change" event could trigger this but it doesn't.  Can you tell me how to run a query whenver I select a different record in the detail area?

Thanks
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39708937
It's the OnCurrent event.

/gustav
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 39708991
Or maybe you don't need to run a query. Look at the sample that I uploaded. Subform2 updates automatically when another record in subform1 is selected, and there's no code.
0
 

Author Comment

by:cwbarrett
ID: 39709131
OnCurrent executed the code but it was a never ending loop that executed the code over and over.  Had something to do with the Master/Child link so  I removed the Master/Child link in this subform1 and changed the datasource from a query to an SQL select query with criteria that was formerly part of the Master/Child link.  My  form seems to be working fine now.

Thanks again.
0
 

Author Comment

by:cwbarrett
ID: 39709149
I didn't notice the other comment.  Where is the upload?  I would like to see what you did, it may work better for me.

Thanks
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39709185
> Where is the upload?  

The very first comment. After which you said that it revealed your problem.
0
 

Author Comment

by:cwbarrett
ID: 39709197
Oh.  Thought you posted another.

Thanks
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39709288
No, it's in that one. You'll see "hidden textbox" on the main form, meaning that in real application it would be with Visible=False. It takes the value from the subform1, and then it serves as the parent for subform2.
0
 

Author Closing Comment

by:cwbarrett
ID: 39717743
Thank you!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now