"Not in List" event won't work 1

[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 = MSg(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

 

[dhookom]

I think you entered the code wrong. You should use MsgBox

i = [b][red]MsgBox[/red][/b](MSg, vbQuestion + vbYesNo, "Unknown Charge Code...")

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Replace this:
strsql = "Insert into tblChargeCode ([strChargeCode])" & CurrentDb.excute strSQL, dbFail
with this:

strsql = "Insert into tblChargeCode ([strChargeCode])" & _
  " VALUES ('" & NewData & "')"
CurrentDb.excute strSQL, dbFail

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vanlanjl]

you are correct that was a typo
thanks

but still not working

 

[vanlanjl]

PHV

Made changes. Assumed that it was a two line change. No changes.

Receive Compile Error - User-defined type not found

Highlighting in yellow line 1
and in blue line 3

 

[vanlanjl]

Ha had Integer spelled wrong
DUH

 

[vanlanjl]

Okay aftera couple stupid spelling errors on my part I have gotten it open the MsgBox but upon clicking okay to add to tbl i receive the following:

Run-time error '3127':
The INSERT INTO statement contains the following unknown field name: 'strChargeCode'. make sure you have typed the name correctly and try the operation again.

highlighting:

CurrentDB.Execute strSQL, dbFail

 

[vanlanjl]

Now my table is structured as follows

name: tblChargeCode
Feilds: DataType:
ChargeCode (PK) Number

 

[vanlanjl]

changed

strsql = "Insert into tblChargeCode ([strChargeCode])" &

to

strsql = "Insert into tblChargeCode ([ChargeCode])" &

Works perfect! Thanks to everyone that contributed!