msgbox with combobox value in not in list event.

[MackMan]

Hi Guys.. I currently use this code to trigger a not in list event, ask the user if they want to add it to the respective table, and then add it.

I'd like to be able to add the text in the combo box that triggers the event to show up in the msgbox.

any ideas? highlighted in red is where I know I'm going wrong.


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

Const Message1 = "The data you have entered " & me.cbocategory.text &" is not in the current dataset."
Const Message2 = "Add now?"
Const Title = "Unknown entry in CATEGORY Field..."
Const NL = vbCrLf & vbCrLf

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then

Set db = CurrentDb
Set rs = db.OpenRecordset("tblCategories")

With rs
.AddNew ' prepare to add a new record
.Fields("Category") = NewData
.Update ' update the table
.Close ' close the recordset object
End With
Response = acDataErrAdded
Else
Me.cboCategory.Undo
Response = acDataErrContinue
End If

Exit_ErrorHandler:

Set rs = Nothing
Set db = Nothing
Exit Sub

Err_ErrorHandler:

MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Sub

 

[JANR]

You can't change a constant during runtime, so dim message1 as String.

Private Sub cboCategory_NotInList(NewData As String, Response As Integer) 
Dim Message1 As String

Message1 = "The data you have entered " & me.cbocategory.text & " is not in the current dataset."
Const Message2 = "Add now?"
Const Title = "Unknown entry in CATEGORY Field..."
Const NL = vbCrLf & vbCrLf

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then

Set db = CurrentDb
Set rs = db.OpenRecordset("tblCategories")

With rs
.AddNew ' prepare to add a new record
.Fields("Category") = NewData 
.Update ' update the table
.Close ' close the recordset object
End With
Response = acDataErrAdded 
Else
Me.cboCategory.Undo 
Response = acDataErrContinue 
End If

Exit_ErrorHandler:

Set rs = Nothing
Set db = Nothing
Exit Sub

Err_ErrorHandler:

MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Sub

JR

 

[MackMan]

Janr.. Thanks for that. I'd totally forgot to write the Dim.
Fresh pair of eyes and all that.