item not found in this collection

[gymphil]

I have the following code behind a combo box "on not in list" event. It runs
Ok until I add the new publication number and then I get the message "item
not found in this collection". Can anyone assist me please?

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

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_Combo423_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new customer.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a customer, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new customer, open a recordset
' using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Publications", dbOpenDynaset)

' Ask the user to input a new Customer ID.
Msg = "Please enter a unique 5-character" & vbCr & "Publication
Number."
NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("[Publication Number]", dbText, NewID)
' If the NewID already exists, ask for another new unique
' CustomerID
Do Until Rs.NoMatch
NewID = InputBox("[Publication Number]" & NewID & " already
exists." & _
vbCr & vbCr & Msg, NewID & " Already Exists")
Rs.FindFirst BuildCriteria("[Publication Number]", dbText, NewID)
Loop
' Create a new record.
Rs.AddNew
' Assign the NewID to the CustomerID field.
Rs![Publication Number] = NewID
' Assign the NewData argument to the CompanyName field.
Rs![CompanyName] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_Combo423_NotInList:
Exit Sub
Err_Combo423_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue

End Sub

 

[Dirk Goldgar]

I have the following code behind a combo box "on not in list" event. It
runs
Ok until I add the new publication number and then I get the message "item
not found in this collection". Can anyone assist me please?

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

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_Combo423_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new customer.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a customer, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new customer, open a recordset
' using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Publications", dbOpenDynaset)

' Ask the user to input a new Customer ID.
Msg = "Please enter a unique 5-character" & vbCr & "Publication
Number."
NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("[Publication Number]", dbText, NewID)
' If the NewID already exists, ask for another new unique
' CustomerID
Do Until Rs.NoMatch
NewID = InputBox("[Publication Number]" & NewID & " already
exists." & _
vbCr & vbCr & Msg, NewID & " Already Exists")
Rs.FindFirst BuildCriteria("[Publication Number]", dbText,
NewID)
Loop
' Create a new record.
Rs.AddNew
' Assign the NewID to the CustomerID field.
Rs![Publication Number] = NewID
' Assign the NewData argument to the CompanyName field.
Rs![CompanyName] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_Combo423_NotInList:
Exit Sub
Err_Combo423_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue

End Sub

Check to make sure that the name of the field you're calling "CompanyName"
is really that, and not (for example) "Company Name". That seems like your
most likely candidate for the error's cause.

Failing that, set a breakpoint in the routine, then trigger the routine by
entering a new publication number, step through it line by line, and see
which line of code raises the error and sends control to the error-handler.