NotInList

[Stephen Rockower]

I'm trying to figure out how to process an NotInList event from a combo box
linked to a table. I want to ask the user if he wants to add the new item,
and then open the form to do that. My problem is that after I've opened the
form and come back, the NotInList event triggers again, asking the user
AGAIN to add the item. What am I doing wrong???

TIA for any tips/pointers


Steve

 

[Ken Snell]

See The ACCESS Web for an example of how to do this:
http://www.mvps.org/access/forms/frm0015.htm


Chances are that the code in the NotInList event is not setting a Response
variable to the value acDataErrAdded:
Response = acDataErrAdded

When you do this, ACCESS automatically requeries the combo box's RowSource
so that the new item is now in the dropdown list (which should stop the
repeating problem that you're now experiencing).

 

[Stephen Rockower]

I had a line, Response = acDataErrAdded , but it still didn't work.
Before that line, response was equal to 1, then it changed to 2. I'm not
sure what those numbers are supposed to mean. I'll get back with a snippet
of code. Perhaps you'll see what I'm doing inappropriately.

Thanks

 

[Stephen Rockower]

The following is my NotInList routine. The RefDocs form uses NewData in the
OnOpen for adding a new record and beginning to input the new info. Even if
the requery and refresh lines are commented out, I still get my reprompt
("Do you want to add this doctor?"). What am I doing wrong here???

TIA

Steve



Public Sub Combo26_NotInList(NewData As String, Response As Integer)
'4.30.03
'12.30.03 getting closer. still need to figure out the notinlist parameters
On Error GoTo NotInList_Err
Dim byteResponse As Byte
byteResponse = MsgBox("Do you want to add this doctor?", vbOKCancel, "COR
Dictations")
If byteResponse = vbOK Then
DoCmd.OpenForm "RefDocs", , , , acFormAdd, acDialog, "newdoc;" & NewData
Combo26.SetFocus
Response = acDataErrAdded
Else
Combo26.Value = Null
[RefDoc] = 0
End If

Me.Requery 'this <I think> reprompts the NotInList routine. Should
[Response] be something
'different?
Me.Refresh

Exit Sub

NotInList_Err:
MsgBox Err.Number & " " & Err.Description
Resume Next

End Sub

 

[Ken Snell]

You haven't posted the code that may be running in the second form
"RefDocs", so it's hard to say what may be happening there. And, I don't
know what the setup of that form is and what it's doing. You may be
triggering the NotInList event the second time via that form. Please provide
more info.

In the code you did post, you do need to add one code step for the option of
when the person does not want to add the value:

If byteResponse = vbOK Then
DoCmd.OpenForm "RefDocs", , , , acFormAdd, acDialog, "newdoc;" & NewData
Combo26.SetFocus
Response = acDataErrAdded
Else
Combo26.Value = Null
Response = acDataErrContinue
[RefDoc] = 0
End If

By putting Response = acDataErrAdded step in, you should not need the
following steps:

Me.Requery 'this <I think> reprompts the NotInList routine. Should
[Response] be something
'different?
Me.Refresh

I typically use Integer variables for receiving responses to message boxes,
not Byte variables. I haven't tested what you've posted, but I'll assume
that it's working ok for you.

 

[Stephen Rockower]

The RefDoc form just adds records for the Referring doctors that I am using
in this combo box. Nothing fancy. It opens the form, adds a blank record,
types in the "NewData" into the LastName field, and then waits for the user
to finish inputting data and then closes. As an aside, even though I use
the acFormAdd criterion, I have to use the code, DoCmd.RunCommand
acCmdRecordsGoToNew, in my Form_Open routine to get a new, blank record.

Thanks for the acDataErrContinue tip.

BTW, where did MickeySoft come up with the bright idea to put "Err" within
those constants? I don't want to think I'm creating or dealing with an
error!!!

Steve

You haven't posted the code that may be running in the second form
"RefDocs", so it's hard to say what may be happening there. And, I don't
know what the setup of that form is and what it's doing. You may be
triggering the NotInList event the second time via that form. Please provide
more info.

In the code you did post, you do need to add one code step for the option of
when the person does not want to add the value:

If byteResponse = vbOK Then
DoCmd.OpenForm "RefDocs", , , , acFormAdd, acDialog, "newdoc;" & NewData
Combo26.SetFocus
Response = acDataErrAdded
Else
Combo26.Value = Null
Response = acDataErrContinue
[RefDoc] = 0
End If

By putting Response = acDataErrAdded step in, you should not need the
following steps:

Me.Requery 'this <I think> reprompts the NotInList routine. Should
[Response] be something
'different?
Me.Refresh

I typically use Integer variables for receiving responses to message boxes,
not Byte variables. I haven't tested what you've posted, but I'll assume
that it's working ok for you.


--
Ken Snell
<MS ACCESS MVP>

The following is my NotInList routine. The RefDocs form uses NewData in the
OnOpen for adding a new record and beginning to input the new info.

Even

if
the requery and refresh lines are commented out, I still get my reprompt
("Do you want to add this doctor?"). What am I doing wrong here???

TIA

Steve



Public Sub Combo26_NotInList(NewData As String, Response As Integer)
'4.30.03
'12.30.03 getting closer. still need to figure out the notinlist parameters
On Error GoTo NotInList_Err
Dim byteResponse As Byte
byteResponse = MsgBox("Do you want to add this doctor?", vbOKCancel, "COR
Dictations")
If byteResponse = vbOK Then
DoCmd.OpenForm "RefDocs", , , , acFormAdd, acDialog, "newdoc;" & NewData
Combo26.SetFocus
Response = acDataErrAdded
Else
Combo26.Value = Null
[RefDoc] = 0
End If

Me.Requery 'this <I think> reprompts the NotInList routine. Should
[Response] be something
'different?
Me.Refresh

Exit Sub

NotInList_Err:
MsgBox Err.Number & " " & Err.Description
Resume Next

End Sub

 

[Stephen Rockower]

One other question: Does the order of where that "Response=" line matter?
ie before or after the other commands? The other commands need to be done
prior to moving to the next field, but I don't think I care which order it
happens in...

Steve