MS Access 2010 combo box requery on continuous form wipes out form data

Dear Experts!

So a bit myopic and just realize that when I requery a combo box on a continuous form ALL the previous combo boxes named the same gets requeried as well.

So I have two combo boxes on a continuous form. The first combo box requeries the second combos box that filters the data based on the value in the first combo box. This works great for the first record. But as you can imagine when I do this for the second record the first combo box for the second record runs the requery command when selected and requeries ALL the 'second' combo boxes on the form. Is there a way around this? I am sure somethone has come up with this problem before.

Thanks!

Rudolph
shogun5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

PatHartmanCommented:
I'm not sure if this is the problem you are experiencing but this sample database may give you the solution.
FixCascadingCombos.zip
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
Dale FyeOwner, Developing Solutions LLCCommented:
@Shogun,

The problem with cascading combo boxes in a continuous form is that whatever the RowSource is for one combo box, it is the same for everycombo box associated with a particular field.  After requerying a combo box, the values in the other combos may appear to go blank.  This is not really the case.  What is happening is that the RowSource for those other controls no longer contains the value associated with each record, and when the value of the Field is not found in the Bound column of the combo, it simply displays what appears to be NULL.

I generally use a popup form to allow users to edit data in a continuous form and simply display the values in the continuous form by linking tables in the query that is the row source.  But if you really want the cascading combos when editing or adding new records to the form, then  in the Form_AfterUpdate event, you need to go back and change the RowSource of the cascading fields to the original value.

Example:
Table contains 2 columns State and City, and you have values like:
STATE        City
  NY            Albany
  NJ             Atlantic City
  MD           Baltimore

After you add the state, you want the City combo to display only the cities that belong to the state selected, so in the State_AfterUpdate event, you change the RowSource for cboCity to:

SELECT City FROM tbl_StateCities WHERE State = me.cboState

When you edit one of these states (MD for example) and change that to VA, then all of the city names will disappear because they are not Cities in Virginia.  But after you select "Norfolk" for the Virginia, if you reset the RowSource of cboCity to:

SELECT City from tbl_StateCities

Then the values for all of the records will appear again.  This can be annoying at worst, or extremely disconcerting if your users don't understand what is going on.  So I prefer to leave combo boxes out of my continuous and datasheet forms and simply provide a way for the user to edit or add a new record as a popup form.  Then when the users closes the popup, I simply requery (or refresh if no record is added) the continuous form.
0
mbizupCommented:
I'm unable to download Pat's database at the moment, so this may duplicate her post.

A solution that has worked for me for this problem is to use a combination of a textbox and a combobox, both bound to the field in question.  By carefully overlaying the two controls, you can use the textbox to display the data, while using the drop down arrow in the combo box for selecting the data.  With this approach, even if the cascaded combo box does not display the data, the textbox will.

For cases where the combo is saving a numeric ID rather than text, include the text in the form's recordsource by joining the lookup table in the underlying query so that you can use the text field as the control source for the textbox.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
That is what my solution does.
0
shogun5Author Commented:
Thanks everyone! Very clever idea!
0
shogun5Author Commented:
Thanks all! This will do it.
0
PatHartmanCommented:
Participation points like Socialism sound good on paper.  They're warm and fuzzy and "fair".   In reality they actually diminish the worth of our answers and muddy the waters for other viewers since they have no clue what answer actually solved your problem.  In the future, if my answer didn't solve your problem I would prefer to not receive participation points.  I also wouldn't object if you reopened this thread and removed my participation points if you didn't use my example.
0
shogun5Author Commented:
Thanks Pat...but on the contrary your comments helped me the most!
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.