Help needed for Open Form OpenArg

[Joe Y]

I am planning to set user privilege based on the user’s group after they logon. I need helps to correct my code or approach.

I created a table named TUSERID that consist of three fields: UserID, UserName, Password, and Department.

The database open with logon form. After user select their username, enter password and click the cmdLogin button, the event code check the password and user department then open the Main_0 form.

Depending on the user’s department, I want to show/hide selected buttons in the Main_0 form.

I use the DoCmd.OpenForm line with cmdVendorRpt = True/False to control user’s privilege. cmdVendorRpt is one of the buttons that I want to control user's ability to see.

However, this does not work. The Main_0 form opened with all buttons shown no matter what the user department is.

Please help. Below is my code.

Thanks

Private Sub cmdLogin_Click()

Dim MyPassword As String
Dim MyGroup As String

'Check to see if data is entered into the UserName combo box

    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboEmployee.SetFocus
        Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

    MyPassword = DLookup("[Password]", "TUSERID", "[UserID]=[cboEmployee]")
    MyGroup = DLookup("[Department]", "TUSERID", "[UserID] = [cboEmployee]")
    
    If txtPassword = MyPassword Then
                   
        DoCmd.Close acForm, "F_Logon", acSaveNo
              
            If MyGroup = "Admin" Then
                DoCmd.OpenForm "F_Main_0", , , , , , cmdVendorRpt = True
                                               
            Else
                DoCmd.OpenForm "F_Main_0", , , , , , cmdVendorRpt = False
            
            End If
                
    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.txtPassword.SetFocus
    
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If


End Sub

[Seth Schrock]

The OpenArgs property doesn't allow you to make changes to the form directly. It only allows you to pass a value to the form. What you need to do is come up with a set of values (such as "Admin"/"Other", "True"/"False", 1/0, etc.) that you can test in F_Main_0's OnLoad event. Based on the value in the OpenArgs property of the form, you can then set your button's visible properties to true or false.

[Joe Y]

Thanks Seth. Can you provide more hint or code example?

Your answer also inspired me for the next question:

Once user entered ID and password, is there a way that their ID and department (group) become global variance? This way, other forms can easily use these variances for user privilege setting. If this can be done, how and where to set the global variance?

Thanks again.

[Seth Schrock]

Only one question per thread please. Just post your "next question" in a new thread and we will be glad to help.

Back to the original question, I will try to help you out in a way that will fit in with your next question. What would probably be simplest would be to just use MyGroup as the value for the OpenArgs property. That way you can get rid of the If/Then/Else statement. So your lines 32-38 can be replaced with

DoCmd.OpenForm FormName:="F_Main_0", OpenArgs:=MyGroup

Then in F_Main_0's OnLoad event, you would use a Select Case statement to test for the OpenArgs value.

Select Case Me.OpenArgs
    Case "Admin"
        cmdVendorRpt.Visible = True

    Case "[I]Other Group Name[/I]"
        cmdVendorRpt.Visible = False

End Select

The down side of doing things this way is that everything is hard coded for what each group can do and also what groups there are. If you want to add a group for some reason down the road, you need to edit your code to look for a new group name. The up side is that it is probably the simplest method to code.

[Joe Y]

Now I understand the concept of passing value using OpenArg. Thanks!

You mentioned a different way to make it easier down the road when new groups are added without needs of hard coding. Could you let me know the approach or articles that discuss this subject?

I will post a new thread regarding global variance.

Thanks,
Joe

[Seth Schrock]

Glad to hear it. Our goal is to help you in a way that helps you learn the topic and not just how to fix a specific problem. That way you can use your knowledge later on.

Glad to be able to help.