Requery combobox after table update

[Dave the Wave]

I have a few comboboxes on forms that I've programmed to open up their
underlying tables if dbl-clicked. (Allows adding new entries without
jumping through menus.)

My only problem is that the additions or edits to the underlying table is
not represented in the combobox until it is requeried. Since there are
nearly a dozen forms and as many as 3 comboboxes per form, I was hoping
there would be an ellegant solution to having the control requeried after
the underlying table's form closes.

Thanks.

 

[Guest]

You only need to requery the combo boxes if changes are made to the
recordsource of the form. It is not necessary to requery any other form's
combo boxes unless the forms are open. The place to do this would be in the
After Update event of the form:

Me.Combo1.Requery
Me.Combo2.Requery
Me.Combo3.Requery

 

[Dave the Wave]

The combobox record source is not that of the form. (Or more speficially
the sub-form.) The subform joins data from several supporting data
tables via a complicated SQL statement. The comboboxes use the primary
key values -which are stored in a main table- to determine which support
info details to show. The Row Source for each combobox is an SQL
statement chosing the desired columns from the supporting tables.

The row sources for the comboboxes are not requeries when any changes
are made to the support tables. Once the combobox is selected (by double
or single click) there seems to be no other events that are fired until
the user does something -like leave the control. I need the control to
be requeried after the support table form is closed and before the drop
down list appears again.

Perhaps I've setting something up wrong???? Any comments are greatly
appreciated.

Regards,
Dave

 

[Guest]

See comments below

The combobox record source is not that of the form. (Or more speficially
the sub-form.) The subform joins data from several supporting data
tables via a complicated SQL statement. The comboboxes use the primary
key values -which are stored in a main table- to determine which support
info details to show. The Row Source for each combobox is an SQL
statement chosing the desired columns from the supporting tables.

None of the above makes any difference. When a form is opened, the combo
row source is queried. If you add or delete records or make changes to
records that would show up in the combo's list, they will not be seen until
the combo is requeried.

The row sources for the comboboxes are not requeries when any changes
are made to the support tables. Once the combobox is selected (by double
or single click) there seems to be no other events that are fired until
the user does something -like leave the control. I need the control to
be requeried after the support table form is closed and before the drop
down list appears again.

I guess I don't understand the relationship of the support table form to the
form with the combo boxes; however, you could do the requeries in the close
event of the support table form provided the form with the combo boxes will
always be open when th support table form is closed. Unless there is
something I don't know or am missiing from your descriptions, the After
Update event of the form with the combos is the place to do the requeries.

What would make that wrong if the data modifications take place in the
support table form, but you want to see the changes in the form with the
combos, then it should be either when the support table form closes or in the
After Update event of the support table form. Again it will depend on
whether the form with the combos is always open.

 

[Dave the Wave]

See comments below



None of the above makes any difference. When a form is opened, the
combo row source is queried. If you add or delete records or make
changes to records that would show up in the combo's list, they will
not be seen until the combo is requeried.


I guess I don't understand the relationship of the support table form
to the form with the combo boxes; however, you could do the requeries
in the close event of the support table form provided the form with
the combo boxes will always be open when th support table form is
closed. Unless there is something I don't know or am missiing from
your descriptions, the After Update event of the form with the combos
is the place to do the requeries.

What would make that wrong if the data modifications take place in the
support table form, but you want to see the changes in the form with
the combos, then it should be either when the support table form
closes or in the After Update event of the support table form. Again
it will depend on whether the form with the combos is always open.

Here's what I ended up doing:

I created a global variable, of type control, in a module. I wrote a
small snippet instructing the global variable to requery.

In the supporting data forms'modules I added an OnClose event calling
the code snippet in the module.

On each subform where a combox appears I added a line of code to the
dbl-click that sets the active combobox to the global variable.


When a combobox is dbl-clicked, that combobox is stored in a global
variable. The support data form opens and then is closed by the user.
Upon closing, the form calls the code to requery whatever combobox is
referenced by the global variable. When the user gets back to the
combobox, all edits are represented by the data in the drop down list.

Let me know if you see any traps or bugs in this.

Thanks much for your comments.

 

[Guest]

Interesting approach. I had not considered that. I don't see a problem with
it. In fact, it appears it may reduce redundant code - which is always a
good thing.
The only thing I would change is that the variable should not be global. A
global variable is available anywhere in a project. Dimming it at the
beginning of the module makes it available anywhere in the module, but will
not be available outside the module. If you need it to be available outside
the module, then global would be correct.