Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Form and Report Open Arguments

Introduction

While opening a Report or Form we can pass several Optional Values as Run-Time Arguments to control the output on the Report or to change the Form Open Mode depending on the User's Profile.

For example: If the Current User belongs to a particular Users Group, in the Security Workgroups, who has only enough privilege to view the data and not to Add or Edit, then we can open the Form in Read-only mode otherwise in Normal Mode when the Current User attempts to open the Form.

The following code checks whether the Current User belongs to a particular User Group. The Code of CheckGroup() Function given below must be copied into a Standard Module in your Database.

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 must be called from a Command Button Click Event Procedure (we will do this at the end of this Article) as shown below to check and returns the User Group Name to open the Form in a particular 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 Sub

The CheckGroup() program creates a list of Work Groups, checks whether the User belongs to the Admins Group, and returns the result. If the result value is Admins then the Products Form is open in Read-Only Mode otherwise in Normal Mode.

The Workgroups Array creation is necessary because a particular User can belong to one or more Workgroups, like Admins, Users (default), Supervisor, Manager, Editor, or any other group in the Workgroup Information File(database.mdw), which he/she has been joined in.

The Filter() Function compares the text "Admins" in the array elements and if found it is output into the GrpOut(0) element.

We cannot use the Filter() Function in the Form Module Sub-Routine, because it will clash with the Form Property name Filter.

Coming back to the Open Arguments of Form and Reports we can pass the name of a Query as Filter Argument or a WHERE condition without the word WHERE. But, there is another parameter known as Open Argument (OpenArgs) through which you can pass a value to the Report or Form and read it back in the Class Module of Report or Form from the same variable OpenArgs and use it for whatever purpose you passed it.

OpnArgs Example

We try a simple example to learn the usage of this parameter. We need a few objects from the C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.

  1. Import the following from the Northwind.mdb sample database:

    • Table: Products
    • Table: Categories
    • Query: Products by Category
    • Report: Products by Category
  2. 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.

  3. Select the Combo Box and display its Property Sheet (View - -> Properties or press Alt+Enter).

  4. Change the Name Property value to cboCategory.

  5. Create a Command Button and change its Name Property value to cmdOpen and the Caption Property Value to Open Report.

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

  7. 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
    
  8. Save the Form with the name Open Argument Demo or any other name you prefer.

    Modify the Report Module

  9. Open Products by Category Report in Design View.

  10. Display the Class Module (View - -> Code)

  11. 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 Sub
    
  12. Save the Report with the Code.

  13. Open the Open Argument Demo Form in Normal View.

  14. Select a Product Category Name (say Beverages) in the Combo Box.

  15. 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 modified to show the Product category Name.

  16. Make the Combo box value empty by deleting the current value and clicking on the Open Report Command Button.

This time all the Products by Category will appear on the Report and the heading will remain without any change. We are testing the presence of some values in the OpenArgs Variable in the Report Open Event Procedure, if it is Null then terminate the Sub-Routine.

Trial Run of First Two Programs.

Do the following to try the first two Programs given at the top of this page:

  1. Open the Open Argument Demo Form in Design View.

  2. Create a second Command Button on the Form.

  3. Display the Property Sheet of the Command Button (View - -> Properties).

  4. Change the Name Property value to cmdOpenForm, and the Caption Property Value as Open Form.

  5. Display the Class Module (View - -> Code).

  6. Copy and paste the second Program from the top into the Module and save the Open Argument Demo Form.

  7. Create a Tabular type Form for Products Table and name the Form as Products.

  8. Open the Open Argument Demo Form and click on the Open Form Command Button.

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

Share:

3 comments:

  1. 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!

    ReplyDelete
  2. Sometimes 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.

    ReplyDelete
  3. Hi, thanks for very usefull information

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code