Runtime Error 3622 you must use dbSeeChanges

[JenniU]

Am Converting an Access 2000 database to SQL Express 2005. The Access database was divided into a backend (data) and front end (menus, queries, forms).
I am having problems with one of the forms, as the code which worked with Access is obviously wrong for sql.

specifically,
code as follows

If NewRecord=True Then
 SectionName="Item Search code"
 qry2="select top 1" &_
          "DeceasedID, DateOfEntry from dbo_AllFields where "&_
          "format(DateOfEntry, "yyyy-mm-dd"")='" & Format(DateOfEntry, "yyyy-mm-dd")&-
          "'and DataEntryBy= '" & CurrentUser &_
          "'and Dec_cn='" & Dec_cn &_
          "'andFamilyna='" & Familyna & "'" & _
          "order by DeceasedID desc"
 
Set rs = CurrentDb.openrecordset(qry2)
mDeathID = rs!DeceasedID
lblDeceasedID.Caption =mDeathID
End If

I realise i need the dbseeChanges command on the set rs line, but i have tried numerous combinations with no success.

Please help.

thanks

[NeoPa]

If NewRecord Then
 SectionName="Item Search code"
 qry2="select top 1 " & _
        "DeceasedID, DateOfEntry from dbo_AllFields where " & _
        "format(DateOfEntry, 'yyyy-mm-dd')='" & Format(DateOfEntry, 'yyyy-mm-dd') & _
        "' and DataEntryBy= '" & CurrentUser & _
        "' and Dec_cn='" & Dec_cn & _
        "' and Familyna='" & Familyna & _
        "' order by DeceasedID desc"
 
 Set rs = CurrentDb.openrecordset(qry2)
 mDeathID = rs!DeceasedID
 lblDeceasedID.Caption =mDeathID
End If

If you look through your code, or even look at qry2 before executing it, you'll see a fair number of typos where spaces are missing; (&) & (_) are used wrongly; etc.
I've updated the ones I noticed. With the code tags it's much easier to notice.

[JenniU]

Thank you i will make the changes and test again

If NewRecord Then
 SectionName="Item Search code"
 qry2="select top 1 " & _
        "DeceasedID, DateOfEntry from dbo_AllFields where " & _
        "format(DateOfEntry, 'yyyy-mm-dd')='" & Format(DateOfEntry, 'yyyy-mm-dd') & _
        "' and DataEntryBy= '" & CurrentUser & _
        "' and Dec_cn='" & Dec_cn & _
        "' and Familyna='" & Familyna & _
        "' order by DeceasedID desc"
 
 Set rs = CurrentDb.openrecordset(qry2)
 mDeathID = rs!DeceasedID
 lblDeceasedID.Caption =mDeathID
End If

If you look through your code, or even look at qry2 before executing it, you'll see a fair number of typos where spaces are missing; (&) & (_) are used wrongly; etc.
I've updated the ones I noticed. With the code tags it's much easier to notice.

[NeoPa]

No problem.
I'm happy to look again if you need me to, but try using Debug.Print qry2 to see exactly what it's trying to execute first.
You may find you see the problems yourself which can save you some time.