none
Password on Command Button in Access 2007 RRS feed

  • Question

  • I cannot get a password to work. The Query Name is 'Master Membership Query'.  The Macro name is 'Master Membership Query Macro'.  This is what I have thus far:...Thanks

    Private Sub cmdMaster Membership Query_Click()

    'Attached to On Click event of cmdMaster Membership Query

        Dim strPasswd

        strPasswd = InputBox("Enter Password", "Restricted Form")

        'Check to see if there is any entry made to input box, or if
        'cancel button is pressed. If no entry made then exit sub.

        If strPasswd = "" Or strPasswd = Empty Then
            MsgBox "No Input Provided", vbInformation, "Required Data"
            Exit Sub
        End If

        'If correct password is entered open Master Membership Query
        'If incorrect password entered give message and exit sub

        If strPasswd = "Graham" Then
            DoCmd795.OpenQuery "Master Membership Query", acNormal
        Else
            MsgBox "Sorry, you do not have access to this form", _
                   vbOKOnly, "Important Information"
            Exit Sub
        End If
    End Sub


    Private Sub Command795_Click()

    End Sub

    Sunday, February 21, 2016 9:50 PM

Answers

  • I'm not sure where the macro comes into play.  What you have posted is an attempt at a VBA procedure intended to be executed as the On Click event property of a button, but it contains invalid code.  

    Firstly the procedure name contains a space.  In a procedure name a space should be represented by an underscore character.  If the procedure had been built via the On Click event property of a button whose name included a space the space would have been replaced automatically by an underscore character.

    Secondly your reference to 'DoCmd795' suggests that you are confusing the DoCmd object with a command button as you appear to have a command button named Cmd795.  The OpenQuery method is a method of the DoCmd object, so the line would be:

        DoCmd.OpenQuery "Master Membership Query", acViewNormal  

    However, rather than opening the query I'd suggest you open a form bound to the query.  You can then passport the form, and open it simply by calling the OpenForm method.  The password functionality is contained in the form's module.  The form can be in datasheet view if course, if so wished.

    You'll find an example as PasswordForm.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how to password the form by creating a custom property of the form, and includes a form for setting or changing the password(s) of one or more forms.

    Ken Sheridan, Stafford, England


    Sunday, February 21, 2016 11:43 PM
  • 1.  Do you have a button named 'CmdMaster_Membership_Query' (possibly with a space or spaces in the button name)?

    2.  You should always declare a variable as the relevant data type, i.e.

        Dim strPasswd As String

    Otherwise in will be a Variant, and will be Null rather than a zero length string when you later test for the latter with If strPasswd = "" (there's no need to test for empty).

    3.  Do you really want to hard-code the password in the code rather than storing it where it can be referenced?  My demo does so by storing it as a custom property of a form, but a simpler, though more accessible to 'meddlers', solution would be as a value in a table.  You can then build in a mechanism to set or change passwords.

    5.  Assuming the answer to 1 is yes, the Command795 button and its Command795_Click sub-procedure  are unnecessary.

    6.  Bear in mind that this is a very rudimentary form of 'security' and easily subverted.  For more robust solutions see Tom van Stiphout's blog at http://www.accesssecurityblog.com

    Ken Sheridan, Stafford, England

    Monday, February 22, 2016 12:39 AM

All replies

  • I'm not sure where the macro comes into play.  What you have posted is an attempt at a VBA procedure intended to be executed as the On Click event property of a button, but it contains invalid code.  

    Firstly the procedure name contains a space.  In a procedure name a space should be represented by an underscore character.  If the procedure had been built via the On Click event property of a button whose name included a space the space would have been replaced automatically by an underscore character.

    Secondly your reference to 'DoCmd795' suggests that you are confusing the DoCmd object with a command button as you appear to have a command button named Cmd795.  The OpenQuery method is a method of the DoCmd object, so the line would be:

        DoCmd.OpenQuery "Master Membership Query", acViewNormal  

    However, rather than opening the query I'd suggest you open a form bound to the query.  You can then passport the form, and open it simply by calling the OpenForm method.  The password functionality is contained in the form's module.  The form can be in datasheet view if course, if so wished.

    You'll find an example as PasswordForm.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how to password the form by creating a custom property of the form, and includes a form for setting or changing the password(s) of one or more forms.

    Ken Sheridan, Stafford, England


    Sunday, February 21, 2016 11:43 PM
  • Thank You...I am new at this Code.  Does this look better?  I am still trying to understand.

    Private Sub CmdMaster_Membership_Query_Click()

    'Attached to On Click event of CmdMaster_Membership_Query

        Dim strPasswd

        strPasswd = InputBox("Enter Password", "Restricted Form")

        'Check to see if there is any entry made to input box, or if
        'cancel button is pressed. If no entry made then exit sub.

        If strPasswd = "" Or strPasswd = Empty Then
            MsgBox "No Input Provided", vbInformation, "Required Data"
            Exit Sub
        End If

        'If correct password is entered open Master_Membership_Query
        'If incorrect password entered give message and exit sub

        If strPasswd = "Graham" Then
            DoCmd.OpenQuery "Master_Membership_Query", acViewNormal
        Else
            MsgBox "Sorry, you do not have access to this form", _
                   vbOKOnly, "Important Information"
            Exit Sub
        End If
    End Sub


    Private Sub Command795_Click()

    End Sub

    Monday, February 22, 2016 12:08 AM
  • 1.  Do you have a button named 'CmdMaster_Membership_Query' (possibly with a space or spaces in the button name)?

    2.  You should always declare a variable as the relevant data type, i.e.

        Dim strPasswd As String

    Otherwise in will be a Variant, and will be Null rather than a zero length string when you later test for the latter with If strPasswd = "" (there's no need to test for empty).

    3.  Do you really want to hard-code the password in the code rather than storing it where it can be referenced?  My demo does so by storing it as a custom property of a form, but a simpler, though more accessible to 'meddlers', solution would be as a value in a table.  You can then build in a mechanism to set or change passwords.

    5.  Assuming the answer to 1 is yes, the Command795 button and its Command795_Click sub-procedure  are unnecessary.

    6.  Bear in mind that this is a very rudimentary form of 'security' and easily subverted.  For more robust solutions see Tom van Stiphout's blog at http://www.accesssecurityblog.com

    Ken Sheridan, Stafford, England

    Monday, February 22, 2016 12:39 AM