acDataErrAdded causes combo box to requiry but I still get error message?

  • Thread starter Thread starter Jim Shaw
  • Start date Start date
J

Jim Shaw

BlankIn my combo box's "NotInList" event code, I open a maintenance form (in
acDialog mode) to add the missing record to the underlying table. The
maintenance form does perform an explicit save of the added record via a
command button control. Upon return from the form, I set the event's
"Response" argument to acDataErrorAdded which should cause the combo box to
requiry. However, I get a VBA generated message that my item is not in the
list. When I close that message, the combo box drops down to show the added
table entry. If the requiry is working, which I believe it is from the
contents of the dropdown list, why am I getting this error message and how
can I not get it?

In case this is important, the combo box list is populated with a "SELECT
FacilityCode, FacilityName from tblFacility" statement. The first column is
hidden and the bound column is column 0. The value I'm entering into the
combo box is a new FacilityCode.

Thanks
Jim
 
Hi Jim.

The NotInList event doesn't work properly if the bound column is zero-width.
It actually looks for the NewData value in the primary key field, i.e. in
your case it is looking for the new FacilityName in the FacilityCode column.
Naturally enough, it can't find it there, and hence the error.

What we do is to use the DblClick event of the combo to open the form where
the user can type in the new lookup value. Then in the AfterUpdate event of
the new form, you let the original combo know about the new entry:
Forms!MyOtherForm!MyCombo.Requery

There's actually a bit more to it, including testing if the other form is
open - CurrentProject.AllForms("MyOtherForm").IsLoaded - and doing it in the
AfterDelConfirm event of the lookup form as well.
 
Hi Allen;
Thanks for the explanation. Didn't know about it not working when the bound
column is hidden.
I've studied your "dbl click" event approach. I think, in this case, it
begs the question of whether or not the users knows in advance that an entry
is required in the underlying table.

I think I'm gonna go with a visible text box overlaying an invisible combo
box. Clicking on the visible text box makes it invisible and makes the
combo box visible. The combo box will have the bound column visible. That
way it will work as I expected. The combo box will then place the
entered/selected data in the text box and return visibility and focus back
to the text box.

Thanks again. I never would have figured out this issue by myself.

Jim
 
There seems to be some misunderstandings here, Jim. If
you've made the FacilityCode's ColumnWidth 0, then you must
enter the FacilityName value in the combo's text portion.
That's one of the great features of combo boxes. If you
want to enter the FacilityCode, then don't make that column
invisible.

Another note, the NotInList procedure will work if you code
it properly. The NewData value will be the string the user
entered and must be assigned to the corresponding field in
the table. In your case (where the FacilityCode is
invisible) the user enters a new FacilityName. Then the
code in your dialog form adds the name to the table along
with whatever FacilityCode the form assigns to it. It is
critical that the FacilityName entered in the table be
exactly the value in NewData, otherwise the acDataErrorAdded
requery will not be able to find it.

This can be rather confusing until you get a grasp on the
significance of the first visible column.
--
Marsh
MVP [MS Access]



Jim said:
Thanks for the explanation. Didn't know about it not working when the bound
column is hidden.
I've studied your "dbl click" event approach. I think, in this case, it
begs the question of whether or not the users knows in advance that an entry
is required in the underlying table.

I think I'm gonna go with a visible text box overlaying an invisible combo
box. Clicking on the visible text box makes it invisible and makes the
combo box visible. The combo box will have the bound column visible. That
way it will work as I expected. The combo box will then place the
entered/selected data in the text box and return visibility and focus back
to the text box.


The NotInList event doesn't work properly if the bound column is zero-width.
It actually looks for the NewData value in the primary key field, i.e. in
your case it is looking for the new FacilityName in the FacilityCode column. [snip]


Jim Shaw said:
BlankIn my combo box's "NotInList" event code, I open a maintenance form
(in
acDialog mode) to add the missing record to the underlying table. The
maintenance form does perform an explicit save of the added record via a
command button control. Upon return from the form, I set the event's
"Response" argument to acDataErrorAdded which should cause the combo box
to
requiry. However, I get a VBA generated message that my item is not in
the
list. When I close that message, the combo box drops down to show the
added
table entry. If the requiry is working, which I believe it is from the
contents of the dropdown list, why am I getting this error message and how
can I not get it?

In case this is important, the combo box list is populated with a "SELECT
FacilityCode, FacilityName from tblFacility" statement. The first column
is
hidden and the bound column is column 0. The value I'm entering into the
combo box is a new FacilityCode.
Click to expand...
Click to expand...
Click to expand...
 
Well, I tried it your way and typed in the name "shaw" in both locatiions
exactly as shown and got an Error 2001 message..."You Canceled the Previous
Operation" on the statement:
If IsNull(DLookup(NewData, "tblFacility")) Then
At least, that is where the VB Debug button is pointing...
Why? The new name was successfully added to the table! No reason for
DLookup to fail??...At that point NewData had the correct value..

I don't understand this...I'm copying the code all most directly out of
Microsoft's Running Microsoft Access 2000 book, page 847, by John Viescas.
Only difference is my data string is a straight name string, not parsed like
in their Author Name example. I am running AC2002 though.

Thanks for your help
Jim

The FacilityName was the same in both the combo box and in the tblFacility
table.
Marshall Barton said:
There seems to be some misunderstandings here, Jim. If
you've made the FacilityCode's ColumnWidth 0, then you must
enter the FacilityName value in the combo's text portion.
That's one of the great features of combo boxes. If you
want to enter the FacilityCode, then don't make that column
invisible.

Another note, the NotInList procedure will work if you code
it properly. The NewData value will be the string the user
entered and must be assigned to the corresponding field in
the table. In your case (where the FacilityCode is
invisible) the user enters a new FacilityName. Then the
code in your dialog form adds the name to the table along
with whatever FacilityCode the form assigns to it. It is
critical that the FacilityName entered in the table be
exactly the value in NewData, otherwise the acDataErrorAdded
requery will not be able to find it.

This can be rather confusing until you get a grasp on the
significance of the first visible column.
--
Marsh
MVP [MS Access]



Jim said:
Thanks for the explanation. Didn't know about it not working when the bound
column is hidden.
I've studied your "dbl click" event approach. I think, in this case, it
begs the question of whether or not the users knows in advance that an entry
is required in the underlying table.

I think I'm gonna go with a visible text box overlaying an invisible combo
box. Clicking on the visible text box makes it invisible and makes the
combo box visible. The combo box will have the bound column visible. That
way it will work as I expected. The combo box will then place the
entered/selected data in the text box and return visibility and focus back
to the text box.


The NotInList event doesn't work properly if the bound column is zero-width.
It actually looks for the NewData value in the primary key field, i.e. in
your case it is looking for the new FacilityName in the FacilityCode column. [snip]


BlankIn my combo box's "NotInList" event code, I open a maintenance form
(in
acDialog mode) to add the missing record to the underlying table. The
maintenance form does perform an explicit save of the added record via a
command button control. Upon return from the form, I set the event's
"Response" argument to acDataErrorAdded which should cause the combo box
to
requiry. However, I get a VBA generated message that my item is not in
the
list. When I close that message, the combo box drops down to show the
added
table entry. If the requiry is working, which I believe it is from the
contents of the dropdown list, why am I getting this error message
Click to expand...
and
how
can I not get it?

In case this is important, the combo box list is populated with a "SELECT
FacilityCode, FacilityName from tblFacility" statement. The first column
is
hidden and the bound column is column 0. The value I'm entering into the
combo box is a new FacilityCode.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I just don't see how a DLookup fits into this issue,
nor do I see why it would cancel some unexplained operation.

If you want to pursue this technique instead of using
Allen's approach, you'll have to post the code involved so I
can see what's going on in more detail.
--
Marsh
MVP [MS Access]



Jim said:
Well, I tried it your way and typed in the name "shaw" in both locatiions
exactly as shown and got an Error 2001 message..."You Canceled the Previous
Operation" on the statement:
If IsNull(DLookup(NewData, "tblFacility")) Then
At least, that is where the VB Debug button is pointing...
Why? The new name was successfully added to the table! No reason for
DLookup to fail??...At that point NewData had the correct value..

I don't understand this...I'm copying the code all most directly out of
Microsoft's Running Microsoft Access 2000 book, page 847, by John Viescas.
Only difference is my data string is a straight name string, not parsed like
in their Author Name example. I am running AC2002 though.

The FacilityName was the same in both the combo box and in the tblFacility
table.


There seems to be some misunderstandings here, Jim. If
you've made the FacilityCode's ColumnWidth 0, then you must
enter the FacilityName value in the combo's text portion.
That's one of the great features of combo boxes. If you
want to enter the FacilityCode, then don't make that column
invisible.

Another note, the NotInList procedure will work if you code
it properly. The NewData value will be the string the user
entered and must be assigned to the corresponding field in
the table. In your case (where the FacilityCode is
invisible) the user enters a new FacilityName. Then the
code in your dialog form adds the name to the table along
with whatever FacilityCode the form assigns to it. It is
critical that the FacilityName entered in the table be
exactly the value in NewData, otherwise the acDataErrorAdded
requery will not be able to find it.

This can be rather confusing until you get a grasp on the
significance of the first visible column.


Jim said:
Thanks for the explanation. Didn't know about it not working when the bound
column is hidden.
I've studied your "dbl click" event approach. I think, in this case, it
begs the question of whether or not the users knows in advance that an entry
is required in the underlying table.

I think I'm gonna go with a visible text box overlaying an invisible combo
box. Clicking on the visible text box makes it invisible and makes the
combo box visible. The combo box will have the bound column visible. That
way it will work as I expected. The combo box will then place the
entered/selected data in the text box and return visibility and focus back
to the text box.


The NotInList event doesn't work properly if the bound column is
zero-width.
It actually looks for the NewData value in the primary key field, i.e. in
your case it is looking for the new FacilityName in the FacilityCode
column. [snip]


BlankIn my combo box's "NotInList" event code, I open a maintenance form
(in
acDialog mode) to add the missing record to the underlying table. The
maintenance form does perform an explicit save of the added record via a
command button control. Upon return from the form, I set the event's
"Response" argument to acDataErrorAdded which should cause the combo box
to
requiry. However, I get a VBA generated message that my item is not in
the
list. When I close that message, the combo box drops down to show the
added
table entry. If the requiry is working, which I believe it is from the
contents of the dropdown list, why am I getting this error message and
how
can I not get it?

In case this is important, the combo box list is populated with a
"SELECT
FacilityCode, FacilityName from tblFacility" statement. The first
column
is
hidden and the bound column is column 0. The value I'm entering into
the
combo box is a new FacilityCode.
Click to expand...
Click to expand...
Click to expand...
 
Marshall;
Here is the code.
---------------------------------------begin code
here------------------------------------
Private Sub cboPriorFacName_NotInList(NewData As String, Response As
Integer)
Dim intReturn As Integer
intReturn = MsgBox("Facility Code " & NewData & " Is not in Facility
Table" & vbCrLf & _
"Do you want to add this facility now?", _
vbQuestion + vbYesNo)
If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frmFacilityMaint", _
datamode:=acAdd, _
WindowMode:=acDialog
If IsNull(DLookup(NewData, "tblFacility")) Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Exit Sub
End If
Response = acDataErrDisplay

End Sub
----------------------------------------end code
here ------------------------------------------
The frmFacilityMaint does not look at OpenArgs. It just does its thing to
add a record to the table.
There are only two fields in the table. FacilityCode is the Primary key.
Thanks
Jim

Marshall Barton said:
Sorry, I just don't see how a DLookup fits into this issue,
nor do I see why it would cancel some unexplained operation.

If you want to pursue this technique instead of using
Allen's approach, you'll have to post the code involved so I
can see what's going on in more detail.
--
Marsh
MVP [MS Access]



Jim said:
Well, I tried it your way and typed in the name "shaw" in both locatiions
exactly as shown and got an Error 2001 message..."You Canceled the Previous
Operation" on the statement:
If IsNull(DLookup(NewData, "tblFacility")) Then
At least, that is where the VB Debug button is pointing...
Why? The new name was successfully added to the table! No reason for
DLookup to fail??...At that point NewData had the correct value..

I don't understand this...I'm copying the code all most directly out of
Microsoft's Running Microsoft Access 2000 book, page 847, by John Viescas.
Only difference is my data string is a straight name string, not parsed like
in their Author Name example. I am running AC2002 though.

The FacilityName was the same in both the combo box and in the tblFacility
table.


There seems to be some misunderstandings here, Jim. If
you've made the FacilityCode's ColumnWidth 0, then you must
enter the FacilityName value in the combo's text portion.
That's one of the great features of combo boxes. If you
want to enter the FacilityCode, then don't make that column
invisible.

Another note, the NotInList procedure will work if you code
it properly. The NewData value will be the string the user
entered and must be assigned to the corresponding field in
the table. In your case (where the FacilityCode is
invisible) the user enters a new FacilityName. Then the
code in your dialog form adds the name to the table along
with whatever FacilityCode the form assigns to it. It is
critical that the FacilityName entered in the table be
exactly the value in NewData, otherwise the acDataErrorAdded
requery will not be able to find it.

This can be rather confusing until you get a grasp on the
significance of the first visible column.


Jim Shaw wrote:
Thanks for the explanation. Didn't know about it not working when the bound
column is hidden.
I've studied your "dbl click" event approach. I think, in this case, it
begs the question of whether or not the users knows in advance that an entry
is required in the underlying table.

I think I'm gonna go with a visible text box overlaying an invisible combo
box. Clicking on the visible text box makes it invisible and makes the
combo box visible. The combo box will have the bound column visible. That
way it will work as I expected. The combo box will then place the
entered/selected data in the text box and return visibility and focus back
to the text box.


The NotInList event doesn't work properly if the bound column is
zero-width.
It actually looks for the NewData value in the primary key field,
Click to expand...
i.e.
in
your case it is looking for the new FacilityName in the FacilityCode
column.
[snip]


BlankIn my combo box's "NotInList" event code, I open a
Click to expand...
maintenance
form
(in
acDialog mode) to add the missing record to the underlying table. The
maintenance form does perform an explicit save of the added record via a
command button control. Upon return from the form, I set the event's
"Response" argument to acDataErrorAdded which should cause the
Click to expand...
combo
box
to
requiry. However, I get a VBA generated message that my item is
Click to expand...
not
in
the
list. When I close that message, the combo box drops down to show the
added
table entry. If the requiry is working, which I believe it is
Click to expand...
from
the
contents of the dropdown list, why am I getting this error message and
how
can I not get it?

In case this is important, the combo box list is populated with a
"SELECT
FacilityCode, FacilityName from tblFacility" statement. The first
column
is
hidden and the bound column is column 0. The value I'm entering into
the
combo box is a new FacilityCode.
Click to expand...
Click to expand...
Click to expand...
 
Jim said:
Here is the code.
-------------------begin code here-----------------
Private Sub cboPriorFacName_NotInList(NewData As String, Response As
Integer)
Dim intReturn As Integer
intReturn = MsgBox("Facility Code " & NewData & " Is not in Facility
Table" & vbCrLf & _
"Do you want to add this facility now?", _
vbQuestion + vbYesNo)
If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frmFacilityMaint", _
datamode:=acAdd, _
WindowMode:=acDialog
If IsNull(DLookup(NewData, "tblFacility")) Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Exit Sub
End If
Response = acDataErrDisplay

End Sub
---------------end code here -------------------
The frmFacilityMaint does not look at OpenArgs. It just does its thing to
add a record to the table.
There are only two fields in the table. FacilityCode is the Primary key.
Click to expand...


How the heck is the dialog form supposed to know what to add
if it doesn't use the value of NewData??

As for the DLookup, it looks like your want it to check if
Newdata was actually added to the table?? What's totally
losing me here is that the DLookup is trying to return the
value of a field named by the value of NewData. Unless your
facility table is a gross violation of normalization rules,
this is just plain nonsense. Not only that, but I strongly
suspect that checking if it was added is at best redundant,
since the combo box makes this check when it requeries the
combo's list.

The first thing I would do here is get rid of the DLookup
stuff. Then I would examine what is going on with the
facility table that makes you think you can add a record
without using the value in NewData.

If you need further help, please explain the fields in the
facility table and what the dialog form does.
 
Marshall
Thanks for sticking with me on this...
Good catch on the DLookup code. I've revised it as follows:

If IsNull(DLookup("[FacilityName]", "tblFacility", "[FacilityName] =
" & NewData)) Then

I'm still getting the same "canceled" result though. The first column in
the combo box, "FacilityCode" has a zero width, I'm entering the
FacilityName into the combo box, and at the time of the error messsage, the
specified facility is in the facility table. This code does work if I make
the first column 0.5" and change the DLookup arguments to "FacilityCode".

The purpose of the DLink code is to assure that the frmFacilityMaint actions
did, in fact, result in the required entry being added to the Facility table
before allowing the Facility Code to be used as a Foreign Key in the current
table.

The tblFacility has only two columns: FacilityCode (Prinary Key, intiger,
fixed, 0 decimals) and FacilityName (text, 50 chars). It is simply a
Reference table. Form "frmFacilityMaint" has a combo box bound to
FacilityCode ( to allow quick movement to existing records for updateing)
and a text box bound to FacilityName. Facility codes are only 3 characters
long. Names are much longer and prone to typing errors when entered. I
tried to pass the new Facility Code in the OpenArgs, but VBA will not allow
assignment to the combo box, so I have the user retype it. That's how the
form knows what to add to the data table.

The FacilityCode is used as a Foreign Key in many other database tables (to
achieve 3rd normal form).

In the form with the DLookup code, I'm trying to make life a little easier
for my users by having them enter the Facility Code and respond back with
the Facility Name. I didn't know that hiding a field negated its
availability as a data entry field.

My general design approach is to show users meaningfull data, not a lot of
primary key codes. However, if using a code, that is user assigned,
symplifyes data entry, I want them to use it. I can see, from what you've
told me that won't work. If I use the Facility Code field, the feedback
will be the code, not the name. So I'll have to use another approach to do
what I want. I can achieve it with a combo box for the code and a text box
for the name.

However, if it is true that if the FacilityCode (Column 1) is hidden and the
combo box then moves to Column 2 to do its thing, I have no explaination as
to why I'm getting the "Cancel" error.

Hope this helps you to understand my goal here.
Jim
 
Jim said:
Marshall
Thanks for sticking with me on this...
Good catch on the DLookup code. I've revised it as follows:

If IsNull(DLookup("[FacilityName]", "tblFacility", "[FacilityName] =
" & NewData)) Then

I'm still getting the same "canceled" result though. The first column in
the combo box, "FacilityCode" has a zero width, I'm entering the
FacilityName into the combo box, and at the time of the error messsage, the
specified facility is in the facility table. This code does work if I make
the first column 0.5" and change the DLookup arguments to "FacilityCode".

The purpose of the DLink code is to assure that the frmFacilityMaint actions
did, in fact, result in the required entry being added to the Facility table
before allowing the Facility Code to be used as a Foreign Key in the current
table.

The tblFacility has only two columns: FacilityCode (Prinary Key, intiger,
fixed, 0 decimals) and FacilityName (text, 50 chars). It is simply a
Reference table. Form "frmFacilityMaint" has a combo box bound to
FacilityCode ( to allow quick movement to existing records for updateing)
and a text box bound to FacilityName. Facility codes are only 3 characters
long. Names are much longer and prone to typing errors when entered. I
tried to pass the new Facility Code in the OpenArgs, but VBA will not allow
assignment to the combo box, so I have the user retype it. That's how the
form knows what to add to the data table.

The FacilityCode is used as a Foreign Key in many other database tables (to
achieve 3rd normal form).

In the form with the DLookup code, I'm trying to make life a little easier
for my users by having them enter the Facility Code and respond back with
the Facility Name. I didn't know that hiding a field negated its
availability as a data entry field.

My general design approach is to show users meaningfull data, not a lot of
primary key codes. However, if using a code, that is user assigned,
symplifyes data entry, I want them to use it. I can see, from what you've
told me that won't work. If I use the Facility Code field, the feedback
will be the code, not the name. So I'll have to use another approach to do
what I want. I can achieve it with a combo box for the code and a text box
for the name.

However, if it is true that if the FacilityCode (Column 1) is hidden and the
combo box then moves to Column 2 to do its thing, I have no explaination as
to why I'm getting the "Cancel" error.
Click to expand...


Well. yes it helps, but I still don't see how the dialog
form knows what to insert. You said something about
OpenArgs not working, but I don't inderstand what that
problem is either. Something about the user typing the
code, which, seeing as how the name is new and doesn't have
a code yet, sounds like a requirement to me.

Regardless of all that, if you really are getting a good
record added, then your current problem with the DLookup is
a sybtax error. The name value in the criteria is a string
and must be exclosed in quotes:

DLookup(. . ., "[FacilityName] =""" & NewData & """")
 
Back
Top