"Not in list" event for Access 2007

[vanlanjl]

I cannot get the not in list event to work right.

On my form I have a combo box named "cboChargeCode"

I have a table named "tblChargeCode"

I would like it so when you enter a value into the cbohargeCOde that is not listed it will alert you and ask you if you would like to add such item.

My code

 
Private Sub ChargeCode_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As interger
Dim MSg As String
 
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
 
MSg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
MSg = MSg & "Do you want to add it?"
 
i = MSgBox(MSg, vbQuestion + vbYesNo, "Unknown Charge Code...")
If i = vbYes Then
    strsql = "Insert into tblChargeCode ([strChargeCode])" & CurrentDb.excute strSQL, dbFailOnError
    Response = acDataErrAdded
Else
    Response = acDataErrContinue
End If
 

error occurs on line:

strsql = "Insert into tblChargeCode ([strChargeCode])" & CurrentDb.excute strSQL, dbFailOnError
 

I got this code form Microsoft so...
not sure what is going on or why it doesnt work, any help would be awesome!
Thanks

[RuralGuy]

Recheck where you got the code from (Microsoft). You'll see it is not the same.

[vanlanjl]

I figured it out.

 
Option Compare Database
 
Private Sub cboChargeCode_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim MSg As String
 
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
 
MSg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
MSg = MSg & "Do you want to add it?"
 
i = MsgBox(MSg, vbQuestion + vbYesNo, "Unknown Charge Code...")
If i = vbYes Then
    strSQL = "Insert into tblChargeCode ([ChargeCode])" & " VALUES ('" & NewData & "')"
    CurrentDb.Execute strSQL, dbFail
    Response = acDataErrAdded
Else
    Response = acDataErrContinue
End If
End Sub
 

[RuralGuy]

Excellent! Glad you got it sorted.

[92Felton]

Hey I'm Felton and here for the first time. I came across this thread and I find It truly useful & it helped me out much. I hope to give something back and aid others like you aided me.

[RuralGuy]

Welcome. Glad we could help.

[NeoPa]

Suspicion alert:
Why would this help anyone but the OP? It's not like it's a common mistake to make, and it's all about the OP's mistake.

If 92Felton goes on to post normally then no problem. If, on the other hand, they post anything suspicious, then this should not be taken to indicate their motives are pure.