Open a DAO recordset on an attached SQL Server table with an identity 1

[TommyTea]

I am trying to Open a DAO recordset on an attached SQL Server table with an identity field. My code is:

Public Function VerifynewRecord()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
strSQL = "Select review_id from Review_Instance where reviewer = me.cmbIAm and " _
& "reviewed = me.cmbReviewed and role_id = Forms!frmReviewerID.cmbMyRole and " _
& "project_id = ProjectID() and reviewer_id = Forms!frmReviewerID.txtReviewerID"

Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly, dbSeeChanges, dbOptimistic)

End Function

If I do not use the dbSeeChanges option, I get error 3622 and it says the you must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

I am able to run the query (strSQL) successfuly as a query so I think that the syntax of the OpenRecordset is the problem. If I run it as, I get error 3061 Too few parameters. Expected 4.

What I need to accomplish is to test to see whether a record already exists in the SQL Server table and, if not,
to add it. Without this test I get a locked database on error.

 

[younguser]

Try do not use Forms!form_name.fieldName, use variable. If you want to use form!..., you should add "&" in front and back, like = & Forms!frmReviewerID.cmbMyRole & ...

 

[TommyTea]

Thanks to younguser! I was able to get the rest of the function to work. It's really great to have a tek-tips to come to when you have a problem, even if it's self inflicted.