Adapting my code

A

aron.ridgway

Registered User.
Local time
Today, 12:55
Joined
Apr 1, 2014
Messages
148
i have the following code that compares the environ username with the username. I need to adapt it so it looks at and compares the Username Sting and finds the ID for that field? thanks

Code: 
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 Companyname, Permission FROM tblUserPermission WHERE Username = '" & Me.txtName.Value & "'")
    
    Do While Not rsO.EOF
        Select Case rsO.Fields("Companyname")
            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, I seriously suggest you change your Design of your table. The userPermission table should have ID's of the company and users, the combo box should have the rowsource of ID, userName with the ID Column hidden. so you do not need to worry about any of this. What should have been simple is quiet complicated in your scenario, this is my VHO.
 
Hi Paul

i've got the feeling its over complicated at the moment. Ive adapted my user permission table. how can i adapt my code to still run the cases to hide the buttons if they havnt got permission?

thanks Aron:banghead:
 

Attachments

  • tbl Permission.JPG
    tbl Permission.JPG
    19.7 KB · Views: 141
Good now you are on the right track. Lets go from here.
Code: 
Set rsO = CurrentDb.OpenRecordset("SELECT tblUserPermission.UserID, tblUserPermission.CompanyFK, tblUserPermission.Permission " & _
                                  "FROM tblUserPermission INNER JOIN tblUsers ON tblUserPermission.UserID = tblUsers.UserID " & _
                                  "WHERE Username = '" & Me.txtName.Value & "'")
 
I have modified the code slightly

Code: 
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 & "'")

It is now falling over at the select case rsO.fields("Companyname")

Code: 
Do While Not rsO.EOF
        Select Case rsO.Fields("Companyname")
            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

thanks
 
You need to change that Companyname to Company FK, and the Case options to Numbers as defined in your Company Table mate !
Code: 
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
        Exit Sub
    End If
    
    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 [COLOR=Green]'"Rothschild Waddesdon Ltd"[/COLOR]
                Me.btnRWL.Visible = rsO.Fields("Permission")
            Case 2 [COLOR=Green]'"Five Arrows Hotel"[/COLOR]
                Me.btnFAH.Visible = rsO.Fields("Permission")
            Case 3 [COLOR=Green]'"Rothschild Foundation Grants"[/COLOR]
                Me.btnRFG.Visible = rsO.Fields("Permission")
            Case 4 [COLOR=Green]'"Rothschild Foundation Property"[/COLOR]
                Me.btnRFP.Visible = rsO.Fields("Permission")
            Case 5 [COLOR=Green]'"Rothschild Foundation Waddesdon"[/COLOR]
                Me.btnRFW.Visible = rsO.Fields("Permission")
        End Select
        rsO.MoveNext
    Loop
    Set rsO = Nothing
End Sub
 
I've tried the following changes, The code works with no errors but the Menu is returning all the buttons. But in my table i should on have access to one.

Code: 
Do While Not rsO.EOF
        Select Case rsO.Fields("Companyname")
            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
:confused:
 
Did you even check my code in Post#6? :rolleyes:
pr2-eugin said:
You need to change that Companyname to Company FK, and the Case options to Numbers as defined in your Company Table mate !
Code: 
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
        Exit Sub
    End If
    
    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("[SIZE=5][COLOR=Red][B]CompanyFK[/B][/COLOR][/SIZE]")
            Case [SIZE=5][COLOR=Red][B]1[/B][/COLOR][/SIZE] [COLOR=Green]'"Rothschild Waddesdon Ltd"[/COLOR]
                Me.btnRWL.Visible = rsO.Fields("Permission")
            Case [SIZE=5][COLOR=Red][B]2[/B][/COLOR][/SIZE] [COLOR=Green]'"Five Arrows Hotel"[/COLOR]
                Me.btnFAH.Visible = rsO.Fields("Permission")
            Case [SIZE=5][COLOR=Red][B]3[/B][/COLOR][/SIZE] [COLOR=Green]'"Rothschild Foundation Grants"[/COLOR]
                Me.btnRFG.Visible = rsO.Fields("Permission")
            Case [SIZE=5][COLOR=Red][B]4[/B][/COLOR][/SIZE] [COLOR=Green]'"Rothschild Foundation Property"[/COLOR]
                Me.btnRFP.Visible = rsO.Fields("Permission")
            Case [SIZE=5][COLOR=Red][B]5[/B][/COLOR][/SIZE] [COLOR=Green]'"Rothschild Foundation Waddesdon"[/COLOR]
                Me.btnRFW.Visible = rsO.Fields("Permission")
        End Select
        rsO.MoveNext
    Loop
    Set rsO = Nothing
End Sub
Click to expand...
 
It has worked now i have put all the company's in with my user permission table!
Thank you! Could you summaries what rsO is doing, i understand most of it i think????


Also what would be the best way to have a form filter on companys and list all the users, with there Permission status yes/no??

thanks :D

Posted the wrong code my Bad!!:rolleyes:
 
aron.ridgway said:
It has worked now i have put all the company's in with my user permission table!
Click to expand...
Back to Square 1 again :banghead:. Time to move on !
aron.ridgway said:
Thank you! Could you summaries what rsO is doing, i understand most of it i think????
Click to expand...
Recordsets for Beginners - This will explain everything.
aron.ridgway said:
Also what would be the best way to have a form filter on companys and list all the users, with there Permission status yes/no??
Click to expand...
Start a new thread ! Someone else will respond to it. :rolleyes:
 
Ha i meant filled in the Permission table with the company ID's and User ID's,

If theirs no data, then there is nothing to find! :o

completed Code bellow, Thanks for the Help have a good weekend!!

Code: 
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 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.btnRWL.Visible = rsO.Fields("Permission")
                Me.lblRWL.Visible = rsO.Fields("Permission")
            Case 2
                Me.btnFAH.Visible = rsO.Fields("Permission")
                Me.lblFAH.Visible = rsO.Fields("Permission")
            Case 3
                Me.btnRFG.Visible = rsO.Fields("Permission")
                Me.lblRFG.Visible = rsO.Fields("Permission")
            Case 4
                Me.btnRFP.Visible = rsO.Fields("Permission")
                Me.lblRFP.Visible = rsO.Fields("Permission")
            Case 5
                Me.btnRFW.Visible = rsO.Fields("Permission")
                Me.lblRFW.Visible = rsO.Fields("Permission")
        End Select
        rsO.MoveNext
    Loop
    
    Set rsO = Nothing
End Sub
 
You must log in or register to reply here.

Similar threads

A
Help changing code
Replies
3
Views
610
aron.ridgway
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom