Not In List Event and Requery Errors

[unclemuffin]

I am using Access 2007. I have a form named frmRoll with control
RMItemNumber with the Limit to List property set to "Yes". This
control is bound to a field in the table tblWIPTable. I have event
code for the NotInList event for the RMItemNumber control.

When the user enters a new value that is not in the tblWIP, the code
properly opens the form frmWIPTable and populates the RMItemNumber
field with the NewData value that the user has entered. Once the form
is updated with the new information, I close the frmWIPTable. At this
point I get an Access window that says "The text you entered isn't an
item in the list".

The code in frmRoll:

Private Sub RMItemNumber_NotInList(NewData As String, Response As
Integer)

If NewData = "" Then Exit Sub

If MsgBox("'" & NewData & "' no es en la lista!" & Chr(13) &
Chr(10) & "¿Quiere añadir a la lista?", _ vbExclamation + vbYesNo) =
vbYes Then

DoCmd.OpenForm "frmWIPTable", , , , acFormEdit, acDialog,
NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

the code in frmWIPTable:

Private Sub Form_Load()
Dim NewData As String
Me![cmdWIPStyle] = Me.OpenArgs
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunCommand acCmdRecordsGoToNew
End Sub

I have tried adding Me![RMItemNumber].Requery after the Response =
acDataErrContinue line but that gives me a '2118 error' - you must
save the current field before you run the requery action.

In order to combat the above error I added Me.Refresh before the Me!
[RMItemNumber].Requery line but this simply causes the NotInList event
to fire again which puts me in an endless loop.

TIA,

Brent

 

[Jeanette Cunningham]

Hi,
here is some code I use in an app.

Private Sub cboSubcategory_NotInList(NewData As String, _
Response As Integer)
Dim strMsg As String


' Prompt user to verify they wish to add new value.
strMsg = "Value is not in list. Add it?"
If MsgBox(strMsg, vbOKCancel) = vbOK Then
DoCmd.OpenForm "fdlgSubcategoryAdd", , , , acAdd, acDialog,
NewData

' Code will wait until "add" form closes - now verify that it
got added!
'debug.Print NewData
If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then
' Ooops
MsgBox "You failed to add a Subcategory that matched what
you entered. Please try again.", vbInformation
' Tell Access we handled the error, but cancel the update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error message
Response = acDataErrDisplay
End If

End Sub

Jeanette Cunningham

I am using Access 2007. I have a form named frmRoll with control
RMItemNumber with the Limit to List property set to "Yes". This
control is bound to a field in the table tblWIPTable. I have event
code for the NotInList event for the RMItemNumber control.

When the user enters a new value that is not in the tblWIP, the code
properly opens the form frmWIPTable and populates the RMItemNumber
field with the NewData value that the user has entered. Once the form
is updated with the new information, I close the frmWIPTable. At this
point I get an Access window that says "The text you entered isn't an
item in the list".

The code in frmRoll:

Private Sub RMItemNumber_NotInList(NewData As String, Response As
Integer)

If NewData = "" Then Exit Sub

If MsgBox("'" & NewData & "' no es en la lista!" & Chr(13) &
Chr(10) & "¿Quiere añadir a la lista?", _ vbExclamation + vbYesNo) =
vbYes Then

DoCmd.OpenForm "frmWIPTable", , , , acFormEdit, acDialog,
NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

the code in frmWIPTable:

Private Sub Form_Load()
Dim NewData As String
Me![cmdWIPStyle] = Me.OpenArgs
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunCommand acCmdRecordsGoToNew
End Sub

I have tried adding Me![RMItemNumber].Requery after the Response =
acDataErrContinue line but that gives me a '2118 error' - you must
save the current field before you run the requery action.

In order to combat the above error I added Me.Refresh before the Me!
[RMItemNumber].Requery line but this simply causes the NotInList event
to fire again which puts me in an endless loop.

TIA,

Brent

 

[unclemuffin]

Jeanette, thanks for the help. That has me most of the way there I am
still running into a problem.

On the line If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then

The lookup always returns a null value even though I have checked the
table and the new value has actually been added to the table. Is it
possible that some reason the lookup is being performed on a copy of
the table that is in memory and has not been updated with the new
value?

I even tried Microsoft's suggestion here: http://support.microsoft.com/kb/197526/en-us
and am still having the same problem.

Thanks,

Brent

Hi,
here is some code I use in an app.

Private Sub cboSubcategory_NotInList(NewData As String, _
Response As Integer)
Dim strMsg As String

' Prompt user to verify they wish to add new value.
strMsg = "Value is not in list. Add it?"
If MsgBox(strMsg, vbOKCancel) = vbOK Then
DoCmd.OpenForm "fdlgSubcategoryAdd", , , , acAdd, acDialog,
NewData

' Code will wait until "add" form closes - now verify that it
got added!
'debug.Print NewData
If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then
' Ooops
MsgBox "You failed to add a Subcategory that matched what
you entered. Please try again.", vbInformation
' Tell Access we handled the error, but cancel the update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error message
Response = acDataErrDisplay
End If

End Sub

Jeanette Cunningham


I am using Access 2007. I have a form named frmRoll with control
RMItemNumber with the Limit to List property set to "Yes". This
control is bound to a field in the table tblWIPTable. I have event
code for the NotInList event for the RMItemNumber control.

When the user enters a new value that is not in the tblWIP, the code
properly opens the form frmWIPTable and populates the RMItemNumber
field with the NewData value that the user has entered. Once the form
is updated with the new information, I close the frmWIPTable. At this
point I get an Access window that says "The text you entered isn't an
item in the list".

The code in frmRoll:

Private Sub RMItemNumber_NotInList(NewData As String, Response As
Integer)

If NewData = "" Then Exit Sub

If MsgBox("'" & NewData & "' no es en la lista!" & Chr(13) &
Chr(10) & "¿Quiere añadir a la lista?", _ vbExclamation + vbYesNo) =
vbYes Then

DoCmd.OpenForm "frmWIPTable", , , , acFormEdit, acDialog,
NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

the code in frmWIPTable:

Private Sub Form_Load()
Dim NewData As String
Me![cmdWIPStyle] = Me.OpenArgs
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunCommand acCmdRecordsGoToNew
End Sub

I have tried adding Me![RMItemNumber].Requery after the Response =
acDataErrContinue line but that gives me a '2118 error' - you must
save the current field before you run the requery action.

In order to combat the above error I added Me.Refresh before the Me!
[RMItemNumber].Requery line but this simply causes the NotInList event
to fire again which puts me in an endless loop.

TIA,

Brent

 

[Jeanette Cunningham]

I assume that you replaced SubcategoryID, tblSubcategories and
[Subcategory] with the names of the fields and controls for your form. If
not, try that.

Jeanette Cunningham

Jeanette, thanks for the help. That has me most of the way there I am
still running into a problem.

On the line If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then

The lookup always returns a null value even though I have checked the
table and the new value has actually been added to the table. Is it
possible that some reason the lookup is being performed on a copy of
the table that is in memory and has not been updated with the new
value?

I even tried Microsoft's suggestion here:
http://support.microsoft.com/kb/197526/en-us
and am still having the same problem.

Thanks,

Brent

Hi,
here is some code I use in an app.

Private Sub cboSubcategory_NotInList(NewData As String, _
Response As Integer)
Dim strMsg As String

' Prompt user to verify they wish to add new value.
strMsg = "Value is not in list. Add it?"
If MsgBox(strMsg, vbOKCancel) = vbOK Then
DoCmd.OpenForm "fdlgSubcategoryAdd", , , , acAdd, acDialog,
NewData

' Code will wait until "add" form closes - now verify that it
got added!
'debug.Print NewData
If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then
' Ooops
MsgBox "You failed to add a Subcategory that matched what
you entered. Please try again.", vbInformation
' Tell Access we handled the error, but cancel the update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error message
Response = acDataErrDisplay
End If

End Sub

Jeanette Cunningham


I am using Access 2007. I have a form named frmRoll with control
RMItemNumber with the Limit to List property set to "Yes". This
control is bound to a field in the table tblWIPTable. I have event
code for the NotInList event for the RMItemNumber control.

When the user enters a new value that is not in the tblWIP, the code
properly opens the form frmWIPTable and populates the RMItemNumber
field with the NewData value that the user has entered. Once the form
is updated with the new information, I close the frmWIPTable. At this
point I get an Access window that says "The text you entered isn't an
item in the list".

The code in frmRoll:

Private Sub RMItemNumber_NotInList(NewData As String, Response As
Integer)

If NewData = "" Then Exit Sub

If MsgBox("'" & NewData & "' no es en la lista!" & Chr(13) &
Chr(10) & "¿Quiere añadir a la lista?", _ vbExclamation + vbYesNo) =
vbYes Then

DoCmd.OpenForm "frmWIPTable", , , , acFormEdit, acDialog,
NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

the code in frmWIPTable:

Private Sub Form_Load()
Dim NewData As String
Me![cmdWIPStyle] = Me.OpenArgs
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunCommand acCmdRecordsGoToNew
End Sub

I have tried adding Me![RMItemNumber].Requery after the Response =
acDataErrContinue line but that gives me a '2118 error' - you must
save the current field before you run the requery action.

In order to combat the above error I added Me.Refresh before the Me!
[RMItemNumber].Requery line but this simply causes the NotInList event
to fire again which puts me in an endless loop.

TIA,

Brent

 

[unclemuffin]

Yes, I modified the code you posted to include the appropriate names.
Here is my code:
BiasSize is the Key of BiasSizeTable
cmdBiasSize is the control on frmCut

Private Sub cmdBiasSize_NotInList(NewData As String, Response As
Integer)

Dim strMsg As String

' Prompt user to verify they wish to add new value.
strMsg = UCase$(NewData) & "No es in la lista. ¿Quieres añadir a
la lista?"
If MsgBox(strMsg, vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmBiasSize", , , , acAdd, acDialog,
NewData

' Code will wait until "add" form closes - now verify that
it got added!
'debug.Print NewData
If IsNull(DLookup("BiasSize", "BiasSizeTable",
"[cmdBiassize] = """ & NewData & """")) Then
' Ooops
MsgBox "You failed to add a Subcategory that matched
what you entered. Please try again.", vbInformation
' Tell Access we handled the error, but cancel the
update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error
message
Response = acDataErrDisplay
End If
End Sub

Thank you,

Brent

I assume that you replaced SubcategoryID, tblSubcategories and
[Subcategory] with the names of the fields and controls for your form. If
not, try that.

Jeanette Cunningham


Jeanette, thanks for the help. That has me most of the way there I am
still running into a problem.

On the line If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then

The lookup always returns a null value even though I have checked the
table and the new value has actually been added to the table. Is it
possible that some reason the lookup is being performed on a copy of
the table that is in memory and has not been updated with the new
value?

I even tried Microsoft's suggestion here:http://support.microsoft.com/kb/197526/en-us
and am still having the same problem.

Thanks,

Brent

Hi,
here is some code I use in an app.

Private Sub cboSubcategory_NotInList(NewData As String, _
Response As Integer)
Dim strMsg As String

' Prompt user to verify they wish to add new value.
strMsg = "Value is not in list. Add it?"
If MsgBox(strMsg, vbOKCancel) = vbOK Then
DoCmd.OpenForm "fdlgSubcategoryAdd", , , , acAdd, acDialog,
NewData

' Code will wait until "add" form closes - now verify that it
got added!
'debug.Print NewData
If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then
' Ooops
MsgBox "You failed to add a Subcategory that matched what
you entered. Please try again.", vbInformation
' Tell Access we handled the error, but cancel the update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error message
Response = acDataErrDisplay
End If

End Sub

Jeanette Cunningham

"unclemuffin" <[email protected]> wrote in message

I am using Access 2007. I have a form named frmRoll with control
RMItemNumber with the Limit to List property set to "Yes". This
control is bound to a field in the table tblWIPTable. I have event
code for the NotInList event for the RMItemNumber control.

When the user enters a new value that is not in the tblWIP, the code
properly opens the form frmWIPTable and populates the RMItemNumber
field with the NewData value that the user has entered. Once the form
is updated with the new information, I close the frmWIPTable. At this
point I get an Access window that says "The text you entered isn't an
item in the list".

The code in frmRoll:

Private Sub RMItemNumber_NotInList(NewData As String, Response As
Integer)

If NewData = "" Then Exit Sub

If MsgBox("'" & NewData & "' no es en la lista!" & Chr(13) &
Chr(10) & "¿Quiere añadir a la lista?", _ vbExclamation + vbYesNo) =
vbYes Then

DoCmd.OpenForm "frmWIPTable", , , , acFormEdit, acDialog,
NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

the code in frmWIPTable:

Private Sub Form_Load()
Dim NewData As String
Me![cmdWIPStyle] = Me.OpenArgs
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunCommand acCmdRecordsGoToNew
End Sub

I have tried adding Me![RMItemNumber].Requery after the Response =
acDataErrContinue line but that gives me a '2118 error' - you must
save the current field before you run the requery action.

In order to combat the above error I added Me.Refresh before the Me!
[RMItemNumber].Requery line but this simply causes the NotInList event
to fire again which puts me in an endless loop.

TIA,

Brent

 

[Jeanette Cunningham]

I looked through your code and found this:

If IsNull(DLookup("BiasSize", "BiasSizeTable",
"[cmdBiassize] = """ & NewData & """")) Then

It looks like BiasSize is a number field,
try changing the code above to:

If IsNull(DLookup("BiasSize", "BiasSizeTable",
"[cmdBiassize] = " & NewData)) Then

Jeanette Cunningham


Yes, I modified the code you posted to include the appropriate names.
Here is my code:
BiasSize is the Key of BiasSizeTable
cmdBiasSize is the control on frmCut

Private Sub cmdBiasSize_NotInList(NewData As String, Response As
Integer)

Dim strMsg As String

' Prompt user to verify they wish to add new value.
strMsg = UCase$(NewData) & "No es in la lista. ¿Quieres añadir a
la lista?"
If MsgBox(strMsg, vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmBiasSize", , , , acAdd, acDialog,
NewData

' Code will wait until "add" form closes - now verify that
it got added!
'debug.Print NewData
If IsNull(DLookup("BiasSize", "BiasSizeTable",
"[cmdBiassize] = """ & NewData & """")) Then
' Ooops
MsgBox "You failed to add a Subcategory that matched
what you entered. Please try again.", vbInformation
' Tell Access we handled the error, but cancel the
update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error
message
Response = acDataErrDisplay
End If
End Sub

Thank you,

Brent

I assume that you replaced SubcategoryID, tblSubcategories and
[Subcategory] with the names of the fields and controls for your form. If
not, try that.

Jeanette Cunningham


Jeanette, thanks for the help. That has me most of the way there I am
still running into a problem.

On the line If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then

The lookup always returns a null value even though I have checked the
table and the new value has actually been added to the table. Is it
possible that some reason the lookup is being performed on a copy of
the table that is in memory and has not been updated with the new
value?

I even tried Microsoft's suggestion
here:http://support.microsoft.com/kb/197526/en-us
and am still having the same problem.

Thanks,

Brent

Hi,
here is some code I use in an app.

Private Sub cboSubcategory_NotInList(NewData As String, _
Response As Integer)
Dim strMsg As String

' Prompt user to verify they wish to add new value.
strMsg = "Value is not in list. Add it?"
If MsgBox(strMsg, vbOKCancel) = vbOK Then
DoCmd.OpenForm "fdlgSubcategoryAdd", , , , acAdd, acDialog,
NewData

' Code will wait until "add" form closes - now verify that
it
got added!
'debug.Print NewData
If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then
' Ooops
MsgBox "You failed to add a Subcategory that matched
what
you entered. Please try again.", vbInformation
' Tell Access we handled the error, but cancel the
update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error
message
Response = acDataErrDisplay
End If

End Sub

Jeanette Cunningham

"unclemuffin" <[email protected]> wrote in message

I am using Access 2007. I have a form named frmRoll with control
RMItemNumber with the Limit to List property set to "Yes". This
control is bound to a field in the table tblWIPTable. I have event
code for the NotInList event for the RMItemNumber control.

When the user enters a new value that is not in the tblWIP, the code
properly opens the form frmWIPTable and populates the RMItemNumber
field with the NewData value that the user has entered. Once the form
is updated with the new information, I close the frmWIPTable. At this
point I get an Access window that says "The text you entered isn't an
item in the list".

The code in frmRoll:

Private Sub RMItemNumber_NotInList(NewData As String, Response As
Integer)

If NewData = "" Then Exit Sub

If MsgBox("'" & NewData & "' no es en la lista!" & Chr(13) &
Chr(10) & "¿Quiere añadir a la lista?", _ vbExclamation + vbYesNo) =
vbYes Then

DoCmd.OpenForm "frmWIPTable", , , , acFormEdit, acDialog,
NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

the code in frmWIPTable:

Private Sub Form_Load()
Dim NewData As String
Me![cmdWIPStyle] = Me.OpenArgs
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunCommand acCmdRecordsGoToNew
End Sub

I have tried adding Me![RMItemNumber].Requery after the Response =
acDataErrContinue line but that gives me a '2118 error' - you must
save the current field before you run the requery action.

In order to combat the above error I added Me.Refresh before the Me!
[RMItemNumber].Requery line but this simply causes the NotInList event
to fire again which puts me in an endless loop.

TIA,

Brent