Using SQL to create recordset then use those values 2

[cdgeer]

I'm trying to create a recordset with a simple SQL statement. I am then going to use the records in the recordset to do an IF Then statement to change the Enabled property for a control on the open form.
I can't figure out why I keep getting no results from the SQL statement. I keep getting a "=nothing" and/or "out of context". Not sure if the .Value = "Yes" is right (Yes/No chkbx). maybe should be "-1" instead, but... I can't even get that far because the SQL is not querying the table. (Which I can do from the Navigation Pane with a query, no problem)
Thanks for any help.

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strAdmin As String
Dim strPM As String
Dim strLM As String
Dim strRO As String
strSQL = "SELECT UserS3.UserID, UserS3.Administrator, UserS3.[Project Manager], UserS3.[Launch Manager], UserS3.[Read Only] FROM UserS3 WHERE (((UserS3.UserID)=GetLogonName()));"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
strAdmin = rst![Administrator]
strPM = rst![Project Manager]
strLM = rst![Launch Manager]
strRO = rst![Read Only]

If rst.Fields(strAdmin).Value = "Yes" Then Me.cmdOpenProgMgmt.Enabled = True Else: Me.cmdOpenProgMgmt.Enabled = False

rst.Close

End Sub

 

[dhookom]

First of all, please use the Code tgml functionality to make your code more readable. Is your Administrator field yes/no? If so you shouldn't use "As String". Also, rst.Fields(strAdmin).Value would be looking for a field name "0" or "-1". I would use Dim booAdmin As Boolean.

Consider:

Private Sub Form_Open(Cancel As Integer)
  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  Dim booAdmin As Boolean
  Dim strPM As String
  Dim strLM As String
  Dim strRO As String
  strSQL = "SELECT UserID, Administrator, [Project Manager], [Launch Manager], [Read Only] FROM UserS3 WHERE UserID = GetLogonName();"
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  rst.MoveFirst
  booAdmin = rst![Administrator]
  strPM = rst![Project Manager]
  strLM = rst![Launch Manager]
  strRO = rst![Read Only]
  Me.cmdOpenProgMgmt.Enabled = booAdmin
  rst.Close
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

Thanks dhookom!! That's perfect! I rarely use boolean values but ...now I remember. Thanks again.

 

[Andrzejek]

You may also try this:

Private Sub Form_Open(Cancel As Integer)
  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  [s]Dim booAdmin As Boolean[/s]
  Dim strPM As String
  Dim strLM As String
  Dim strRO As String
  strSQL = "SELECT UserID, Administrator, [Project Manager], [Launch Manager], [Read Only] FROM UserS3 WHERE UserID = GetLogonName();"
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  rst.MoveFirst
  [s]booAdmin = rst![Administrator][/s]
  strPM = rst![Project Manager]
  strLM = rst![Launch Manager]
  strRO = rst![Read Only][blue]
  Me.cmdOpenProgMgmt.Enabled = rst![Administrator][/blue]
  rst.Close
End Sub

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[cdgeer]

Nice shortcut! Thanks also, Andy!

-Carl

 

[dhookom]

I was also wondering why all the other variables. I assume you use them some place else:

  Dim strPM As String
  Dim strLM As String
  Dim strRO As String

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

Yeah, I was just trying to get results from the Administrator value first. The others should have been Boolean as well since they were all Yes/No check box controls. This is basically used as a kind of Access rights control. The GetLogonName() function compares the network login ID (NID) of the person that is opening the database and compares it to a list of people and their NIDs and what access they are allowed to have (i.e. Admin,ProjectMgr,LaunchMgr) in a table within the db. Whatever access that is checked decides what other Forms they can open (besides the initial startup form which contains the cmd buttons to open other forms.)

Thanks for your help! I get way more out of this than looking up stuff from MS Access development pages. I was actually able to figure out a couple of other things just from what I have learned here.

 

[dhookom]

Do you understand that you are storing data values in field names? If you need to add another role, you will have to add another field, modify queries and code and forms and reports. A more normalized table structure would have a table of roles and then a junction table of roles vs users. Instead of adding fields, you should add data.

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

Good point. I typically don't like to say never but, in this case, I know that these roles will not change or be added to.
I will keep that in mind for future projects though. Thanks again.