Help changing code

[aron.ridgway]

Hi There i have the following code that hides 5 buttons depending if the user has permission which works fine.

im trying to incorporate an IF statement if there is only 1 of the buttons visible then to automatically click the button. but if there is more than 1 do noting?

Private Sub Form_Open(Cancel As Integer)

Dim rsO As DAO.Recordset
 
    
Set rsO = CurrentDb.OpenRecordset("SELECT tblUserPermission.UserFK, tblUserPermission.CompanyFK, tblUserPermission.Permission " & _
                                  "FROM tblUserPermission INNER JOIN tblUser ON tblUserPermission.UserFK = tblUser.UserPK " & _
                                  "WHERE Username = '" & Me.txtName.Value & "'")
    
    Do While Not rsO.EOF
        Select Case rsO.Fields("CompanyFK")
            Case 1
                Me.cmdRWL.Visible = rsO.Fields("Permission")
                Me.lblRWL.Visible = rsO.Fields("Permission")
            Case 2
                Me.cmdFAH.Visible = rsO.Fields("Permission")
                Me.lblFAH.Visible = rsO.Fields("Permission")
            Case 3
                Me.cmdRFG.Visible = rsO.Fields("Permission")
                Me.lblRFG.Visible = rsO.Fields("Permission")
            Case 4
                Me.cmdRFP.Visible = rsO.Fields("Permission")
                Me.lblRFP.Visible = rsO.Fields("Permission")
            Case 5
                Me.cmdRFW.Visible = rsO.Fields("Permission")
                Me.lblRFW.Visible = rsO.Fields("Permission")
        End Select
        rsO.MoveNext
    Loop
    
    Set rsO = Nothing
End Sub

 

[plog]

In general this is how I would do it. Before you run the query you have set up, run another query to determine how many Permission fields for that user are set to True and assign that to a variable (Dim VisibleButtons).

Then in each Case, after you set the button visible you add your If statement. That if would test to see if VisibleButtons=1 and if the current Permission is true. If both are, you execute the underlying code for that button.

 

[namliam]

Add a variable.... myCount

then count the permissions just before you "Movenext"

myCount = myCount + rsO.Fields("Permission")

then at the end
If mycount = -1 then
check for the visible button and run its code....
endif

 

[aron.ridgway]

Thank you namliam just got round to trying your suggestion! works a treat thank you!!