Solved

MS Access: Listbox Rowsource Causes Sub Forms to Requery

Posted on 2009-07-16
9
688 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24872895
better if you upload your db
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

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.

Question has a verified solution.

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

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

809 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