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?
 
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
 
Dale FyeCommented:
@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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.