Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Creating Using Form Custom Property

Introduction

Normally, Parameter Controls are provided to the Users for entering data Filter criteria values for preparing MS-Access Reports. A reference to the parameter control fields can be set in the criteria row of the Report Source Query directly to filter the data. A sample image of such a Parameter Control is given below.

The above Report Parameter Control gives flexibility to the User to set a Date Range in the fields provided on the screen before opening one of two Report Options provided. When the user clicks on the Preview Command Button the Report will be opened with data filtered using the Parameter Control Date Range values set as Criteria on the Source Query.

To record the Date Range values (Date From and Date To) a Small Table with two fields is created with a single record and used as Record Source for the above Form. The idea behind the use of a Table is to preserve the last used Report Parameter Values in the Table. Next time when we open the Form we will know for which period we have prepared the Report earlier. The parameter table can also be used in the Query to link to the Data Table or use its value as criteria in the criteria Row of the Report Source Query. The following two Form Property Values must be set as shown below to prevent adding new Records to the Table and not to delete the existing ones:

  • Allow Additions = No
  • Allow Deletion = No

Multiuser Environment.

This method works fine when the Database is a Single User one.

But, when the database is shared on a Network there is a problem with this method. Different Users may open the same Parameter Screen at the same time (assuming that a single Front-End is shared on a Network) to prepare their version of the same Report. They will attempt to change the parameter values at the same time. This action will end up with a record, edit-lock error or the values set can cross over and the Report printed can go wrong too. Even though the Users can open different instances of the same Form on their machines the Record Source Table is the same.

We are focusing on this particular aspect to see how we can safely provide the above Parameter Control to the Users to work safely without clashing with each other.

Perhaps, you have the right solution to this problem by now. Yes, do not use the Parameter Table to store the Report criteria values; instead create two Unbound Text Boxes on the Form, as you have rightly guessed. This will ensure that all Users will work independently on their own instances of the Report Parameter Form and no chance of clashing with each other's value.

There is only a minor drawback in this method; you cannot store the last used Report Parameter Value so that it will be displayed next time when you open the Form.

At least one set of values is required when you open the Form next time. If these controls remain empty and if you run the Preview option without setting the parameter values, then the Report will be empty and will end up showing #Error in all controls with expressions created for Summary Values.

I have already published an article on this topic earlier on how to open the Report without this Error condition when the Report Source Query or Table is empty. Click here to find out.

We can save the values, from the Unbound Text Box controls, in the Parameter Form itself in Custom Properties, which we can create on the Form. Managing data in Custom Properties can be done only through VBA and these Property Names and their Values are not visible on the Property Sheets that we normally work with.

Click here to find out more details on Custom Properties and a method that we used earlier to open the Form with the last edited record as current on the Form.

The Custom Property

We have to go through the following procedure to manage the User data on the Form itself without the use of a Table as a Record Source:

  1. Create two Custom Properties on the Form with the names DateFrom and DateTo with the Data Type Date/Time and with an initial value.

    This is required only once. A small VBA Program is required in the Standard Module to create the Custom Properties on the Form. In the Program, the Parameter Form Name is required for reference. Not necessary to keep the Form in Design View to create the Custom Properties.

  2. When the Parameter Form is closed after normal use, the values set on the Unbound TextBoxes are Saved in the Custom Properties, during the Form Close Event.
  3. The saved values are loaded back into the Unbound Text Boxes from the Custom Properties when the Report Parameter Form is open next time.

The Design Task of Custom Property

  1. To try out this method, open a new Form, and create two Unbound Text Boxes.
  2. Click on the first Text Box and display its Property Sheet (View - -> Properties).
  3. Change the Name Property Value to fromDate.
  4. Change the Name Property Value of the second Text Box to to-Date.
  5. Close and save the Form with the name RptParameter.
  6. Display the VBA Editing Window (Alt+F11), and copy and paste the following VBA Code into the Standard Module. If necessary, create a new Module (Insert - -> Module).

    VBA Code to Create Custom Property

    Public Function CreateCustomProperty()
    Dim cdb As Database, doc As Document
    Dim prp As Property
    
    Set cdb = CurrentDb
    Set doc = cdb.Containers("Forms").Documents("RptParameter")
    Set prp = doc.CreateProperty("DateFrom", dbDate, Date)
    doc.Properties.Append prp
    
    Set prp = doc.CreateProperty("DateTo", dbDate, Date)
    doc.Properties.Append prp
    doc.Properties.Refresh
    
    Set prp = Nothing
    Set doc = Nothing
    Set cdb = Nothing
    
    End Function
  7. Click somewhere within the pasted VBA Code and press F5 to Run the Code and create two Custom Properties with the Names DateFrom and DateTo with the Data Type Date/Time and with the initial value of System Date.

    How do you know whether these Properties are created or not? Try running the Program again and this will tell you that these Property names already exist on the Form.

    VBA Code to Delete Property if needed.

    If you want to Delete these Properties from the Form then Run the following Code:

    Public Function DeleteCustomProperty()
    Dim cdb As Database, doc As Document
    Dim prp As Property
    
    Set cdb = CurrentDb
    Set doc = cdb.Containers("Forms").Documents("RptParameter")
    doc.Properties.Delete "DateFrom"
    doc.Properties.Delete "DateTo"
    doc.Properties.Refresh
    
    Set prp = Nothing
    Set doc = Nothing
    Set cdb = Nothing
    
    End Function
  8. Open the RptParameter Form in Design View.
  9. Display the VBA Code Module of the Form (View - -> Code).
  10. Copy and Paste the following two Sub-Routines into the Form Module and save the Form:

    Storing the Text Box Values in Properties

    Private Sub Form_Close()
    Dim cdb As Database, doc As Document
    
    Set cdb = CurrentDb
    Set doc = cdb.Containers("Forms").Documents("RptParameter")
    doc.Properties("DateFrom").Value = Me![fromDate]
    doc.Properties("DateTo").Value = Me![toDate]
    
    Set cdb = Nothing
    Set doc = Nothing
    
    End Sub
    

    Retrieving the Values from Custom Properties.

    Private Sub Form_Load()
    Dim cdb As Database, doc As Document
    
    DoCmd.Restore
    
    Set cdb = CurrentDb
    Set doc = cdb.Containers("Forms").Documents("RptParameter")
    
    Me![fromDate] = doc.Properties("DateFrom").Value
    Me![toDate] = doc.Properties("DateTo").Value
    
    Set cdb = Nothing
    Set doc = Nothing
    
    End Sub

    Perform a Demo Run.

  11. Open the RptParameter Form in Normal View and enter some Date Range values into fromDate and toDate Unbound Text Boxes.

    Close the form and open it again in Normal View. The date values you entered earlier will appear in both Unbound Text Boxes.

Even after implementing this method, I am not fully happy with it. Because, it will preserve only one of the Values, set by different Users working with the Form at the same time.

What I would like to see as a User is that the last value that I set in the Report Parameter Field is appearing on the Form again when I open the Form next time, not the value set by someone else. Is it possible? Yes, it is possible. We will see how to do this next week.

Share:

2 comments:

  1. 'This method works fine when the Database is a Single User one.' Great! Thank you for this post. The information provided on this computer software is perfect for such custom property.

    ReplyDelete
  2. Thanks.
    The explanation on the limitation of different solution is great.
    I have try out the method and it work for me.
    This is also a good example to understand the costume properties.

    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