Dlookup or ? to compare windows login to a table of allowed accesses

[KyleB]

Perhaps this isn't the most appropriate method of verifying user identity, as someone could use an existing login on some server, or login with someone elses password, but I believe that shouldn't be an issue to deal with here. I've spent the past 8 hours poring over the boards, and can't quite find this particular scenario on the boards. It seems that either a Dcount, or a DLookup would be the most likely avenues to take, but I am unable to get an accurate result from my If Statements.

I've got my DB to query a current users login to the system, and would like to make some buttons visible for all users, and some buttons useable to just a specified list of users. If I hard-code the users names into the VBA codes for each action I am successful, but when I attempt to compare the textbox on the form to a table of allowable users I get a result that seems to be completely ignoring my if statement and showing everything to everyone. This is the function that is causing the problems:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Exit_Form_Open_Click

With DoCmd
    .Hourglass True
    .SetWarnings False
    .OpenQuery "qry Mar Geology Data"
    .OpenQuery "qry Apr Geology Data"
    .OpenQuery "qry Graphing Query"
      'These are update queries that I am currently running because my data
      'relies on some Excel sheets from another department that I import for
      'calculation purposes.
    .SetWarnings True
    .Hourglass False
End With

If DLookup("[Allowed Usernames]", "tbl Database Admins", "[Allowed Usernames] = " & [UserNameControl]) Then
     ' I have also tried various punctuation options based on examples on the
     'boards. The [Allowed Usernames] is the field with usernames in the table
     'called "tbl Database Admins", and [UserNameControl] is the textbox on
     'this form, which is generated via a module that queries the windows
     'username, in my case that is <kbeebe> which matches an entry in tbl
     'above.
   Me.Modify_Budget.Visible = True
   Me.Modify_Forecast.Visible = True
   Me.Modify_Calculation_Factors.Visible = True
   Me.Daily_Data_Entry.Visible = True
   Me.Email_Report.Visible = True
   Me.Email_Report_Text.Visible = True
   Me.Modify_Email_List.Visible = True
   Me.Database_Admin_List.Visible = True
Else
    Me.Modify_Budget.Visible = False
    Me.Modify_Forecast.Visible = False
    Me.Modify_Calculation_Factors.Visible = False
    Me.Daily_Data_Entry.Visible = False
    Me.Email_Report.Visible = False
    Me.Email_Report_Text.Visible = False
    Me.Modify_Email_List.Visible = False
    Me.Database_Admin_List.Visible = False
End If

Exit_Form_Open_Click:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub

End Sub

I have attempted some error trapping within both if options, but the msgbox's that I put in did not appear at any point. It seems as if the condition of the if statement is as far as it goes, and the form will open with all options visible no matter who logs in, whether they are in the database admin list or not.

 

[pbaldy]

Try something like this:

If DCount("[Allowed Usernames]", "tbl Database Admins", "[Allowed Usernames] = " & [UserNameControl]) >0 Then

If "Allowed Usernames" is text, it would need to be:

If DCount("[Allowed Usernames]", "tbl Database Admins", "[Allowed Usernames] = '" & [UserNameControl] & "'") >0 Then

If it's still goofing up, try a message box on the DCount and see what it's returning.

 

[KyleB]

I've tried this exact code before several times, but all I can get out of this syntax is a compile error saying that it's expecting a list separator or )

As a result, I'm a bit confused as to why it doesn't work, as I've seen it posted quite a few places here on the boards, and it's worked for others. I've double, triple, quadruple checked my syntax, and as far as I can tell, my code matches exactly, but it won't compile:

If DCount("[Allowed Usernames]", "tbl Database Admins", "[Allowed Usernames] = '" & [UserNameControl]"'")>0 Then

Ok, I know why this was giving the compile error, I forgot an ampersand:

If DCount("[Allowed Usernames]", "tbl Database Admins", "[Allowed Usernames] = '" & [UserNameControl] & "'")>0 Then

There before the last "'". However; the problem that I'm still encountering, is that this if statement seems to be ignored entirely, as above with the dlookup.

Within the if and also within the else statement I have placed msgbox entries, just for control, to see which result I'm getting. The problem is that niether message box ever shows up, it's as if the dcount is never evaluated. I get niether a true, nor a false evaluation from the statement, but by default it is showing all controls, as if the true statement was reached. If the true statement was reached I should have seen a msgbox, which I never do see. Could this be due to the fact that this code is within the OnOpen as opposed to the OnLoad function? It would make more sense to me to evaluate before the form is loaded into memory though, than for it to display the form, and then modify the appearance. Example:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Exit_Form_Open_Click

With DoCmd
    .Hourglass True
    .SetWarnings False
    .OpenQuery "qry Mar Geology Data"
    .OpenQuery "qry Apr Geology Data"
    .OpenQuery "qry Graphing Query"
    .SetWarnings True
    .Hourglass False
End With

If DCount("[Allowed Usernames]", "tbl Database Admins", "[Allowed Usernames] = '" & [UserNameControl] & "'") > 0 Then
MsgBox ("Dcount Evaluates as True, Boxes will be visible.")
   Me.Modify_Budget.Visible = True
   Me.Modify_Forecast.Visible = True
   Me.Modify_Calculation_Factors.Visible = True
   Me.Daily_Data_Entry.Visible = True
   Me.Email_Report.Visible = True
   Me.Email_Report_Text.Visible = True
   Me.Modify_Email_List.Visible = True
   Me.Database_Admin_List.Visible = True
Else
MsgBox ("Dcount Evaluates as False, Boxes will NOT be visible.")
    Me.Modify_Budget.Visible = False
    Me.Modify_Forecast.Visible = False
    Me.Modify_Calculation_Factors.Visible = False
    Me.Daily_Data_Entry.Visible = False
    Me.Email_Report.Visible = False
    Me.Email_Report_Text.Visible = False
    Me.Modify_Email_List.Visible = False
    Me.Database_Admin_List.Visible = False
End If

Exit_Form_Open_Click:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub

End Sub

 

[KyleB]

Hey, I've posted far too much in this message, but I was able to solve my problem. So I thought I'd post it here in case there's anyone else looking to do something of this sort. Looking at another post where I was initially trying to get my code to work I found a reference to using Environ("username") to determine the login of the current user. This particular reference saved me. I believe my problem in the previous examples was that the Module I was using to query the current user hadn't fired yet, as it is defined within the form. Since I'm attempting to query it in the form_open function (which takes place before the form actually opens) it was not being called, and that value was not known. Here's what it looks like now, and it does work correctly:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Exit_Form_Open_Click

With DoCmd
    .Hourglass True
    .SetWarnings False
    .OpenQuery "qry Mar Geology Data"
    .OpenQuery "qry Apr Geology Data"
    .OpenQuery "qry Graphing Query"
    .SetWarnings True
    .Hourglass False
End With

If DLookup("[Allowed Usernames]", "[tbl Database Admins]", _
      "[Allowed Usernames] = '" & Environ("username")&"'")=1 Then
msgbox("The username is within the Table, boxes are visible.")
   Me.Modify_Budget.Visible = True
   Me.Modify_Forecast.Visible = True
   Me.Modify_Calculation_Factors.Visible = True
   Me.Daily_Data_Entry.Visible = True
   Me.Email_Report.Visible = True
   Me.Email_Report_Text.Visible = True
   Me.Modify_Email_List.Visible = True
   Me.Database_Admin_List.Visible = True
Else
msgbox("The username is NOT within the Table, boxes are not visible.")
    Me.Modify_Budget.Visible = False
    Me.Modify_Forecast.Visible = False
    Me.Modify_Calculation_Factors.Visible = False
    Me.Daily_Data_Entry.Visible = False
    Me.Email_Report.Visible = False
    Me.Email_Report_Text.Visible = False
    Me.Modify_Email_List.Visible = False
    Me.Database_Admin_List.Visible = False
End If

Exit_Form_Open_Click:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub

End Sub