We have seen how to define a Pop up Form and how it can be made to appear on a particular position on the Application window, when it is opened. We have also seen that the Form behavior is controlled by setting or modifying the Property values of the Form. In those cases we have changed the Form’s Pop up, Border Style and Auto Center Property Values manually during design time.

Here, we will try something different and will attempt to change the Form opening Modes (Data Entry Mode or Edit/Search Mode) at run time 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 Users 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 not allowed to add new records. Data Entry User can also edit data that he/she keys-in during the current data entry session.

The Open Mode change can be done by checking the Security User Account or Group Account name of the Current User and by changing the Data Entry, Allow Additions Property Values.

We need a small VBA program to check the Current User’s Group Account information to determine whether he/she belongs to the DATAENTRY Group Account or not (the User Workgroup name DATAENTRY is used here as an example only). 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
If chkFlag Then
    frm.DataEntry = True
    frm.AllowAdditions = True
    frm.DataEntry = False
    frm.AllowAdditions = False
End If

Exit Function

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() EventProcedure 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

A security User Account may belong to one or more security Group Accounts besides the default Users Group Account, which all the Users belong. Therefore, we need to check through the User’s Security Group Account list, whether he/she belongs to the DataEntry Group or not and if found then change the Form’s open Mode accordingly.

Note: This method to work, the Database must be secured by implementing MS-Access Security and assume that the Users are grouped under different Work Groups like Data Entry Group, Team Leaders Groupb or Managers Group and so on. To learn more about Microsoft Access Security visit the pages under Security 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 log-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 UserID or Password. In that case you can try this procedure by passing the Admins or Users work group name as parameter to the above Program.

But, if all Users need both features inter-changeably at their will then this can be implemented with the use of a Command Button on the Form. Since, this procedure is manually controlled, requirement of checking the Security User Account or Group Accounts doesn't arise.

All we need is a Command Button at the Footer of the Form, set with specific Caption Value which can be checked and changed on every Click on the Command Button and switch the Form Mode to Data Entry or Normal.

Assume that the Form 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 as Data Entry indicating that if the User wishes to change the Form into Data Entry Mode then she may click on the Command Button. Since, the same Command Button click is needed to switch the Form into one of these two Modes we must check the Caption of the Command Button to determine what is the User's intention. She may click on the Command Button repeatedly too.

On every click we must check the Caption Value and change the Mode of the Form as well as the Caption of the Command Button to Edit/Search or Data Entry interchangeably.

We can implement this with 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
     Me.cmdEdit.Caption = "Data Entry"
     Me.DataEntry = False
     Me.AllowAdditions = False
End If
End Sub

The above technique we have used for a different function on a Form and you may take a look at that Article with the Title: Double Action Command Button.

StumbleUpon Toolbar