MS Access: Listbox Rowsource Causes Sub Forms to Requery

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!
VoodooFrogAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ErezMorCommented:
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
VoodooFrogAuthor Commented:
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
Rey Obrero (Capricorn1)Commented:
better if you upload your db
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

VoodooFrogAuthor Commented:
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
Rey Obrero (Capricorn1)Commented:
ok
0
ErezMorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VoodooFrogAuthor Commented:
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
VoodooFrogAuthor Commented:
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
ErezMorCommented:
cheers for the points, mate
and sorry i couldnt suggest a real cure to what seems like an inherent bug in access
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.