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
Answer by Fatherjack ·
this link seems to resolve the same problem as you are encountering
Answer by Kev Riley ·
Yup, the error message tells you everything you need really, in that you need to specify the dbSeeChanges option.
I'm guessing it's on the second OpenRecordSet, where you are trying to return the ID (an IDENTITY ?) from the Nurses table.
I'm not over familiar with VBA, but it seems to be needed here (line breaks my own):
Set rs = CurrentDb.OpenRecordset ("SELECT tbl_Nurses.Nurse_ID FROM tbl_Nurses WHERE tbl_Nurses.Surname='" & Me.Combo51.Value & "'", dbSeeChanges)
the link provided by FatherJack also suggests that
dbOpenDynaset may also be needed