Jeremiah31
Programmer
I’m using a combo box on a form where the user can enter a Sku and the item description will populate on an unbound text box. I’m trying to establish a relation in code where if the Sku doesn’t exist, the NotInList event will warn the user and then ask if they want to create the Sku. If the user selects Yes, a input box prompt’s and asks the user to enter the Sku description.
With the code listed below, everything works until the Sku Description. After I enter the description, I receive an “Item not found in this collection” error. First time I seen this error message. Can someone help me out with this? I have the table named tblSku, with only two fields txtSku, and txtDescription. I created a relationship to a main table in the relationship window as one to many.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String
Dim NewID As String
On Error GoTo Err_cboSku_NotInList
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Confirm that the user wants to add the new Sku.
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 Sku, set the Response
' argument to suppress an error message and undo changes.
Me.Undo
Response = acDataErrContinue
Else
' If the user chose to add a new Sku, open a recordset
' using the tblSku table.
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSku", dbOpenDynaset)
' Ask the user to input a Sku Description .
Msg = "Please enter a Sku Description" & vbCr
NewID = InputBox(Msg)
rs.FindFirst BuildCriteria("txtDescription", dbText, NewID)
rs.AddNew
' Assign the NewID to the Vendor field.
rs![txtDescription] = NewID
' Assign the NewData argument to the CompanyName field.
rs![cboSKU] = NewData
' Save the record.
rs.Update
' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
End If
Exit_cboSku_NotInList:
Exit Sub
Err_cboSku_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
With the code listed below, everything works until the Sku Description. After I enter the description, I receive an “Item not found in this collection” error. First time I seen this error message. Can someone help me out with this? I have the table named tblSku, with only two fields txtSku, and txtDescription. I created a relationship to a main table in the relationship window as one to many.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String
Dim NewID As String
On Error GoTo Err_cboSku_NotInList
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Confirm that the user wants to add the new Sku.
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 Sku, set the Response
' argument to suppress an error message and undo changes.
Me.Undo
Response = acDataErrContinue
Else
' If the user chose to add a new Sku, open a recordset
' using the tblSku table.
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSku", dbOpenDynaset)
' Ask the user to input a Sku Description .
Msg = "Please enter a Sku Description" & vbCr
NewID = InputBox(Msg)
rs.FindFirst BuildCriteria("txtDescription", dbText, NewID)
rs.AddNew
' Assign the NewID to the Vendor field.
rs![txtDescription] = NewID
' Assign the NewData argument to the CompanyName field.
rs![cboSKU] = NewData
' Save the record.
rs.Update
' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
End If
Exit_cboSku_NotInList:
Exit Sub
Err_cboSku_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
