Macro For Password Protect Switchboard

[kawprice60]

I want to password protect the menu where you can edit or delete data on a switchboard. I have a switchboard with Reports, Forms - Add Data, and Forms - Edit or Delete Data. How can I put a password to access the edit or delete data?

I am guessing that a macro is in order but I am not sure how to get started.

Thanks!

 

[moishy]

There are multiple solutions, depending upon how robust your solution must be.

For a simple (and not so secure) way of doing it you would do the following:

Private Sub cmdEditMenu_Click()

    Dim strDocName As String 
    Dim strLinkCriteria As String 
    Dim strPassWord

    strPassWord = InputBox("Please Type Your Password")

    If strPassWord = "password" Then
       strDocName = "NameOfForm"
       DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
       MsgBox "The password provided was incorrect - permission denied"
    End If

End Sub

Note: above code is to be placed on the on click event of a command button, instead you may place it in the on open event of the menu editing form by replacing the first line with this: Private Sub Form_Open()


A more robust and secure option (although by no means fail proof) is outlined by Microsoft here.

 

[fiona88]

Hi Moishy, I followed the instruction which the link was mentioned in last email to create password protected form in my Access. The problem I found out is that the password protected form still open up if no password entered in the frmPassword or simply close the frmPassword. I am fairly new with Access, I guess a macro needed to work on this. Please help me out!

 

[moishy]

I've successfully used that before, if you post a sample of your db I'll take a look.

 

[fiona88]

Thanks for your quick reply, Moishy. I use PASSWORD to get into the frm_WagesCost form. Please see my attahced. Thanks!

 

[moishy]

Here is an enhanced version, which prompts for the password using a masked inputbox, if the password is wrong the inputbox will prompt the user for the password until the right password is provided, clicking on cancel will shutdown the db.

 

[fiona88]

Thanks Moishy. When directly open the form in Design View and change to form view, the Password window comes out, the problem is the content of the form shows up as well.

 

[moishy]

How is that a problem?

When a form is opened it is in form view.
If you are concerned that unauthorized users will use it as a backdoor, you can can go to Access Options and disable the menus, make sure to display your switchboard form on open. This way your users cannot get into the design view - unless they use the shift bypass key but this can be prevented too.

 

[fiona88]

Thanks, Moishy. users have different level limitation when viewing objects. so I want stop them from opening object in design view. How to prevent the shift bypass key? I know macro is in order to do this in Access 2003, but I don't know how to do in Access 2010?

 

[moishy]

To prevent the shift bypass copy the following code into a new module (naming it whatever you like).

Option Explicit

Function ChangeProperty(strPropName As String, _
                        varPropType As Variant, _
                        varPropValue As Variant) As Integer

    Dim dbs As Object, prp As Variant
    Const conPropNotFoundError = 3270

    Set dbs = CurrentDb
    On Error GoTo Change_Err
    dbs.Properties(strPropName) = varPropValue
    ChangeProperty = True

Change_Bye:
    Exit Function

Change_Err:
    If Err = conPropNotFoundError Then    ' Property not found.
        Set prp = dbs.CreateProperty(strPropName, _
                                     varPropType, varPropValue)
        dbs.Properties.Append prp
        Resume Next
    Else
        ' Unknown error.
        ChangeProperty = False
        Resume Change_Bye
    End If
    
End Function

Add the following code to your startup form.

Sub SetBypassProperty()
    Const DB_Boolean As Long = 1
    ChangeProperty "AllowBypassKey", DB_Boolean, False
    ChangeProperty "AllowBypassKey", DB_Boolean, True
End Sub

And finally to the on open event of your startup form add the following line.

ChangeProperty "AllowBypassKey", DB_Boolean, False

NOTES:
1. It is highly recommended to add a backdoor so the developer can get back in.
To do that add a button or label set its hidden property to true, and add the following line:
ChangeProperty "AllowBypassKey", DB_Boolean, True
The next time the db is opened holding the sift key will bypass all startup options.
p.s. you may want to add a prompt for a password changing the AllowBypassKey property to true, but I'll leave that for you to figure out, it really is simple.

2. Even all these measures won't hermetically seal your db, they will only prevent not savvy users from getting in.

 

[fiona88]

Thanks, Moishy, your code is so good! for your P.S, I haven't tried it, I let you know later.

 

[davisbroen21]

The problem I found out is that the password protected form still open up if no password entered in the frmPassword or simply close the frmPassword. I am fairly new with Access, I guess a macro needed to work on this. Please help me out!

----------------------------
the seven deadly sins

 

[moishy]

See post #6 and #10.

 

[mlb5]

I tried the code to make a password. I am trying to understand code better. I really appreciate the help. The code worked very well. I have gone through the code to see how to change the password from what the code set it as. I cannot figure it out. Can I get some help as to how to change the password?

 

[moishy]

Please open a new thread.

 

[mlb5]

I'm new to forums. I've been trying to figure out how to post a new thread. I've been looking for a link to post a new thread. If you wouldn't mind, would you point me in the right direction as to how to open a new thread? Sorry for the newbie problem.

 

[moishy]

not sure what the problem is. you just opened a new thread with this question.

 

[mlb5]

As usual, I figured it out after I asked the question. Thanks. I don't suppose you could check out that thread and see if you have a possible answer?

 

[moishy]

See this: http://support.microsoft.com/kb/209871

 

[mlb5]

I really appreciate your help. That's the code I used. It's very good. I also figured out how to stop it from allowing access if it was "X" out. But the problem I'm having is that this code makes the password, "PASSWORD." I would like to change that to something else. I've looked through the code, but can't figure out how to change it to something else.