Change Form Modes on User Profile ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

We have already learned how to define a Pop-up Form and control its on-screen position when it opens. We also saw how a Form’s behavior can be managed by setting or modifying its property values—such as Pop-up, Border Style, or Auto Center—during design time.

Now, we will try something different and try to change the Form opening Mode (Data Entry Mode or Edit/Search Mode) at runtime automatically based on the identity of the Current User of the Form. When the Database is shared on a Network, the same Form can be opened by different Users, and the Form should behave differently for each User or User Group.

For Example, a single Form can be used for Data Entry by one group of users, who are allowed only to key in new records, and others are allowed to view, edit, and search, but are not allowed to add new records. Data Entry Users can also edit data during the current data entry session.

This distinction in Form behavior can be implemented by checking the current user’s security account or group membership and adjusting the Form’s Data Entry and Allow Additions property values accordingly at runtime.

Checking the Current User's credentials

We need a small VBA program to check the Current User's Group Account information to determine whether the user belongs to the DATAENTRY Group Account or not (the User Workgroup name DATAENTRY is used only as an example). Accordingly, the program will set the Mode of the Form for the current session.

The VBA Routine is given below:

Public Function ChangeFormMode(ByVal strFormName As String, ByVal UserGroupName As String)
'----------------------------------------------
'Author : a.p.r. pillai
'Date   : Feb-2009
'All Right Reserved by www.msaccesstips.com
'----------------------------------------------
Dim wsp As Workspace, strUser As String
Dim j As Integer, frm As Form
Dim chkFlag As Boolean, GrpCount As Integer

On Error GoTo ChangeFormMode_Err

Set frm = Forms(strFormName)
strUser = CurrentUser
Set wsp = DBEngine.Workspaces(0)
GrpCount = wsp.Users(strUser).Groups.Count
'checks whether 'DATAENTRY' Group Name is present
'within the User's Group Account Names.
chkFlag = False
For j = 0 To GrpCount - 1
    If wsp.Users(strUser).Groups(j).Name = UserGroupName Then
        chkFlag = True
        Exit For
    End If
Next
If chkFlag Then
    frm.DataEntry = True
    frm.AllowAdditions = True
Else
    frm.DataEntry = False
    frm.AllowAdditions = False
End If

ChangeFormMode_Exit:
Exit Function

ChangeFormMode_Err:
MsgBox Err.Description, , "ChangeFormMode"
Resume ChangeFormMode_Exit
End Function

Copy and paste the above program into a Global Module (Standard Module) in your database and save it. The program is called from the Form_Load() Event Procedure of the Form with the Form Name and the User Group Account name as parameters, as follows:

Private Sub Form_Load()
   ChangeFormMode Me.Name, "DataEntry"
End Sub

About Security Accounts

A security User Account can belong to one or more Security Group Accounts, in addition to the default Users group to which all users automatically belong. Therefore, when determining how a Form should open, we must check the Security Groups to ensure that the current user belongs to. If the user belongs to the Data Entry Group, the Form’s open mode can be changed accordingly.

Note: For this method to work, the database must be secured by implementing Microsoft Access User-Level Security. It is assumed that users are organized into different workgroups, such as Data Entry, Team Leaders, or Managers, each with specific permission settings. For more information on setting up and managing Access Security, refer to the Security section in the main menu.

If your machine is not configured to use a common MS-Access Work Group Information file or if you have not implemented MS-Access Security using the local Work Group Information File, then you are automatically logged in using the default User Account Admin, a member of the Admins and Users Group. This is happening silently, and you are not asked to enter a User ID or Password. In that case, you can try this procedure with the Admins or Users' workgroup name as a parameter to the above Program.

Form View Mode Changing Manually

However, if all users need access to both features—Data Entry and Edit/Search—interchangeably at their discretion, this can be implemented using a Command Button on the Form. Since this method is manually controlled by the user, there is no need to check the Security User Account or Group Account details.

All we need is a Command Button placed in the Form Footer, with a specific Caption value that can be checked and toggled each time the button is clicked. This button will switch the Form mode between Data Entry and Normal.

Assume that the Form initially opens in Edit/Search Mode by setting the following Property Values at design time:

Data Entry = False
Allow Additions = False

The Caption of the Command Button will be set to Data Entry, indicating that if the user wishes to switch the Form into Data Entry Mode, she can simply click the button. Since the same Command Button is used to toggle between the two modes, we must check its Caption value each time it is clicked to determine the user’s intention. The user may click the button repeatedly to switch between modes.

With every click, the program should verify the current Caption value, change the Form’s Mode accordingly, and then update the Command Button Caption to Edit/Search or Data Entry alternately.

Form Mode change with VBA

We can implement this in a few lines of Code on the Form's Class Module itself. The Command Button's Name Property Value is cmdEdit.

Private Sub cmdEdit_Click()
If Me.cmdEdit.Caption = "Data Entry" Then
     Me.cmdEdit.Caption = "Edit/Search"
     Me.DataEntry = True
     Me.AllowAdditions = True
 Else
     Me.cmdEdit.Caption = "Data Entry"
     Me.DataEntry = False
     Me.AllowAdditions = False
End If
End Sub

The above technique is for a different function on a Form, and you can visit the article titled: Double Action Command Button.