Show and Hide Command Buttons

[aron.ridgway]

I'm trying to alter the look of my menu . Depending on the User's Permission setting from the Table .

If they do not have permission for a company then, i want it to be hidden from the menu?

Any tips would be appreciated

thanks

 

[pr2-eugin]

Private Sub Form_Load()
    Dim rsO As DAO.Recordset
    
    Set rsO = CurrentDB.OpenRecordset("SELECT CompanyID, Permission FROM tblPermission WHERE UserID = '" & userID & "'")
    
    Do While Not rsO.EOF
        Select Case rsO.Fields("CompanyID")
            Case "Roth...."
                Me.RWL.Visible = rsO.Fields("Permission")
            Case "Five Arr.."
                Me.FAH.Visible = rsO.Fields("Permission")
            Case "Roth.. Foundation Grant"
                Me.RFG.Visible = rsO.Fields("Permission")
            Case "Roth.. Foundation Property"
                Me.RFP.Visible = rsO.Fields("Permission")
            Case ""Roth.. Foundation Wal.."
                Me.RFW.Visible = rsO.Fields("Permission")
        End Select
        rsO.MoveNext
    Loop
    
    Set rsO = Nothing
End Sub

Just that you know UserID -> ID denotes numeric value, CompanyID -> ID again denotes numeric value. It would be better if you follow a Good Naming convention.

 

[aron.ridgway]

Thank you the naming convention makes sense! My code is as follows i have updated with my new naming conventions. Its failing when setting the rsO?

Should i be doing anything with my form recordsource?

appreciate the help!

Private Sub Form_Open(Cancel As Integer)
    If DCount("*", "tblUser", "userName = '" & Environ("username") & "'") = 0 Then
        MsgBox Environ("username") & " You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
        Cancel = True
    Else
       Me.lblUserName.Caption = Environ("Username")
     End If
  
  
  
  
  Dim rsO As DAO.Recordset
    
    Set rsO = CurrentDb.OpenRecordset("SELECT CompanyFK, Permission FROM tblUserPermission WHERE UserFK = '" & UserFK & "'")
    
    Do While Not rsO.EOF
        Select Case rsO.Fields("CompanyID")
            Case "Roth...."
                Me.btnRWL.Visible = rsO.Fields("Permission")
            Case "Five Arr.."
                Me.btnFAH.Visible = rsO.Fields("Permission")
            Case "Roth.. Foundation Grant"
                Me.btnRFG.Visible = rsO.Fields("Permission")
            Case "Roth.. Foundation Property"
                Me.btnRFP.Visible = rsO.Fields("Permission")
            Case "Roth.. Foundation Wal.."
                Me.btnRFW.Visible = rsO.Fields("Permission")
        End Select
        rsO.MoveNext
    Loop
    
    Set rsO = Nothing
   
     
End Sub

 

[pr2-eugin]

I used UserId in there hoping you would have a control on the form, if this is just the continuation, then use.

Private Sub Form_Open(Cancel As Integer)
    If DCount("*", "tblUser", "userName = '" & Environ("username") & "'") = 0 Then
        MsgBox Environ("username") & " You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
        Cancel = True
    Else
        Me.lblUserName.Caption = Environ("Username")
    End If
    
    Dim rsO As DAO.Recordset
    
    Set rsO = CurrentDb.OpenRecordset("SELECT CompanyFK, Permission FROM tblUserPermission WHERE UserFK = '" & Environ("Username") & "'")
    
    Do While Not rsO.EOF
        Select Case rsO.Fields("CompanyID")
            Case "[COLOR=Red][B]Roth....[/B][/COLOR]"
                Me.btnRWL.Visible = rsO.Fields("Permission")
            Case "[COLOR=Red][B]Five Arr..[/B][/COLOR]"
                Me.btnFAH.Visible = rsO.Fields("Permission")
            Case "[COLOR=Red][B]Roth.. Foundation Grant[/B][/COLOR]"
                Me.btnRFG.Visible = rsO.Fields("Permission")
            Case "[B][COLOR=Red]Roth.. Foundation Property[/COLOR][/B]"
                Me.btnRFP.Visible = rsO.Fields("Permission")
            Case "[B][COLOR=Red]Roth.. Foundation Wal..[/COLOR][/B]"
                Me.btnRFW.Visible = rsO.Fields("Permission")
        End Select
        rsO.MoveNext
    Loop
    
    Set rsO = Nothing
End Sub

Please not, you have to edit the highlighted sections. I was being lazy and did not type the whole thing. When saying about Naming convention, what I meant was, the junction table would/should store the ID's of the company and users not the names.

 

[aron.ridgway]

Thank you for the Reply i will update my code and see how it goes. Yep they are linked to the ID, its just ive hidden the 1st column to display just the description.

When you say control is that when you link a form to a recordsource?

thanks

 

[pr2-eugin]

Control - Anything on a Form that could represent/hold data, this may include but not limited to TextBox, ComboBox, ListBox, Label, OptionBox, Checkbox etc.

What I meant by that was, I was hoping you would have a TextBox or ComboBox that will have the user name stuck to it, so it could be filtered based on that, but looking at your code I understood you only had the caption set. Anyway the code should work now.

 

[aron.ridgway]

ah ok i understand, i have tried the bellow code but im getting a 'Data Type mismatch in criteria expression'. Is this because im trying to compare the Companyfk which is numeric to a text string of the Environ?

thanks

Private Sub Form_Open(Cancel As Integer)
    If DCount("*", "tblUser", "userName = '" & Environ("username") & "'") = 0 Then
        MsgBox Environ("username") & " You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
        Cancel = True
    Else
       Me.lblUserName.Caption = Environ("Username")
     End If
  
  

  
  Dim rsO As DAO.Recordset
    
    Set rsO = CurrentDb.OpenRecordset("SELECT CompanyFK, Permission FROM tblUserPermission WHERE UserFK = '" & Environ("Username") & "'")
    
    Do While Not rsO.EOF
        Select Case rsO.Fields("CompanyFK")
            Case "Rothschild Waddesdon Ltd"
                Me.btnRWL.Visible = rsO.Fields("Permission")
            Case "Five Arrows Hotel"
                Me.btnFAH.Visible = rsO.Fields("Permission")
            Case "Rothschild Foundation Grants"
                Me.btnRFG.Visible = rsO.Fields("Permission")
            Case "Rothschild Foundation Property"
                Me.btnRFP.Visible = rsO.Fields("Permission")
            Case "Rothschild Foundation Waddesdon"
                Me.btnRFW.Visible = rsO.Fields("Permission")
        End Select
        rsO.MoveNext
    Loop
    
    Set rsO = Nothing
End Sub

 

[pr2-eugin]

You have all the answers.

Is this because im trying to compare the Companyfk which is numeric to a text string of the Environ?

Although you would be using Userfk not comparing it with Companyfk.

 

[aron.ridgway]

Ive changed the bound column to use the text (is this bad practice?)

The code is now running without error but all the buttons are showing, even though only 3 are ticked with permission? any ideas?

Dim rsO As DAO.Recordset
    
    Set rsO = CurrentDb.OpenRecordset("SELECT CompanyFK, Permission FROM tblUserPermission WHERE UserFK = '" & Environ("Username") & "'")
    
    Do While Not rsO.EOF
        Select Case rsO.Fields("CompanyFK")
            Case "Rothschild Waddesdon Ltd"
                Me.btnRWL.Visible = rsO.Fields("Permission")
            Case "Five Arrows Hotel"
                Me.btnFAH.Visible = rsO.Fields("Permission")
            Case "Rothschild Foundation Grants"
                Me.btnRFG.Visible = rsO.Fields("Permission")
            Case "Rothschild Foundation Property"
                Me.btnRFP.Visible = rsO.Fields("Permission")
            Case "Rothschild Foundation Waddesdon"
                Me.btnRFW.Visible = rsO.Fields("Permission")
        End Select
        rsO.MoveNext
    Loop
    
    Set rsO = Nothing
End Sub

 

[aron.ridgway]

Scrap that, i also made the change from numeric to text on the company field, which now it works!

Only issue is my User permission table has no relationships, im guessing this is probably a bad thing?