Solved

MS Access: Listbox Rowsource Causes Sub Forms to Requery

Posted on 2009-07-16
9
680 Views
Last Modified: 2013-11-28
I have a parent form that has an unbound value list listbox on it. The purpose of the list box is to show just to show summary information. However, whenever I update the rowsource to show new information, it causes every subform on this parent form to requery. Why is this happening and how can I update the listbox without causing every sub form to requery?

Thanks!
0
Comment
Question by:VoodooFrog
  • 4
  • 3
  • 2
9 Comments
 
LVL 12

Expert Comment

by:ErezMor
ID: 24872627
the requerying subforms are connected to the listbox in any way?
how do you re-set the listbox data? by changing the rowsource or requery the control?
0
 

Author Comment

by:VoodooFrog
ID: 24872814
No, none of the subforms are connected, related, or dependant on the listbox in any way.

I am building a string and then assigning it to the rowsource since it is based off a value list that I am building.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24872895
better if you upload your db
0
 

Author Comment

by:VoodooFrog
ID: 24873659
I am unable to upload the database or any portion of it. However, I do not believe uploading my db will help any. I created a blank db, created 2 tables, and created 2 forms from the two tables. Made one form a subform of the other. Then created a blank listbox and a command button on the parent form. On clicking of the button placed the following code.
   me.list4.rowsource = ""
In the on current event of the sub form I placed this code:
   debug.print "On Current Ran"

Every time I clicked the button, the On Current event for the subform ran. This is what is happening on my form in my db. Every subform is requerying and setting back to the first record. This an efficiency problem since I reset the listbox's rowsource on current for the parent form. Every subform ends up getting requeried 3-4 times every time the record changes. And if the user wants to update the list box, it again causes everything to requery.

I do have forms with the same lisbox and subform scenario. that do not have this problem; however, I can not find that I am doing anything different on those forms.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24873740
ok
0
 
LVL 12

Accepted Solution

by:
ErezMor earned 500 total points
ID: 24874748
if you can afford it, un-link the subforms from the main form (using the subform control's property sheet
even if you didnt link them explicitly, access tries to link them as it sees best
and for unlinked subforms that phnomena doesnt occur
you can still manually link the subforms to the main form using the main form's current event and subform's requery method or recordsource property
0
 

Author Comment

by:VoodooFrog
ID: 24878300
Removing the linking field properties seems keep the subform from requerying when it is not supposed to. However, it does not explain why this is only happening to some linked subforms and not all. Or how  to really prevent it from happening. I should be able to requery a listbox/combo box whithout worrying about every subform requerying as well.

0
 

Author Comment

by:VoodooFrog
ID: 24879318
Well, as a work around I ended up isolating the listbox on it's own subform so it will not affect anything else. This is not really an answer to the problem. I would still like to know how to avoid it from happening . Thank you for your help.
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 24883361
cheers for the points, mate
and sorry i couldnt suggest a real cure to what seems like an inherent bug in access
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

10 Experts available now in Live!

Get 1:1 Help Now