Please can anyone help me on this. I have just migrated my access database to SQL server 2005 using SSMA When i am trying to use the one of the forms in Access i am having this ERROR - 'Run-time error 3622' - You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL server table that has an IDENTITY column.
this is my code:
Public Sub Command23_Click()
If Me.Combo51.Value = "" Or IsNull(Me.Combo51.Value) = True Then
MsgBox "Please select the Nurse from the list for whom you want to enter clinical data", vbOKOnly + vbExclamation
Exit Sub
End If
If Me.backOk = False Then
Exit Sub
End If
Dim rs As DAO.Recordset
DoCmd.GoToRecord , , acNewRec
DoCmd.OpenForm "frmSearchPatient", acNormal, , , acFormEdit, acDialog
Me.THPCTID.Value = Me.lblthpct.Caption
Set rs = CurrentDb.OpenRecordset("SELECT [First name], Surname, [NHS number] FROM tbl_Patients WHERE tbl_Patients.[THPCT ID]=" & Me.lblthpct.Caption)
Me.PatientFirstName.Value = Nz(rs(0), "")
Me.PatientSurname.Value = Nz(rs(1), "")
Me.PatientNHSNo.Value = Nz(rs(2), "")
rs.Close
Me.Place_of_contact = "Home (including residential homes)"
Set rs = CurrentDb.OpenRecordset("SELECT tbl_Nurses.Nurse_ID FROM tbl_Nurses WHERE tbl_Nurses.Surname='" & Me.Combo51.Value & "'")
Me.Nurse_Id.Value = rs(0)
rs.Close
Me.PatientFirstName.SetFocus
Me.Command23.Enabled = False
End Sub