Introduction
When opening a Report or Form, you can pass several optional parameters as run-time arguments. These arguments help control the behavior of the report or form, such as filtering the output or changing the form’s open mode depending on the user’s profile.
If the current user belongs to a specific User Group, and that group has only read-only privileges, then the Form can be opened automatically in Read-Only Mode.
If the user does not belong to that group, the form can open in Normal Mode.
The following VBA function, CheckGroup(), checks whether the current user belongs to a specified User Group. Copy the code for this function into a Standard Module.
The CheckGroup() Function.
Public Function CheckGroup(ByVal strUsr As String, grpName As String) As String
'-----------------------------------------------------
'Author : a.p.r. pillai
'Date : Feb-2010
'URL : www.msaccesstips.com
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
Dim wsp As Workspace
Dim GrpArray() As Variant, grpcnt As Integer
Dim GrpOut As Variant, j As Integer
Set wsp = DBEngine.Workspaces(0)
grpcnt = wsp.Users(strUsr).Groups.Count - 1
ReDim GrpArray(0 To grpcnt) As Variant
'User may belong to more than one User Group
'Create an Array of Group Names
For j = 0 To grpcnt
GrpArray(j) = wsp.Users(strUsr).Groups(j).Name
Next
'Compare Admins with the Array List
'if matches then 'Admins' will be output in grpout Array
GrpOut = Filter(GrpArray(), grpName, True)
CheckGroup = GrpOut(0)
End Function
The CheckGroup() function should be called from a Command Button’s Click event procedure (we will implement it shortly). This function checks the current user’s group and returns the User Group name, which can then be used to open the form in a specific mode.
Private Sub cmdOpenForm_Click()
Dim strGrp
strGrp = CheckGroup(CurrentUser, "Admins")
If strGrp = "Admins" Then
DoCmd.OpenForm "Products", acNormal, , , acFormReadOnly
Else
DoCmd.OpenForm "Products", acNormal
End If
End SubThe CheckGroup() program creates a list of workgroups, checks whether the current user belongs to the Admins Group, and returns the result. If the result is Admins, the Products Form opens in Read-Only Mode; otherwise, it opens in Normal Mode.
Creating the Workgroups array is necessary because a single user can belong to multiple workgroups, such as Admins, Users (default), Supervisor, Manager, Editor, or any other group defined in the Workgroup Information File (.mdw).
The Filter() function searches the array for the text "Admins". If it is found, the value is stored in the GrpOut(0) element.
We cannot use the Filter() function inside a form module subroutine, because it conflicts with the form’s Filter property.
Regarding the Open arguments for forms and reports, you can pass the name of a query as a Filter argument or a WHERE condition (without including the word WHERE).
In addition, there is another parameter called OpenArgs, which allows you to pass a value to a form or report. You can then read this value in the class module of the form or report using the same OpenArgs variable, and use it for any purpose for which it was passed.
OpnArgs Example
We try out a simple example to learn the usage of a OpnArgs parameter. We need a few objects from the C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.
Import the following from the Northwind.mdb sample database:
- Table: Products
- Table: Categories
- Query: Products by Category
- Report: Products by Category
Open a new Form and create a Combo Box with the Category Name from the Categories Table.
A sample Form image is given below for reference.
Select the Combo Box and display its Property Sheet (View -> Properties or press Alt+Enter).
Change the Name Property value to cboCategory.
Create a Command Button and change its Name Property value to cmdOpen and the Caption Property Value to Open Report.
Select Event Procedure in the On Click Event Property and click on the build (...) Button to open the Class Module of the Form with the empty Sub-Routine lines.
Copy and paste the following VBA Code overwriting the existing line, or copy and paste the middle line alone:
Private Sub cmdOPen_Click() DoCmd.OpenReport "Products by Category", acViewPreview, , , , Nz(Me!cboCategory, "") End Sub
Save the Form with the name Open Argument Demo or any other name you prefer.
Modify the Report Module
Open Products by Category Report in Design View.
Display the Class Module (View - -> Code)
Copy and Paste the following Code into the Class Module:
Private Sub Report_Open(Cancel As Integer) Dim strFilter As String If IsNull([OpenArgs]) Then Exit Sub End If Report.Title.Caption = Report.Title.Caption & " (" & [OpenArgs] & ")" strFilter = "CategoryName = '" & [OpenArgs] & "'" Report.Filter = strFilter Report.FilterOn = True End SubSave the Report with the Code.
Open the Open Argument Demo Form in Normal View.
Select a Product Category Name (say Beverages) in the Combo Box.
Click on the Open Report Command Button.
The Products by Category Report will open with only Beverage items on the Report, and the heading label is modified to show the Product category Name.
Now, delete the Combo box's current value and click the Open Report Command Button.
This time, all products by category will appear on the report, and the heading will remain unchanged.
In the Report_Open event procedure, we check whether the OpenArgs variable contains a value. If it is Null, the subroutine terminates immediately.
Trial Run of First Two Programs.
Do the following to try the first two Programs given at the top of this page:
Open the Open Argument Demo Form in Design View.
Create a second Command Button on the Form.
Display the Property Sheet of the Command Button (View -> Properties).
Change the Name Property value to cmdOpenForm, and the Caption Property Value to Open Form.
Display the Class Module (View -> Code).
Copy and paste the second Program from the top into the Module and save the Open Argument Demo Form.
Create a Tabular type Form for the Products Table and save the Form as Products.
Open the Open Argument Demo Form and click on the Open Form Command Button.
If you have not implemented Microsoft Access Security, you are by default the Admin User, a member of the Admins Group, and the Products Form will open in Read-Only mode.
Terrific work! This is the type of information that should be shared around the web. Shame on the search engines for not positioning this post higher!
ReplyDeleteSometimes none of the answers get it just right. If so, pick "No Best Answer". Voters DO NOT get any points for voting on the No Best Answer.
ReplyDeleteHi, thanks for very usefull information
ReplyDelete