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).

 

[Jim/Chris]

After you have added the new item in the list you need to
requery the combobox to refresh the data

Me.ListBoxName.Requery

Jim

 

[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

 

[Ken Snell]

I'm perplexed as to why your form would not open to a new record when you're
specifying that in the OpenForm command. I've not seen that behavior before.
This, coupled with what you're seeing, is making me suspect that your
database and/or forms are corrupted.

I assume that the RefDoc form is bound to a query that is based on the
desired table?

Regarding your other post about in which order the Response setting is to be
done, the Response variable should be set after you return from the second
form and before you end the code in the first form. (Beats me how the Err
text was included in the intrinsic constant...)

--
Ken Snell
<MS ACCESS MVP>

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]

Thanks for your other answers. The RefDoc form is based directly on the
table, rather than a query. I did that because all the fields in the table
are in the form, and no others, so I didn't see the need for the additional
overhead.
Would a corrupted database and/or form continue to run for this long? It's
been months that I've been dealing with this. As I said, I've constructed a
work-around, which does the job; it's just inelegant!! <g>

Steve

I'm perplexed as to why your form would not open to a new record when you're
specifying that in the OpenForm command. I've not seen that behavior before.
This, coupled with what you're seeing, is making me suspect that your
database and/or forms are corrupted.

I assume that the RefDoc form is bound to a query that is based on the
desired table?

Regarding your other post about in which order the Response setting is to be
done, the Response variable should be set after you return from the second
form and before you end the code in the first form. (Beats me how the Err
text was included in the intrinsic constant...)

--
Ken Snell
<MS ACCESS MVP>

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]

A corrupted database can "run" for a while...usually, people don't like that
thing stop working correctly and import all objects into a new database and
continue from there.

If you'd like, zip up a small example of the database that's having this
problem and email to me (remove this is not real from my reply email
address) and I'll take a look.

--
Ken Snell
<MS ACCESS MVP>

Thanks for your other answers. The RefDoc form is based directly on the
table, rather than a query. I did that because all the fields in the table
are in the form, and no others, so I didn't see the need for the additional
overhead.
Would a corrupted database and/or form continue to run for this long? It's
been months that I've been dealing with this. As I said, I've constructed a
work-around, which does the job; it's just inelegant!! <g>

Steve

I'm perplexed as to why your form would not open to a new record when you're
specifying that in the OpenForm command. I've not seen that behavior before.
This, coupled with what you're seeing, is making me suspect that your
database and/or forms are corrupted.

I assume that the RefDoc form is bound to a query that is based on the
desired table?

Regarding your other post about in which order the Response setting is

to

be
done, the Response variable should be set after you return from the second
form and before you end the code in the first form. (Beats me how the Err
text was included in the intrinsic constant...)

--
Ken Snell
<MS ACCESS MVP>

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.

 

[Stephen Rockower]

I'll try... Since it is medical information, there are new laws about
privacy, and whether I can release information. I'll see what I can do to
sanitize it, and send it to you privately....

Thanks

Steve