Microsoft Access User Level Security - VBA Codes / Macros

Please see my blog article on Logon Security with VBA (see blog address in Sig) It has instructions and sample code for creating a login form and securing your database.

If you are having a problem using sample code, please detail he error messages, the code you are using and what the problems are.

Hi Scott,

Thank you for responding and sending me the link to your blog, that's so helpful.

I'll follow the steps as best i can, i'm not too familiar with Microsoft Access terminology, would you mind if i message you if i get stuck?

The code i attempted was:

Private Sub Command1_Click()

If IsNull (Me.txtLoginID) Then

Msgbox "Please Enter LoginID", vb information, "LoginID Required"

Me.txtLoginID.SetFocus

ElseIf IsNull (Me.txtPassword) Then

MsgBox "Please enter password", vbinformation, "Password Required"

Me.txtPassword.SetFocus

Else

'process the job

If (IsNull (DLookup ("UserLogin", "tblUser", "UserLogin ='" & Me.txtLoginID.Value & "'"))) Or _(IsNull (DLookup ("password", "tblUser", "Password ='" & Me.txtPassword.Value &"'"))) Then

MsgBox "Incorrect LoginID or Password"

Else

'MsgBox "LoginID and Password correct"

Docmd.OpenForm "Navigation Form"

End If

End If

End Sub

The error message for the above was:

The expression on click you entered as the event property setting produced the following error: Invalid Outside Procedure

*The expression may not result in the name of a macro, the name of a user-defined function, or [event procedure]

*there may have been an error evaluating the function, event or a macro.

Thank you

Laura

Do you know what line of code is throwing the error? If not, I would got to the VBE and use Debug>compile and see if you can find what lines of code are causing problems.

But frankly, I would do it the way my blog article describes. Instead of having the user enter an Login ID, they choose it from a combobox. So only users who have been added to the Users table can log in. When they select their Login, the combobox stores the password and Access Level (your code doesn't account for Access level. So instead of using the Dlookup you just read the password from the combobox column. I would also hide the login form after validation. This way you can always retrieve the AccessLevel using the expression:

Forms!frmLogin!cboUser.Column(x)

where x is the column for the Access Level. 

Unfortunately, i have no idea where the error occurred, i dont really understand the components of VBA codes so i typed this one in following a tutorial which had a login form similar to the one i need to create.

Yes I will do, yours looks much easier to follow.

Thanks for your advice, i'll give it a go and keep you posted :)

Laura

Good Morning Scott,

I have followed your steps as best i can, i just have a couple of questions:

• When setting up tblUser, i didnt have the option to choose field type long integer, so i used the lookup wizard for AccessLevelID -> tbuAccessLevel, and the field type changed to number in design view - does this sound correct?

• "...using AccessLevel to restrict access to various parts of the application" - I cant seem to find the place to enter the column reference expression, please can you let me know where i can locate it? I'm using Access 2010. Also, which expression would you reccomend using? I'm unsure of the difference.

• I attempted to move ahead and enter Case 2 option to make a form read only for readers, and it rejected - am i right in thinking this is because i am yet to implement the column reference expression?

• As for our purpose, we only need an Admin user (Full access) and a Reader user (Read only), if i deleted the other usertypes, would the codes and expressions etc adjust accordingly / still function, or does your example require all fields mentioned to be included? (This wouldnt be a problem, i could happily leave them there and ignore them if need be).

• Can the format of passwords be changed to have an input mask displaying ****, or will this have a negative effect later on? I'm just thinking it would be helpful if users could type in their passwords directly to the table, without seeing their colleagues, or is it more benificial to be able to see the passwords as an Admin ie. if a reset is required?

• Do you have a blog further detailing the process of resetting passwords with this type of form for future reference? Someone is bound to forget theirs!

• Do you know how many users can enter the database as read only at one time, when it is saved as a desktop version on a shared server? It's a worrying thought that only crossed my mind this morning!

Ive saved a copy of your sample database, thats brilliant to have to hand! If only mine looked that pretty! Do you think, if i set up the same tables, forms and their properties in our database, it would be likely to work the same (as if by magic), i have a very basic understanding at this stage and i'm working towards a deadline to have the database up and running, if you can think of any shortcuts that would be awesome.

Thank you

Laura

1) Long Integer is a subtype. If you select the Datatype as Number, the default subtype (listed in the Size property on the properties box in the lower part of table Design mode) is Long Integer. I'm not a big fan of Lookup fields on the table level, but if you understand that its stroring the level # not the description you are OK.

2) Where you enter the expression is dependent on what you are using it for. For example, lets say you don't want readers to even open a maintenance form. In the On Open event you would use:

If Forms!frmLogin!cboUser.Column(3) = 4 Then

     MsgBox "You are not authorized to use this function!"

     DoCmd.close acForm, "formname", ac SaveNo

End If

Similarly, if you want a form to be read only you could use the following in the On Load event:

If Forms!frmLogin!cboUser.Column(3) = 4 Then

     Me.AllowEdits = False

     Me.AllowAdditions=False

     Me.AllowDeletions = False

End If

Note, I'm assuming cboUser has a RowSource of: SELECT UserID, Lastname & ", Firstname AS FullName, Password, AccessLevel FROM tblUsers, ORDER BY Lastname, Firstname;  that would make the AccessLevel Column 3. 

3) Depends on how and where you entered the code.

4) I would leave the Access level table as is. Who knows what you might need for the future. 

5) I use an input mask of all asterisks, not a problem

6) I have a password reset button on my users form. The code is Simple:

Me.txtpassword = "default password"

Me.chkReset = True

I then have code in the After Update event of the password text box like:

If Me.chkReset then

      DoCmd.OpenForm "frmPWChange"

      Me.chkReset = False

End if

this will force the user to change their password from the default. If that isn't in my blog, let me know and I'll get you the exact code.

7) The proper way to distribute a multi-user database is to split the database into a back end (tables) and a front end (everything else). The back end goes on a shared network drive, the front end on each user's local drive. You don't want to share the front end as that can cause corruption. Using that deployment model, you should have no problem with dozens of users. 

This blog http://www.accesssecurityblog.com/ has some other tips on security. 

Another useful function can be found here: http://allenbrowne.com/ser-56.html This function locks only the bound controls but leaves unbound controls (like Search boxes and buttons) useable. Makes it easy to set Read only but still allow searching and navigation.

Good Morning,

I think the VBA code is far too advanced for me, but ive had a brainwave, what do you think to this...

In the split form, i have changed the properties to "Split form datasheet - read only, Allow additions - No, Allow edits - No, Allow Filters - No".

Now the form is exactly as i want it to be for users, i cant add, edit or delete anything but i can view all the records on both the form in the top half, and the datasheet underneath.

Question is, can i keep it like this without a code? I understand you can hide the navigation pane/ribbon/toolbars, but can you lock them for changes all together? If i managed to stumble across this, a user could also, and change it back to allow edits, which is what i would like to prevent. 

If the navigation pane remains visable, can you freeze it/disable the buttons so that users cannot delete the entire table? or even prevent them from closing the form without also exiting Access all together? Assuming this is possible, how would you then restore editing as an administrator to maintain the data?

If there are options here, how does it work when splitting the database into front and back ends?

Can property settings be changed so that the front end form can be frozen as above and restricted with one general password.... and the back end be secured with another general password, but with editing enabled so that the data could be altered there, but feed through to the front end and display in the split form?

(Apologies for the idiot terms, i'm thinking outloud, i hope this makes sense)

Thank you Ken, that's really helpful i'll look into it :)