Problems with unbound form used to add data

[Steve Springer]

I have a main form that contains a subform. On the
subform is a combo box set at limit to List. If the
desired value doesn't appear in the list, the user can add
the value to the look-up table via an unbound form that is
opened by a commend buttom.

The problem is that after adding the new value and closing
the unbound form, the newly added value doesn't appear in
the combo box unless I first click on a different field.
Once I return to the combo box, the value I added now
appears as a selection.

How can I get the new value to appear in the combo box
selection without having to change the focus to another
control first?

Any help would be much appreciated.
Steve Springer

 

[Dirk Goldgar]

I have a main form that contains a subform. On the
subform is a combo box set at limit to List. If the
desired value doesn't appear in the list, the user can add
the value to the look-up table via an unbound form that is
opened by a commend buttom.

The problem is that after adding the new value and closing
the unbound form, the newly added value doesn't appear in
the combo box unless I first click on a different field.
Once I return to the combo box, the value I added now
appears as a selection.

How can I get the new value to appear in the combo box
selection without having to change the focus to another
control first?

Any help would be much appreciated.
Steve Springer

Even as it is, the only reason I can think of for the newly added value
to appear when you move the focus away from the combo box and back, is
that there is code in the combo's Enter or GotFocus event to requery it.
You can approach this problem in either of two ways:

Approach 1
------------
In the Close event of the form you open to add the new value, put a line
of code that requeries the combo box on the first form:

Forms!MainForm!SubformControl.Form!ComboName.Requery

In the above "MainForm" is the name of the main form containing the
subform containing the combo box, "ComboName" is the name of the combo
box, and "SubformControl" is the name of the subform control on the main
form that is displaying the subform. Note that this may or may not be
the name of the form object displayed by the subform. If the form
"MainForm" is not open, executing the above line will raise an error
which you would have to trap and ignore.

Approach 2
------------
When you open the form to add the new value, open it in dialog mode.
That forces the code in the first form to pause until the "add" form is
closed again. Then requery the combo. If the command button is on the
subform, the code might look like this:

DoCmd.OpenForm "frmAdd", WindowMode:=acDialog
Me!ComboName.Requery

If the command button is on the main form, the code might look like
this:

DoCmd.OpenForm "frmAdd", WindowMode:=acDialog
Me!SubformControl.Form!ComboName.Requery