NotInList function gives error on: Dim db As Database

[HyBry]

I have created a form with a combo box that looks up values in tblFrequency.
If the value is new I want to add it to the table and I have tried couple scripts I found online but it always comes out with an error. This is script that i would like as I seem to understand it more or less:

Private Sub Update_frequency_NotInList(NewData As String, Response As Integer)
    Dim db As Database
    Set db = CurrentDb

    'Add the new value to the field
    db.Execute "INSERT INTO tblFrequency (Update_Frequency) VALUES (""" & NewData & """)", dbFailOnError

    'Tell Access you've added the value
    Response = acDataErrAdded

    db.Close
    Set db = Nothing
End Sub

However with this and other one I tried error is shown and debugger shows this line selected: Dim db As Database
Any ideas on what could be the problem? I am using Access 2000

[FishVal]

Hi, there.

Have you referenced "Microsofrt DAO x.x object library" ?
And you don't need to create object variable at all to run Execute method one time. And what you expect from db.Close command?
Additionally DoCmd.RunSQL does the same.

[HyBry]

well.... I have nothing of that.
I am a total noob at this. And to be honest the code was copied from somewhere and just adjusted the table and field names.
I played with the DoCmd.runSQl and got some results but not yet working

Private Sub Update_frequency_NotInList(NewData As String, Response As Integer)
    Dim question As Integer
    question = MsgBox("Add " & NewData & " to the list?", _
                       vbQuestion + vbYesNo)
    If question = vbYes Then
    DoCmd.RunSQL "INSERT INTO tblFrequency (Update_Frequency) VALUES (""" & NewData & """)"
    Else
    Response = acDataErrContinue
    End If
End Sub

It adds the new value to the table but it does not allow it to be used and displays default not in list error message after I have clicked yes to add it to the table. Tried adding: update_frequenc y.requery without success...

[FishVal]

Setting
Response = acDataErrAdded
will avoid error message.

[HyBry]

Brilliant!!!
works like magic.

Thank you very much!!