Results 1 to 11 of 11

Nav Form Access

  1. #1
    sparker75 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    54

    Nav Form Access

    Hello,



    I'm trying to add a On Load function to disable Navigation buttons based off of the users group access. I have a three tables.

    tblObjectUser - This contains the logged in username and the access group they belong to
    tblObjectAccess - This contains the group access levels
    tblObjectFrmAccess - This is related to the tblObjectFrmAccess and contains the Navigation Button name and if the access group has access

    This is the output of the query I made and is filtered by the Username()

    Click image for larger version. 

Name:	Capture.JPG 
Views:	19 
Size:	14.9 KB 
ID:	35472

    Based on the output of the query I would like to disable any Nav Button that they do not have access too. If formName is null or HasAccess is null then disable button.

    I have very limited experience with VBA and I'm learning slowly. I have mashed some code together below but need help putting it all together. I appreciate your help!



    Private Sub Form_Load()

    Dim UserAccess As String

    UserAccess = "SELECT tblObjectUser.USERNAME, tblObjectFrmAccess.FormName, tblObjectFrmAccess.HasAccess FROM (tblObjectAccess INNER JOIN tblObjectFrmAccess ON tblObjectAccess.AccessID = tblObjectFrmAccess.AccessID) INNER JOIN tblObjectUser ON tblObjectAccess.AccessID = tblObjectUser.AccessID " _
    & "Where (tblObjectUser.USERNAME) LIKE '*" & UserName()
    If UserAccess = False Then
    Me.NotificationsNav.Enabled = False 'Admin'
    Me.MyTasksNav.Enabled = False 'Admin'
    Me.MessagesNav.Enabled = False 'Admin'
    Me.ExecutionNav.Enabled = False 'Admin'
    Me.SettingsNav.Enabled = False 'Admin'
    Me.AdminNav.Enabled = False 'Admin'
    Me.CostControlNav.Enabled = False 'Admin'
    Me.ProjectControlNav.Enabled = False 'Admin'
    Me.OrdersNav.Enabled = False 'Admin'
    Me.DocumentControlNav.Enabled = False 'Admin'
    End If

    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    So what happens - error, wrong result, nothing?

    Please post lengthy code between CODE tags to retain indentation and readability.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    sparker75 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    54
    This code is not functioning. It is just a bad example.

    Im trying to get the output of UserAccess to be true or false. I think what is missing is the FormName in relation to the Navigation button name and if the tblObjectFrmAccess.HasAccess is blank


    UserAccess = "SELECT tblObjectUser.USERNAME, tblObjectFrmAccess.FormName, tblObjectFrmAccess.HasAccess FROM (tblObjectAccess INNER JOIN tblObjectFrmAccess ON tblObjectAccess.AccessID = tblObjectFrmAccess.AccessID) INNER JOIN tblObjectUser ON tblObjectAccess.AccessID = tblObjectUser.AccessID " _
    & "Where (tblObjectUser.USERNAME) LIKE '*" & UserName() " _
    & "Where (tblObjectFrmAccess.FormName) LIKE '*" & "NotificationsNav" " _

    If UserAccess = False Then
    Me.NotificationsNav.Enabled = False 'Admin'

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    Looks like UserAccess is a string variable that is populated with a SQL statement. However, you don't do anything with the SQL, such as open a recordset object. Of course the code does not work because you are comparing this string to a Boolean.

    Possibly you really want to use DLookup() function. Build a query object that includes relevant fields. Then reference that query as source in DLookup.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    sparker75 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    54
    Can I do a DLookup with a Query?

    I would like to look into a query I created called qryUserAccess where the FormName = NotifcationsNav and if HasAccess is null or does not exist then
    Me.NotificationsNav.Enabled = False

    Can you show me how to do this in VBA?


    If DLookup("HasAccess", "Query!qryUserAccess", "FormName='" & NotificationsNav & "'") = False Then
    Me.NotificationsNav.Enabled = False

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    Syntax looks good. But don't you also need username in the criteria?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  7. #7
    sparker75 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    54
    I think I have it now but I need help with one fix. The query qryUserAccess is pulling the computer username of the person who is logged in and seeing if the HasAccess is true for the FormName in qryUserAccess. So if DocumentControlNav is text then is the syntax correct?


    If Nz(DLookup("HasAccess", "qryUserAccess", "FormName=" & "DocumentControlNav"), False) = False Then
    Me.DocumentControlNav.Enabled = False

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    Don't need to concatenate literal text but do need apostrophe delimiters for text parameter.

    "FormName='DocumentControlNav'"
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  9. #9
    sparker75 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    54
    That worked but it is given erratic responses. If I remove the criterial UserName() from the query and add it to this statement maybe this will help?

    Right now it is looking if FormName=DocumentControlNav

    How do I add USERNAME = UserName() to this string?

    If Nz(DLookup("HasAccess", "qryUserAccess", "FormName = 'DocumentControlNav'"), False) = False Then


    Im using this global function to get the UserName of the person logged in

    Public Function UserName()
    UserName = CreateObject("wscript.network").UserName
    End Function



    queryUserAccess

    Click image for larger version. 

Name:	UserAccess.JPG 
Views:	9 
Size:	26.4 KB 
ID:	35512






    Also am I separating each line correctly with the ElseIf? I'm trying to turn on or off these Nav Tabs

    I appreciate your help with this!

    Scott



    Private Sub Form_Load()
    If Nz(DLookup("HasAccess", "qryUserAccess", "FormName = 'DocumentControlNav'"), False) = False Then
    Me.DocumentControlNav.Enabled = False
    ElseIf Nz(DLookup("HasAccess", "qryUserAccess", "FormName = 'NotificationsNav'"), False) = False Then
    Me.NotificationsNav.Enabled = False
    ElseIf Nz(DLookup("HasAccess", "qryUserAccess", "FormName = 'OrdersNav'"), False) = False Then
    Me.OrdersNav.Enabled = False
    ElseIf Nz(DLookup("HasAccess", "qryUserAccess", "FormName = 'ExecutionNav'"), False) = False Then
    Me.ExecutionNav.Enabled = False
    ElseIf Nz(DLookup("HasAccess", "qryUserAccess", "FormName = 'ProjectControlNav'"), False) = False Then
    Me.ProjectControlNav.Enabled = False
    ElseIf Nz(DLookup("HasAccess", "qryUserAccess", "FormName = 'CostControlNav'"), False) = False Then
    Me.CostControlNav.Enabled = False
    ElseIf Nz(DLookup("HasAccess", "qryUserAccess", "FormName = 'AdminNav'"), False) = False Then
    Me.AdminNav.Enabled = False
    End If
    End Sub

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    If Nz(DLookup("HasAccess", "qryUserAccess", "FormName = 'DocumentControlNav' AND [username]='" & UserName() & "'"), False) = False Then

    Example from my db that sets properties of pages of Tab control:
    Code:
    Private Sub ShowTabs()
    With Me
    .tbxSet = TestSet(.tbxLABNUM, "Soils & Aggregate")
    .pge1.Visible = True
    .pge2.Visible = True
    .pge3.Visible = True
    .pge4.Visible = True
    .pge5.Visible = True
    .pge6.Visible = True
    .tbxLABNUM.SetFocus
    'use Val to capture characters preceeding :### which will be present if gradation test selected
    If Not Val(.tbxSet) Like "*1*" Then
        .pge1.Visible = False
    End If
    If Not Val(.tbxSet) Like "*2*" Then
        .pge2.Visible = False
    End If
    If Not Val(.tbxSet) Like "*3*" And Not Val(.tbxSet) Like "*E*" Then
        .pge3.Visible = False
    End If
    If Not .tbxSet Like "*P*" Then
        .pge4.Visible = False
    End If
    If Not .tbxSet Like "*V*" Then
        .pge5.Visible = False
    Else
        .pge2.Visible = False
    End If
    If Not .tbxSet Like "*H*" Then
        .pge6.Visible = False
    End If
    If .pge6.Visible Then
        .pge6.SetFocus
    ElseIf .pge1.Visible Then
        .pge1.SetFocus
    ElseIf .pge2.Visible Then
        .pge2.SetFocus
    ElseIf .pge3.Visible Then
        .pge3.SetFocus
    ElseIf .pge4.Visible Then
        .pge4.SetFocus
    ElseIf .pge5.Visible Then
        .pge5.SetFocus
    End If
    End With
    End Sub
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  11. #11
    sparker75 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    54
    Works Perfectly! Thank you again June.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 04-19-2018, 11:57 AM
  2. Replies: 3
    Last Post: 06-24-2017, 05:39 PM
  3. Replies: 16
    Last Post: 05-04-2014, 08:19 AM
  4. Replies: 1
    Last Post: 07-01-2013, 08:46 AM
  5. Replies: 6
    Last Post: 12-03-2012, 04:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums