question

Hanson74 avatar image
Hanson74 asked

Run-Time error 3622 - when trying to run a form from Access form linking to SQL server 2005

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
sql-server-2005error-messagemicrosoft-access
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
+1: nice. Did you resist the lmgtfy? :)
1 Like 1 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
Hah! very nearly did. taking deep-breathing pauses regularly
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Ok, for the aconym-challenged amoungst us, what is lmgtfy?
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@Tim - simply this http://lmgtfy.com/
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Tim: sorry about that. We are bound to see those type of questions here, it just comes with the territory. It's quite funny, sometimes the lmgtfy results can point back to the very question that caused someone to say lmgtfy. Still...helps to bring traffic here so that's got to be good!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.