Prevent Requery from Blanking Comboboxes

[SemperSalvus]

I am using Access 2003.

I have 4 comboboxes that are dependent in order, similar to how a couple of the Forms FAQs discuss. So, in the afterupdate event on combobox 1, the rowsource for combobox 2 is set and combobox 2 is requried, then so on for the other 2 comboboxes.

All of that works just fine, however, when I move to another pre-existing record and choose a value for combobox 1, then 2, etc. the saved values in those comboboxes for other records will not display (due to the requery). I tested taking out the requery and of course it is needed to make it all work. This form doubles as a viewing and editing form, where new records are added in a totally separate version of the form.

Note, the data selected in each combobox is still saved within the table whenever this occurs, so there is no data loss. This is just a matter of the info not being displayed properly.

I have a workaround in place but would rather avoid it. I was hoping there was a better way to do it. I can perform data entry on all 4 comboboxes via popup (or even subform or just in a separate space on the form) and then display the table values in separate text boxes.

I did not see anything in the FAQ's or by search on this problem. So, is there a better way or do I live with the popup for data entry on just those 4 comboboxes?

Thanks in advance.

 

[ProgramError]

Are they bound to anything?


Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[TheAceMan1]

How are ya SemperSalvus . . .

What you describe is expected behavior of a bound combobox with changing rowsource in a continuous form.

Be aware: The rowsource of the combo is the same for all combo's in the field! Change the rowsource to exclude previous selections and as you've seen . . . they disappear!. Exclusions that are on screen (in view), will remain until their scrolled out of view. When scrolling back . . . their gone!

You need to make the combo's unbound and place them in the forms header or footer. The combo's now update bound textboxes that take their place.

Your Thoughts? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[SemperSalvus]

ProgramError: Yes they are bound.

TheAceMan1: Thank you for confirming what I thought, that it was just typical behavior. I will be doing as you said, making the comboboxes unbound, to feed and display the choices in bound text boxes. I will probably put a button on the form to open a popup that will contain the comboboxes, because my form is totally packed and I do not want to try to implement the use of tabbed pages at this point. I am the only user of this form, so it does not have to be pretty.

Thanks so much for the help.