Requery of DAO Recordset Fails on Third SQL String

[jamescox]

Have a Access db with a tblUsers which contains a user ID field (text)
a DBAdmin field (Yes/No) and a LocalDBAdmin field (Yes/No). I'v
written a subroutine to determing what settings are in effect for
user, using a single DAO recordset and a QueryDef. It works fine fo
the first requery, but the second one always returns an empty recordset
even when there is something to be returned. Changing the order of th
requeries indicates that the SQL expresions are correct - regardless o
what order they are in, the third never works properly.

Any ideas what's going wrong?

Option Compare Database
Option Explicit

Public glUserClass As Long


Public Sub UserClass()

'Called from Form_Load of Switchboard; sets glUserClass
' 3 = DBAdmin
' 2 = Local DBAdmin
' 1 = Responsible Person
' 0 = no access

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qryDef As QueryDef
Dim strSQL As String
Dim strUser As String

'Needed to handle the rs.MoveNext if rs is empty
On Error Resume Next

strUser = Environ$("USERNAME")

Set db = CurrentDb

'First check to see if user is in tblUsers at all - exit if not
strSQL = "SELECT * FROM tblUsers WHERE UserID = """ & strUser
""";"

'Create QueryDef - note that use of "" in the CreateQueryDef make
this a temporary QueryDef that
' is not added to the database's QueryDefs collection
Set qryDef = db.CreateQueryDef("", strSQL)
Set rs = qryDef.OpenRecordset
rs.MoveLast

' Set rs = db.OpenRecordset(strSQL)
If rs.EOF Then
glUserClass = 0
GoTo Clean_Up
End If

'Now check to see if user is DBAdmin - exit if True
' Set up new SQL string
strSQL = "SELECT * FROM tblUsers WHERE UserID = """ & strUser & ""
and DBAdmin = -1;"

'Update QueryDef SQL then requery existing recordset
qryDef.SQL = strSQL
rs.Requery qryDef
rs.MoveLast

If Not rs.EOF Then
glUserClass = 3
GoTo Clean_Up
End If

'Now check to see if user is Local DBAdmin - exit if True
' Set up new SQL string
strSQL = "SELECT * FROM tblUsers WHERE UserID = """ & strUser & ""
and LocalDBAdmin = -1;"

'Update QueryDef SQL then requery existing recordset
qryDef.SQL = strSQL
rs.Requery qryDef
rs.MoveLast

If Not rs.EOF Then
glUserClass = 2
GoTo Clean_Up
End If

'If we've made it to here, then the user is in class 1
glUserClass = 1

Clean_Up:

Set qryDef = Nothing

Set rs = Nothing

Set db = Nothing

End Su

 

[Douglas J. Steele]

Using On Error Resume Next is seldom a good idea: show-stopping errors get
covered up that way. What happens if you comment out that line: do you get a
meaningful error message? The proper way to handle the rs.MoveNext error if
rs is empty is to trap that specific error: see what Allen Browne has at
http://www.allenbrowne.com/ser-23a.html for an example.

Realistically, there's no reason to query the table multiple times: once
should suffice. There's also not really any need to use recordsets: DLookup
should suffice. I believe the following is the equivalent of what you have:

Public Sub UserClass()

'Called from Form_Load of Switchboard; sets glUserClass
' 3 = DBAdmin
' 2 = Local DBAdmin
' 1 = Responsible Person
' 0 = no access

Dim lngClass As Long
Dim strUser As String

strUser = Environ$("USERNAME")
lngClass = Nz(DLookup("Abs(DBAdmin) * 2 + Abs(LocalDBAdmin) * 1 + 1", _
"tblUsers", "UserID = """ & strUser & """"), 0)
If lngClass > 3 Then
glClass = 3
Else
glClass = lngClass
End If

End Sub

Recognize, too, that using Environ$("USERNAME") to retrieve the user id is
risky, as it's extremely trivial to reset the environment variable for the
duration of your Access session. You're far, far better off using the code
in http://www.mvps.org/access/api/api0008.htm at "The Access Web"

 

[David W. Fenton]

'Create QueryDef - note that use of "" in the CreateQueryDef makes
‘ this a temporary QueryDef that is not added to the database's
‘ QueryDefs collection
Set qryDef = db.CreateQueryDef("", strSQL)
Set rs = qryDef.OpenRecordset

It’s silly to create a temporary QueryDef for this purpose. Just
open your recordset with the SQL string you passed to
CreateQueryDef:

Set rs = db.OpenRecordset(strSQL)

rs.MoveLast

The .MoveLast serves no purpose.

' Set rs = db.OpenRecordset(strSQL)
If rs.EOF Then

It’s much more efficient to test for rs.RecordCount > 0:

If rs.RecordCount > 0 Then