NotInList Error

[darude]

Hello,
I have the NotInList code on a combo box (see below):
I'm getting the error message -
"You must use the dbSeeChanges option w/OpenRecordset when
accessing a SQL Server table w/an identity column".

If anyone has seen this message before and can point me in the right direction it would be greatly appreciated. Thanks in advance.

Private Sub cboStudentJobTitle_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim db As Database
Dim rst As Recordset

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "Add new job title to the list?") Then
Response = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("OccupationCodes", dbSeeChanges)
rst.AddNew
rst!OccupationTitle = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If
End Sub

 

[boblarson]

Have you tried changing this line:

Set rst = db.OpenRecordset("OccupationCodes", dbSeeChanges)


to this:

Set rst = db.OpenRecordset("OccupationCodes")

 

[TheAceMan1]

darude . . .

You could also try an append SQL instead of the recordset:

[blue]   Dim db As DAO.Database, SQL As String
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   Set db = CurrentDb
   DL = vbNewLine & vbNewLine
   
   Msg = "'" & NewData & "' is not in the list." & DL & _
         "Would you like to add it?"
   Style = vbQuestion + vbYesNo
   Title = "Add new job title to the list?"
   
   If MsgBox(Msg, Style, Title) = vbYes Then
      [b]SQL = "INSERT INTO [purple][i]TableName[/i][/purple] (OccupationTitle) " & _
            "VALUES ('" & NewData & "');"
      db.Execute SQL, dbFailOnError[/b]
      Response = acDataErrAdded
   Else
      Response = acDataErrDisplay
   End If
   
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886