We have had several requests for information on application code in MS Access and we thought you’d like to see some examples of ‘how did they do that’. Look for coding snippits in these pages as we share some coding ‘secrets’.

 

Password Protection for MS Access applications.

 

This add-on allows password protection to be applied to your Microsoft (R) Access program. It is available as a free download (as an MS Access MDB file in Access 2000 format) from NBI internet by clicking here.

 

 

When the application opens, it calls form Start1, requiring the user to input a user name and password. The user is allowed three tries to successfully enter a user ID and password. On successful entry, the form FRM_Menu opens. This form can be replaced with your own application menu. If the user does not successfully enter a user/password combination in three tries, the application exits.

 

Login attempt log file

 

 

Password entries are logged into an audit file. This file can be viewed by opening the form FRM_UserLog. Successful and unsuccessful attempts to log in are tracked in the access log.

 

Login Access Security Levels

 

 

User access levels are supported in this system. In the above example, two levels (User and Admin) are used. On the form FRM_Menu, two fields are available for entry. The first field requires Admin level access to edit, while the 2nd field is available to all users.

 

 

This strategy can be applied to all forms in your document to restrict access to forms based on security level. To change the field access on your forms, revise the following code in the ‘on load’ control of your form:

 

MyLevel = LookupSecRight(‘User’)

If MyLevel = ‘Admin’ Then

Me.Text21.Locked = False

Else

Me.Text21.Locked = True

End If

 

Default field entry by Login Name

 

Fields can be automatically loaded with the logged in user name by using the ‘on load’ control of your form as follows:

 

Me.Text21 = LookupaControl(‘user’)

 

By making this field locked, the user will not be able to change the user name. To do this, open the form in design view, select the desired field, double click to open the properties dialogue box, and select the data tab. Find the form control named ‘locked’ and change the value to ‘Yes’.

 

Customizing Your Application Forms

 

There are 4 tables included in this application. The 1st table (TBL_custom) allows customization of forms across your application. Use this feature if you want to easily be able to change header information across your entire application. In this application, the controls companyname, owner, companyapp, apptitle, and companylocation are all set by a lookup table and can be used on any form or report in your application.

 

 

Use the command:

 

=LookupControl(‘companyname’)

 

as the control source for your text box.

 

The 2nd table (TBL_custom1) stores the current logged in user name. The command = LookupaControl(‘user’) allows you to retrieve this data from the table.

 

The 3rd table is the user log, storing information about logged in users. The 4th table is the authorized user list. Note the difference between the values in the table TBL_Users and the form FRM_Users. The FRM_Users does not display passwords and does not show Admin as a user in the list. Using this method, default passwords for admin access can be programmed into your application without displaying them in the user listing. To view the filter criteria, look at the Record Source query in the FRM_users property.

 

The module Controls allows the easy lookup of table information.

 

Of course, to put this application into use, best practice would be to create an MDE file for running so that no-one can change your forms and disable the special access keys and do not display the database window from the start menu to control access to the tables. If you want code to disable the shift key on entry, contact us.

 

Hopefully this application gives you some insight into creative methods of customizing your MS Access applications and stimulates some innovative approaches to solving application challenges. If you find this article useful, send me a note and let me know how you utilized it in your application. Contact us for custom application development … happy coding!

 

Norm Bain

NBI internet

http://www.nbiinternet.ca

email