Solved

MS Access: Listbox Rowsource Causes Sub Forms to Requery

Posted on 2009-07-16
9
694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24872895
better if you upload your db
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

733 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