Null error

[Guest]

Hi,

I am a new user to Access 2003. I have a table with name of people, there
windows nt log in info, and an assigned role for them. I have written code
to grab the windows nt log in during form load, and look through the table
for that name and grab the role and store it in a variable. What I am
running to is if there is someone that is not in the table that logs in, it
will look in the table for that name, but it returns null in this case since
it didn't mind a match. So, it is storing a null in a variable and it
doesn't like that. It gives me the following error "Either BOF or EOF is
True, or the current record has been deleted. Requested opearation requires a
current record." Here is the code that I have:

SQLStmt = "SELECT User_CAI_Roles FROM tblUserCAI WHERE User_CAI = '" &
UserName & "'"
Set rs = CurrentProject.Connection.Execute(SQLStmt)
strRole = rs("User_CAI_Roles")

'If "A" then its Administrator, and so give access to all fields
If strRole = "A" Then
Engagement_Name.Enabled = True

 

[George Nicholson]

strRole = rs("User_CAI_Roles")
If rs doesn't contain a record, the above line causes an error.
You're not storing Null in a variable (you aren't getting that far). You are
trying to retrieve a value from an empty recordset (which isn't possible).

You need to test if there is a current record before you attempt to read
from the recordset. EOF (EndOfFile) is frequently used for this. EOF will be
True if the recordset is empty (or if you have moved past the last record).

You probably want something like (aircode):

SQLStmt = yada yada
Set rs = CurrentProject.Connection.Execute(SQLStmt)
If rs.EOF = True Then
'Do whatever you need to do when there is no matching record. For
instance:
MsgBox "Sorry, you are an Unrecognized User. Please contact an
Administrator for Access to this file. Goodbye."
Application.Quit acQuitSaveNone
Else
strRole = rs("User_CAI_Roles")

'If "A" then its Administrator, and so give access to all fields
If strRole = "A" Then
Engagement_Name.Enabled = True
End If
End If

HTH,