MS Access Error 3622 vba

Question

I have an Access 2010 database that was local and I have since linked to a SQL 2012 database. However, I have a form to insert a highlight record that runs the below code:

Private Sub Command18_Click()
Dim R As Recordset
Set R = CurrentDb.OpenRecordset("SELECT * FROM [tblJobHead]")
R.AddNew
R![Rep Num] = [Forms]![frmMain]![NavigationSubform].[Form]![RepNum]
R![Item Number] = Me.ItemNumber.Value
R![Description] = Me.Desc.Value
R![EmpID] = [TempVars]![EmpID]
R![Status] = 2
R.Update
R.Close
Set R = Nothing
DoCmd.Save
End Sub

However, when I click the button I now receive the error:

Error 3622 - You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column

Any ideas?

Regards,

Michael

Answer 1

The error is exactly as it says, try:

Function GetRecordset(sSQL) As DAO.Recordset
Dim rdao As DAO.Recordset
Dim db As Database

    Set db = CurrentDb

    Set rdao = db.OpenRecordset(sSQL, dbOpenDynaset, _
          dbFailOnError + dbSeeChanges)

    If Not rdao.EOF Then
        rdao.MoveLast
        rdao.MoveFirst
    End If

    Set GetRecordset = rdao

End Function

I would strongly advise you to avoid single letters as variables.