|
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 |
