Microsoft Access Tips for Serious Users

Provided by allenbrowne.com, May 2005

Field-level Permissions in Microsoft Access

Access security sets permissions per table. It cannot allow some fields to be updated and not others in the same table. There are many scenarios where it would be very handy to lock some controls to prevent accidental alterations, while still permitting changes to others.

You can do that quite simply, with a custom property on each field. Assuming all updates are performed through forms, the Current event of the form then locks the fields based on this property.

While there are many possible implementations of this idea, the sample database (40kb, Access 2000 and later) demonstrates these options:

• Allow anyone to update the field any time.
• Allow the field to be updated any time, but only by the user who created the record (e.g. for user notes.)
• Allow the field to be updated by anyone, but only within the first hour after it is created (to correct errors on entry.)
• Allow the field to be updated only by the user who created it, and only in the first hour (combination of previous two.)
• Allow no-one to update the field once the record is saved (legally or financially important data.)

Access does not record who saved the record or when, so the sample database demonstrates those techniques as well. In practice, this is also very useful for distinguishing user errors from flaws with your application.

The elegance of this solution is that the custom property gives you a single maintenance point. It is transparent to any interface that is unaware of the property, but it takes just one line of code in any form to implement the record-stamping, and one line for the field-locking.

Limitations

The sample database has these limitations:

• The field-level permissions are applied in the Current event of the form. They are not true user-level security permissions, so you must not allow users to access the tables or queries directly.
• Works with local tables and attached Access (JET) tables only, not ODBC sources.
• Works in Access 2000 and later versions only.

Using the sample database

The sample contains two forms: frmAttribUser is the interface for setting the field permissions, and MyForm as a demonstration. To test:

1. Unzip and open the sample database. frmAttribUser loads.
2. In the combo, select the table whose fields you wish to set: MyTable. The field names are loaded, and the check boxes initialized for the current permissions.
3. Change the permissions for some fields, and click the Commit Changes button.
4. Close the form, and open MyForm. This form is based on MyTable, so the controls are locked based on the permissions you set.
5. At a new record, all fields are unlocked. At an existing record, controls are locked based on your permission settings, and have a red border.

Copying to your database

To use this in your own database:

1. Open your database, and import the table tzAttribUser, the form frmAttribUser, and the module ajbFieldLevel, with:
       File | Get External | Import.
2. Add four fields to each table where you will use this, to record who created and last updated the record, and when. You can do this quickly by opening the sample table (MyTable) in design view, selecting the four fields (EnteredOn, EnteredBy, UpdatedOn, and UpdatedBy), copying (Ctrl+C), and pasting (Ctrl+V) into your table.
3. Open your form in design view, and add the line to the form's BeforeUpdate event procedure, and the line to its Current event. The code will end up like this:

       Private Sub Form_BeforeUpdate(Cancel As Integer)
           Call StampRecord(Me, False)
       End Sub
       Private Sub Form_Current()
           Call LockControls(Me, True)
       End Sub

4. Repeat for your other forms.

If you do not want the code to change the border of your controls, change the second argument in Form_Current to False. You may find that the border color is not set correctly in some versions of Access if a control has a Special Effect (such as Eteched.)

If you want to be able to mark old records inactive and record who marked them in active and when, you can also add three more fields to your table: Inactive (Yes/No), InactiveOn (Date/Time), InactiveBy (Text.) Then change the second argument for StampRecord() to True so it stamps these too.

If you have controls that should be treated as an exception for this form and NOT locked/unlocked regardless of their custom property setting, list them as optional arguments. For example, to not lock controls name LeaveMeAlone and MeToo, use:
    Call LockControls(Me, True, "LeaveMeAlone", "MeToo")

That's it, unless you want to delve into what the code actually does.

How it works

Setting Permissions

In form frmAttribUser, the combo cboTable has a RowSource that shows the tables in this database. It omits system tables and deleted tables, showing only those that have Flags set as local (flag=0) or attached Access tables (flag=2097152). It also skips our temporary table (tzAttribUser.) You can exclude further tables by adding them to the WHERE clause of the SQL statement in the combo's RowSource.

When you select a table in the combo, its AfterUpdate event procedure loops through the fields of the table. It skips the AutoNumber field, any system fields, and our special fields for recording who created and updated the record and when (EnteredOn, EnteredBy, UpdatedOn, UpdatedBy, InactiveOn, and InactiveBy.) For the remaining fields, it writes their name to the temporary table tzAttribUser, and sets the 6 Yes/No fields according to the value of the custom property.

The Commit Changes button is enabled when you make a change. Clicking this button saves the current record, and assigns a TableDef variable to the chosen table. Testing the Connect property of the TableDef, we discover if this is an attached table. If so, we check that it is the default type, and parse the file name from the string. It then opens that database directly, gets the name of the source table, and reassigns the TableDef variable to the table in the foreign database so any property is created there and not merely on the link.

It then loops through the fields chosen from the table, comparing the current and desired settings. If they are different, it calls the function SetPropertyDAO(), which creates the custom property if necessary and assigns the desired value. It then reports the results, disables the Commit Changes button, and clears out the temporary table so the user can work on another.

Stamping the record

Once the properties have been set, there are just two lines of code to add to your forms to get this to work. Form_BeforeUpdate() calls StampRecord() which records the user name and  date/time the record was created (new record) or modified (existing record.) If you need to be able to store historical records in your table but mark them inactive, you can also add another three fields to your table: Inactive (Yes/No), InactiveOn (Date/Time), and InactiveBy (Text.) The code will then examine whether there has been a change to the Inactive box, and record the user name and date/time the record was marked inactive. To use this functionality, use True as the second argument for StampRecord().

Locking the controls

Your form's Current event calls LockControls(). This function calls GetUserAndDate4Record(), which initializes strEnteredBy with the user who created the record, and dtEnteredOn with the date when the record was created. It then loops through all controls on the form, skipping irrelevant types (command buttons etc) and any that have no Locked property. It also skips the specially named fields (listed above), and any extra names you passed in as optional arguments. If the form is at a new record, it then sets a flag (bAllow) to allow changes, because it is only existing records that get locked.

Unless the control is skipped or being allowed, we call GetAttribUser() to find out what the custom property is for the source field for this control. This function calls Bound2Field() to learn the name of the source field, ignoring unbound controls and those bound to an expression (starts with "=".) Now we know the name of the source field, we can read the custom property from that field in the form's RecordsetClone. If the field has the property, it returns the value; if not found it returns zero (no blocking is set.) If something goes wrong, it returns -1 as an error flag. If LockControls() receives the error flag, it skips over this control. If the control is not skipped, it locks the field if the Block All value is chosen (1), and compares the user name and/or date/time if the other values are chosen.

Finally, it modifies the border color of the control if the second argument for LockedControls() was True.

Customization

To change the border color that indicates a control is locked, alter the numbers for the constants conBorderNormal (not locked) and conBorderLocked (color to use when locked) at the top of the ajbFieldLevel module.

As an unsecured database, the Access function CurrentUser() would return "Admin" for everyone. The sample therefore uses an API call to get the Windows User Name. Replace each call to NetworkUserName() with CurrentUser() if you prefer.

To use a real error logger, replace the LogError() function with this one.

The custom property is defined as a Long, so you have 30 bits to work with if you want to define more than just the three options used in this example (reserving 0 for none and -1 for error.) Replace the uFieldProp enumeration values with your own choices, and change the tzAttribUser table and frmAttribUser form to handle the new settings. The modify the LockControls() function to handle all the aspects of your enumeration appropriately.