NotInList for combo box not working

[mstekkie]

i have a combo box that lists occupation descriptions, and it has a bound text box that displays its corresponding number. i want the user to be able to add a new occupation.
this is the code that i'm using to try to add a new record to my occupation combo box:

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

Dim strMsg As String
Dim rst As Recordset
Dim db As Database

strMsg = "'" & NewData & "' is not in list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Service Code&quot Then
Response = acDataErrDisplay
Else
Set db = Final_Database()
Set rst = db.OpenRecordset("tblOccupation&quot
rst.AddNew
rst!occName = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If
End Sub

i don't know what i'm doing wrong. would really appreciate help on this. thank you.

 

[Mongooses]

Instead of rst!occName = NewData

try rst.Fields("occName&quot = NewData

HTH,
JC

 

[mstekkie]

i made the change that you suggested. but now i'm getting this error message that says 'user-defined type not defined' and it highlights 'dim db as Database'

any idea on how to fix it?

 

[Mongooses]

Are you using Access 2k? If so then you will need to go to the VB editor click on Tools->References and check the Microsoft DAO 3.61 Object Library. Or else you can rewrite the code for ADO.

Also if the table is in the database you are working with then Set db = CurrentDb

HTH,
JC

 

[mstekkie]

thanks jc,

i got it to somewhat work. the error i'm getting now is that it says there's a type mismatch and it highlights the line:

Set rst = db.OpenRecordset("tblOccupation&quot

did i miss something?