requery problem

[Guest]

I have added code to a form to trap a "item not found" error on a combo box
and call a dialog box allowing the user to either cancel the edit, or to add
a record to the combo box source table (query). Code on the entry form for
the lookup table attempts (after saving the new record) to requery the
updated combo box source on the first form:

dim lkup as control
set lkup as forms!Form1!cbLkUp
lkup.requery

When run, I get the message "must save this field.." Of course, Form1 is
also open and the error causing entry is still in the combobox which has
focus on that form. What I want is for the combo box datasouce query to
update so that the keyed data (in the combo box) will not cause and error and
will pull in the new linked data fields. How do I do that without closing
and then reopening the form?

I should add that the source of the first form is a query on a transaction
table and the master 'names' table containing the source field for the combo
box. Perhaps I should requery the form source as well?

DM

 

[Marshall Barton]

I have added code to a form to trap a "item not found" error on a combo box
and call a dialog box allowing the user to either cancel the edit, or to add
a record to the combo box source table (query). Code on the entry form for
the lookup table attempts (after saving the new record) to requery the
updated combo box source on the first form:

dim lkup as control
set lkup as forms!Form1!cbLkUp
lkup.requery

When run, I get the message "must save this field.." Of course, Form1 is
also open and the error causing entry is still in the combobox which has
focus on that form. What I want is for the combo box datasouce query to
update so that the keyed data (in the combo box) will not cause and error and
will pull in the new linked data fields. How do I do that without closing
and then reopening the form?

I should add that the source of the first form is a query on a transaction
table and the master 'names' table containing the source field for the combo
box. Perhaps I should requery the form source as well?

You should not requery the combo box at all in this kind of
situation. The NotInList procedure sould set the Response
argument to acDataErrAdded to let Access know that you
added the entry. Access will then suppress its own message
and automatically requery the combo box.

 

[Guest]

Marshall,

Using the NotInList procedure is much better, but because the combo box is
bound, I still have to call the input form to get all of the required data,
as follows:

Dim ctl As Control
Set ctl = Me!CbPhoneLU
If MsgBox("Not in 'Names' list. Add now?", vbOKCancel) = vbOK Then
Response = acDataErrContinue
ctl.Undo
basNavigation.formopen ("WinnerNames")
ctl.Requery
Else
Response = acDataErrContinue
ctl.Undo
End If

Problem: When I step through the code, the ctl.requery command executes
properly upon saving the new record on the "winnernames" form. When run
without debug, the requery doesn't work, which suggests that I need to add
some sort of "wait" function to allow the requery to take effect. What do
you suggest?

Thanks,

DM

 

[Marshall Barton]

Using the NotInList procedure is much better, but because the combo box is
bound, I still have to call the input form to get all of the required data,
as follows:

Dim ctl As Control
Set ctl = Me!CbPhoneLU
If MsgBox("Not in 'Names' list. Add now?", vbOKCancel) = vbOK Then
Response = acDataErrContinue
ctl.Undo
basNavigation.formopen ("WinnerNames")
ctl.Requery
Else
Response = acDataErrContinue
ctl.Undo
End If

Problem: When I step through the code, the ctl.requery command executes
properly upon saving the new record on the "winnernames" form. When run
without debug, the requery doesn't work, which suggests that I need to add
some sort of "wait" function to allow the requery to take effect.

Get rid of the requery!

You should also modify your formopen procedure to open the
form in dialog mode.

I don't know why you would want to use a separate procedure
to open a form. I would open the form directly in the
NotInList procedure instead, generally like this:

DoCmd.OpenForm "WinnerNames", _
WindowMode:= acDialog, _
DataMode:= acFormAdd, _
OpenArgs:=NewData

Opening the form in dialog mode causes the code in your
NotInList procedure to be suspended until the form is closed
(or made invisible).

DataMode opens the form directly to a new record without
displaying any existing records.

OpenArgs is used to pass the new name to the dialog form so
the user can't make a mistake by retyping something
different than what was entered in the combo box. The form
would then include code in its Load event to copy the new
name from OpenArgs to the name text box:

If Not IsNull(Me.OpenArgs) Then
Me.txtName = Me.OpenArgs
End If

 

[Guest]

I finally got this to work by using a macro to open the form (as a dialog
box) and requery the control. The requery seems to be necessary, probably
because the combo source and the bound linking field is different; the lookup
is on phone number and the link is the winnerID. For some reason
"docmd.openform "winnernames" acwindowmode = acdialog" would not suspend the
execution while the "winnernames" form was open; perhaps something wrong with
the syntax.

Thanks for your help.

DM

 

[Marshall Barton]

I finally got this to work by using a macro to open the form (as a dialog
box) and requery the control. The requery seems to be necessary, probably
because the combo source and the bound linking field is different; the lookup
is on phone number and the link is the winnerID. For some reason
"docmd.openform "winnernames" acwindowmode = acdialog" would not suspend the
execution while the "winnernames" form was open; perhaps something wrong with
the syntax.

You have to spell the argument name correctly. It's
WindowMode, not acwindowmode.

I still don't understand the need for requerying the combo
box. Don't forget that the line:
Response = acDataErrAdded
tells Access to requery the combo box.

 

[Guest]

Marshall,

You are correct. When I substituted "response = acDataAdded" for
"acDataErrContinue" everything worked. Otherwise, I had to undo the combo
control entry before calling the dialog box, then add the entry back with
"sendkeys newdata".

Tell me about the "newdata" variable or constant that you used with the
formOpen. What scope does it have? Very usefule, but I can't find any
reference to it in the VB help for formOpen and OpenArgs.

DM

 

[Marshall Barton]

You are correct. When I substituted "response = acDataAdded" for
"acDataErrContinue" everything worked. Otherwise, I had to undo the combo
control entry before calling the dialog box, then add the entry back with
"sendkeys newdata".

Tell me about the "newdata" variable or constant that you used with the
formOpen. What scope does it have? Very usefule, but I can't find any
reference to it in the VB help for formOpen and OpenArgs.

NewData is an **argument** that Access provides to the
NotInList event procedure. It contains the text that the
user typed into the combo box.

I am suggesting that you pass the string in NewData on to
the form that's being opened so the form can fill in that
field automatically. This relieves the users of having to
retype the same thing they already entered in the combo box.
Not only does this save the use some effort and remove a
chance for them to make a mistake, it also saves you from
having to add some complicated code to the NotInList
procedure to guard against that chance of user error.