?
Solved

MS Access: Listbox Rowsource Causes Sub Forms to Requery

Posted on 2009-07-16
9
Medium Priority
?
700 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

741 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