Not in List function

[ains26]

Hi I am Relatively new to using VBA and am having problems with my VBA code for a Not In List function.
I have form for entry of a candidates details with a combo box for entering their Ideal Job. This list has as its rowsource a table called JOB TITLE. I want to allow users to add new job titles to this list and have written the code as follows in the NotInList event procedure.

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

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

strMsg = "'" & NewData & "' is not a known Job Title " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this Job Title to the database?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-select Job Title"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("JOB TITLE")
On Error Resume Next
rs.AddNew
rs!Ideal_Job_Title = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End If

End Sub

This runs ok to the point where the user selects yes to add new data and then it returns an error message. I have used a similar code in another data base and it works fine. The problem seems to be when the new data is to be added to the table and not actually accessing the table. I'm pretty sure the problem lies in the following code

rs.AddNew
rs!Ideal_Job_Title = NewData
rs.Update

I should probally mention that I have another combo box called current job that uses the same table as its rou soure and requires the same Not In List function

Any help that you can offer would be much appreciated
Thanks

 

[Karma]

What's the error message?

Replace

MsgBox "An error occurred. Please try again."

with

MsgBox err.number & vbcrlf & err.description

I use this; not saying yours is wrong but it lets me capture all error messages and tells me what they are in case i haven't thought of something - pretty usual

Private Sub CMBO_GENERATOR_NotInList(NewData As String, Response As Integer)
Dim DB As DAO.Database
Dim rst As DAO.Recordset



If MsgBox("This item isn't on the list do you wish to add it?", vbInformation + vbYesNo, "Add item?") = vbNo Then
    Response = acDataErrContinue
    Exit Sub
    
End If

On Error GoTo ErrHandler
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT TBL_ISSUESLOG_GENERATOR.* FROM TBL_ISSUESLOG_GENERATOR", dbOpenDynaset, dbSeeChanges)

rst.AddNew
rst("GENERATOR") = NewData
rst.Update

Set DB = Nothing
Set rst = Nothing

Response = acDataErrAdded

Exit Sub
ErrHandler:
Select Case err.Number

Case Is = 3146

    MsgBox "Unable to add data, this entry is already in the list", vbExclamation, "Data entry error"

Case Else

    MsgBox err.Number & vbCrLf & err.Description

End Select
Set DB = Nothing
Set rst = Nothing
Response = acDataErrContinue

End Sub

 

[MStCyr]

Don't forget that you have to set the Limit To List to YES