LEARN MS-ACCESS TIPS AND TRICKS

Text Search Filter Web Style

a.p.r. pillaiDecember 21, 2009Functions, Process Controls 6 comments

Text Search Filter Web Style.

How to search for several pieces of information across all the fields and in all the records of a Table?

For example, when we search for something on the web, we often enter multiple keywords separated by a Space or the “+” symbol. The search engine then looks for any of the specified terms matching across web pages and displays the matching results on the screen.

Search text example 1: ms-access, forms, reports, queries

Search text example 2: ms-access+forms+reports+queries

In the same way, we can create filters to display records from a table that match several pieces of Text/numbers/Phrases across any field in any record.

Last week, we learned how to use the BuildCriteria() function to filter data using a single field. The BuildCriteria() function accepts only one field as its first parameter. In this session, we’ll use the same function again — but with a clever trick that allows us to apply it to all the fields in a table.

A Simple Demo Run.

So let us do this with a simple Query and a Tabular Form.

Import the Customers Table from:
'C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb' sample database.
Create a new Query based on the Customers table including all its fields.
Then, add a calculated column named FilterField to the Query.
Write the following expression in the new column to join all the Text and Numeric Field values together into a single Column:

FilterField: [CustomerID] & " " & [CompanyName] & " " & [ContactName], etc.; join all the fields this way except HyperLinks, Objects, and Yes/No Field Types. Save the Query named myQuery.
You can automate this task using the following VBA program.
Before running the code, create a Query manually that includes at least one field from the source table and name it myQuery.

If you prefer to use a different Query name, simply modify the program wherever the reference to "myQuery" appears to match your chosen name.
The CombineData() Code.
```
Public Function CombineData(ByVal tblName As String)
'----------------------------------------------------------
'Author : a.p.r. pillai
'Date   : December 2009
'Rights : All Rights Reserved by www.msaccesstips.com
'----------------------------------------------------------
Dim strsql1 As String, db As Database, qrydef As QueryDef
Dim fldName As String, k As Integer, j As Integer
Dim tbldef As TableDef, strjoin As String

On Error Resume Next

strsql1 = "SELECT " & tblName & ".*, "
Set db = CurrentDb
Set qrydef = db.QueryDefs("myQuery")

Set tbldef = db.TableDefs(tblName)
k = tbldef.Fields.Count - 1

strjoin = ""
For j = 0 To k
    If tbldef.Fields(j).Type <> 1 And tbldef.Fields(j).Type <> 11 And tbldef.Fields(j).Type <> 12 Then
        If Len(strjoin) = 0 Then
            strjoin = "[" & tbldef.Fields(j).Name & "] "
        Else
            strjoin = strjoin & " & " & Chr$(34) & " " & Chr$(34) & " &  [" & tbldef.Fields(j).Name & "] "
        End If
    End If
Next

strsql1 = strsql1 & "(" & strjoin & ") AS FilterField FROM " & tblName & ";"
qrydef.SQL = strsql1
db.QueryDefs.Refresh

Set tbldef = Nothing
Set qrydef = Nothing
Set db = Nothing

End Function
```
- Copy and paste the above VBA Code into a Standard Module and save it.
CombineData "Customers"
This program modifies the design of myQuery by combining all fields from the Customers table (or any other source table you specify), except for fields of the following types:
- Hyperlink
- Object (OLE Object)
- Yes/No
- Memo (Long Text)
A new calculated column named FilterField will be created, which concatenates the values of all supported fields.

If you also need to include Memo field contents, you must add that field manually in the Query Design view.
The CombineData() Function intentionally skips Hyperlink, Object, and Memo fields because they all share the same internal data type category, and the program’s validation logic excludes them by design.
The Sample Datasheet view image of the FilterField in myQuery is given below:
Right-click to open the Large Image in a New Tab/Window.

Design a Form.
Create a Tabular Form (continuous form) using myQuery as the Record Source and save the Form as frmMyQuery.
Open the form frmMyQuery in the Design view.
Select the FilterField Column and display its Property Sheet (View ->Properties)
Change the Visible Property Value to No.
Make the FilterField column size very narrow on the Form (it is not visible on normal view) and resize other Columns to view their contents properly.
Remove columns such as Region, Fax, or any other fields that you consider unnecessary for this test run.
This will help ensure that all the required columns fit neatly on a single screen for easier viewing and testing.
Expand the Form Header Section and drag the column headings down so that there is enough space to draw a TextBox and a CommandButton beside it.
Create a Text Box above the column headings.
Display the Property Sheet of the Text Box (View ->Properties).
Change the following Property Values as given below:
- Name = txtSearch
- Width = 3"
Change the Caption of the Child Label of the Text Box to Search Text (delimiter: comma(,) or plus(+)):
Create a CommandButton to the right of the Text Box and change the following Property Values:
- Name = cmdGo
- Caption = GO>
The Form's Class Module Code.
Display the Code Module of the Form (View -> Code).

Copy and Paste the following VBA Code into the Module.

Private Sub cmdGo_Click()
'----------------------------------------------------------
'Author : a.p.r. pillai
'Date   : December 2009
'Rights : All Rights Reserved by www.msaccesstips.com
'----------------------------------------------------------
Dim x_Filter, j As Integer
Dim Y_Filter, Xchar As String, flag

x_Filter = Nz(Me![txtSearch], "")

If Len(x_Filter) = 0 Then
    Me.FilterOn = False
    Exit Sub
End If
'Code segment that tests and removes extra spaces'
'between coma and next search text item.

'--Extra space removal Segment start-
Y_Filter = ""
For j = 1 To Len(x_Filter)
    Xchar = Mid(x_Filter, j, 1)
' Test for presence of , or + and leading spaces
    If (Xchar = "," Or Xchar = "+") And Mid(x_Filter, j + 1, 1) = " " Then
            flag = True
    ElseIf Xchar = " " And flag Then
            flag = False
            Y_Filter = Trim(Y_Filter)
    End If
    Y_Filter = Y_Filter & Xchar
Next
x_Filter = Y_Filter

'--Extra space removal Segment End-

Y_Filter = "*"
For j = 1 To Len(x_Filter)
        Xchar = Mid(x_Filter, j, 1)
     'Validation check
      If Xchar = "(" Or Xchar = ")" Then
                MsgBox "Invalid Characters () in expression, aborted... "
                Exit Sub
     End If
If Xchar = "," Or Xchar = "+" Then
'test for presence of ,+
     Xchar = "* OR *"
End If
Y_Filter = Y_Filter & Xchar
Next

Y_Filter = Y_Filter & "*"
Me.FilterOn = False
Y_Filter = BuildCriteria("FilterField", dbText, Y_Filter)
Me.Filter = Y_Filter
Me.FilterOn = True

End Sub

The Sample Demo Run.

Save the Form and open it in Normal View.
Type some Text/Numbers/Phrases separated with commas (,) or plus (+), which can match in any part of any Field(s) or in different Record(s), in the Text Box.
Example 1: FRANK, Elizabeth Brown, Brazil
Example 2: FRANK+Elizabeth Brown+Brazil
Example 3: Frank Elizabeth Brown, Brazil
Note: Do not use parentheses ( ) in the search text, as they can cause errors when processed by the BuildCriteria() function. Users might accidentally include extra spaces between the text separator (such as the + symbol) and the next search item; these are removed before the search and filter operations begin.
However, spaces within phrases (embedded spaces) in the search text will remain intact.
Click on the GO> CommandButton to search for the given text in fields/records and filter those records on the Form.
You may inspect the filtered records to ensure that one or more of the search texts you have entered into the Text Control appear in all the filtered records. They can appear in any field(s) in any Record, but all filtered records will have these texts/numbers/phrases.

Filter with BuildCriteria Function

a.p.r. pillaiDecember 15, 2009Functions, Process Controls No comments

Filter with the BuildCriteria Function.

Any method that helps users find data on a form quickly is always appreciated. In Form View mode, several options are available to locate records efficiently.

When you right-click a field, a shortcut menu appears, displaying four data filter options, as shown in the sample image below.

Right-click to open Large Image in New Tab/Window.

The third option, Filter For, accepts a criteria expression such as

>10200 AND <=10300

to filter a specific range of values from the selected field.

If you’d like to explore more filtering options, point to the Filter option on the Records menu. There, you’ll find two additional choices: Filter by Form and Advanced Filter/Sort.

Filter by Form allows criteria on multiple fields to select records based on values entered directly into Form fields.

Advanced Filter/Sort opens the Filter Design (or Query Design) window, displaying the source table or query of the form, along with any criteria you may have already entered using Filter for or Filter by Form. You can then further modify your filter conditions, adjust the sorting order, and select Apply Filter from the Records menu to view the results.

However, if you’d like to build a custom filter option for the user—based on a specific field on the form—you can use the built-in BuildCriteria() function. By writing a VBA subroutine around this function and running it from a command button’s Click event, you can allow the user to input filter criteria in flexible ways.

The function usage is quite simple. Let’s try a few examples directly in the Immediate (Debug) Window to see how it works.

Press Alt+F11 to display the Visual Basic Editor window.
Press Ctrl+G to display the Debug window.
Type the following example expressions and press the Enter key to display the output:

Sample Run of the BuildCriteria() Function.

? BuildCriteria("OrderID",dblong, "10200")

Result: OrderID=10200

The BuildCriteria() function requires three parameters.

In the example, OrderID is the data field name, dbLong indicates that OrderID is a numeric field of type Long Integer, and the last value 10200 represents the criteria value used to select matching records.

The BuildCriteria() function automatically inserts the field name (OrderID) into the correct positions within the criteria expression.

The third parameter can be used in different ways depending on the result you want to achieve. Let’s explore a few more examples to better understand its flexibility before we implement this method on a form.

Type the following expressions in the Immediate (Debug) Window to see how the function behaves:

? BuildCriteria("OrderID",dblong, ">=10200 AND <10300")

Result: OrderID>=10200 And OrderID<10300

? BuildCriteria("OrderID",dblong,">=10200 AND <10300 OR >=10400 AND <10500")

Result: OrderID>=10200 And OrderID<10300 Or OrderID>=10400 And OrderID<10500

Try changing the data type to dbText, for example:

? BuildCriteria("OrderID",dbText, "10200")

Result: OrderID="10200"

? BbuildCriteria("OrderDate",dbDate,">10/15/2009 and <=10/31/2009")

Result: OrderDate>#10/15/2009# And OrderDate<=#10/31/2009#

Using a Form to Filter Data.

After getting the result text from the BuildCriteria() Function, all we have to do is insert it into the Filter Property of the Form and turn ON the Filter action.

Let us design a simple Form to run our example straight away.

Import the Orders
Table from the C:\Program Files\Microsoft Office\Officell\Samples\Northwind.mdb sample database.
Select the Orders Table and select Form from the Insert Menu.
Select Auto Form: Tabular to create a Form and save it with the name Orders.
Open the Orders Form in Design View.
Expand the Form Header Area and drag all the Field Headings down to get enough room to create a CommandButton above the Field Headings.
Display the Toolbox if it is not visible (View ->Toolbox).
Select the CommandButton Tool and create a Command Button on the Form Header.
Display the CommandButton's Property Sheet (View -> Properties).
Change the Name Property Value to cmdFilter and change the Caption Property Value to OrderID Filter.
Display the Code Module of the Form (View -> Code).

Run it with a Button Click.

Copy and Paste the following VBA Code into the Module.

Private Sub cmdFilter_Click()
Dim txtCondition, txtFilter As String

txtCondition = InputBox("OrderID Value/Range of Values")
If Len(txtCondition) = 0 Then
    Me.FilterOn = False
    Exit Sub
End If

txtFilter = BuildCriteria("OrderID", dbLong, txtCondition)

Me.FilterOn = False
Me.Filter = txtFilter
Me.FilterOn = True

End Sub

Save and Close the Form.
You may click on the OrderID Filter CommandButton and enter any of the example (except the one with Date) criteria expressions we have entered as the third Parameter into the BuildCriteria() Function above, when prompted for the Filter Condition.

Setting up Criteria Value on Text Box.

If you don't like to use InputBox() to prompt for Criteria Values, then you may create a Text Box on the Form where Users can enter the Criteria expression before hitting the Command Button.

The BuildCriteria() Function accepts only one field as the first parameter. But there is a way to use multiple fields in the Filter condition. Ask the User to enter conditions for two different fields separately, and run the BuildCriteria() function separately to obtain the results. Join both results with AND/OR Logical operators to filter the data.

The following example code uses OrderID and ShipName field values to filter data on the Orders Form.

Create a Copy of the Orders Form and name the Form Orders2.
Open the Form in Design View.
Display the Code Module of the Form (View -> Code).
Copy and paste the following Code into the Module, replacing the existing Code:

Private Sub cmdFilter_Click()
Dim txtOrderNumber, txtOrderfilter As String
Dim txtShipName, txtShipNameFilter As String
Dim msg As String, resp, txtFilter As String

txtOrderNumber = InputBox("OrderID Value/Range of Values to Filter")
txtShipName = InputBox("ShipName/Partial Text to Match")

If Len(txtOrderNumber) > 0 Then
    txtOrderfilter = BuildCriteria("OrderID", dbLong, txtOrderNumber)
End If

If Len(txtShipName) > 0 Then
    txtShipNameFilter = BuildCriteria("ShipName", dbText, txtShipName)
End If

If Len(txtOrderfilter) > 0 And Len(txtShipNameFilter) > 0 Then
    msg = "1. Filter items-that matches both filter conditions" & vbCr & vbCr
    msg = msg & "2. Matches either one or Both conditions" & vbCr & vbCr
    msg = msg & "3. Cancel"
    Do While resp <> 1 And resp <> 2 And resp <> 3
        resp = InputBox(msg)
    Loop
    Select Case resp
        Case 3 
           Exit Sub
        Case 1
            txtFilter = txtOrderfilter & " AND " & txtShipNameFilter
        Case 2
            txtFilter = txtOrderfilter & " OR " & txtShipNameFilter
    End Select
Else
    txtFilter = txtOrderfilter & txtShipNameFilter
    If Len(Trim(txtFilter)) = 0 Then
       Exit Sub
    End If
End If

Me.FilterOn = False
If Len(Trim(txtFilter)) > 0 Then
    Me.Filter = txtFilter
    Me.FilterOn = True
End If

End Sub

How does it work?

The first two InputBox() Functions collect the Filter Criteria for the OrderID and ShipName Field values separately. Next steps: Validate the User responses and build the criteria strings in the txtOrderFilter and txtShipNameFilter Variables.

If both Variables have filter conditions, the User response is collected and checked to determine whether the user needs the result set that meets both conditions (AND) OR the result that meets any one of the conditions.

The Filter Strings are joined accordingly to obtain the intended result. The User doesn't always provide both sets of Criteria Values (for OrderID and ShipName). They can use only one Field for entering Criteria and ignore the other.

Save User Specific Parameter values

a.p.r. pillaiDecember 07, 2009Forms, Process Controls 5 comments

Save User-Specific Parameter values.

Last week, we created a Report Parameter Control Form for concurrent users on a network, ensuring that each user’s report filter parameters do not conflict with others when generating different versions of the same report.

To achieve this, we avoided using a parameter table as the record source for the parameter form. Instead, we added two unbound text boxes for entering the parameter values, which are then used to set the filter conditions in the report’s source query.

Additionally, we saw how to save these parameter values in the form’s Custom Properties when the Form is closed, for later use.

The sample image of the Report Parameter Form: RptParameter is given below for reference:

Right-click to open the Large Image in a New Window.

Last week, we used Textboxes as parameter controls for FromDate and ToDate to enter date-range values as criteria for filtering data in the report’s source query. The custom property names we created were slightly modified versions of these TextBox names, such as DateFrom and DateTo.

One question we raised was: Is it possible to save and retrieve the Report parameter values entered by each user in different instances of the same form without overwriting other users' values?

In last week’s example, only one set of custom properties was created for all users and all instances of the form. Consequently, the last user on the network to close their Form instance will overwrite the custom property values of earlier users. These overwritten values will then be loaded back into the text boxes the next time the form is opened.

User-based Custom Property Name Format.

The solution is straightforward: create custom properties on the form for each user. Save the user-level parameter values into their respective Form custom properties when the form is closed.

To uniquely identify each user’s values, combine the user name with the text box name when naming the custom property. This ensures that each user has a distinct custom property, preventing one user’s input from overwriting another’s.

For example:

User Name: JSmith
Parameter TextBox Name1: fromDate
Parameter TextBox Name2: toDate
Custom Property Name1: JSmithDateFrom
Custom Property Name2: JSmithDateTo

We can retrieve the user name of the current form instance using the CurrentUser() function.

When the User-Name prefixes the Custom Property Names, we can easily save and retrieve individual users' parameter values and use them on their instance of the same Form.

Another point to remember is that new users may be added to the same Workgroup, and this method should work for them also when they start using the RptParameter Form.

We must write a VBA routine that identifies a new user when they join the workgroup and creates a custom property for him.

The Custom Property Creation Code.

The modified Custom Properties creation Program is given below. Copy and paste the code into a Standard Module of your Database.

Public Function CreateCustom_Property(ByVal frmName As String, ByVal usrName As String)
'-------------------------------------------------------------------------
' Author : a.p.r. pillai
' Date   : November-2009
' All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------------
Dim cdb As Database, doc As Document
Dim prp As Property, getPrpValue
Dim fld1 As String, fld2 As String

On Error Resume Next

fld1 = usrName & "DateFrom"
fld2 = usrName & "DateTo"

Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents(frmName)

'check whether the Property of the current user exists
getPrpValue = doc.Properties(fld1).Value

If Err = 3270 Then 
' referenced Property doesn't exist
    Err.Clear
'create Property for new User
    Set prp = doc.CreateProperty(fld1, dbDate, Date)
    doc.Properties.Append prp
    Set prp = doc.CreateProperty(fld2, dbDate, Date)
    doc.Properties.Append prp
    doc.Properties.Refresh
End If

Set prp = Nothing
Set doc = Nothing
Set cdb = Nothing

End Function

Create a Copy of the RptParameter Form with the name RptParameter2.
Open the RptParameter2 Form in Design View.
Display the Form VBA Module (View -> Code).

Revised VBA Code - Form Load.

Copy and paste the following subroutines into the VBA Module, overwriting the earlier Code:

Private Sub Form_Load()
Dim cdb As Database, doc As Document
Dim fld1 As String, fld2 As String

fld1 = CurrentUser & "DateFrom"
fld2 = CurrentUser & "DateTo"

'Validate Current User's Status
'If, necessary create Custom Properties for new User
CreateCustom_Property Me.Name, CurrentUser

DoCmd.Restore

Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents(Me.Name)
Me![fromDate] = doc.Properties(fld1).Value
Me![toDate] = doc.Properties(fld2).Value

Set cdb = Nothing
Set doc = Nothing

End Sub

Revised VBA Code - Form Close.

Private Sub Form_Close()
Dim cdb As Database, doc As Document
Dim fld1 As String, fld2 As String

fld1 = CurrentUser & "DateFrom"
fld2 = CurrentUser & "DateTo"

Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents(Me.Name)

doc.Properties(fld1).Value = Me![fromDate]
doc.Properties(fld2).Value = Me![toDate]

Set cdb = Nothing
Set doc = Nothing
End Sub

Save and Close the Form.

Review of Programs.

As shown in the Form_Load() event procedure, the custom property creation routine CreateCustom_Property() is called only when a user (new or existing) opens the RptParameter2 form.

This means that the custom properties are not created for all database users—created only for users who interact with this specific form.

The Report Parameter Form should be tested simultaneously from different machines by multiple users. Each user can enter their own values in the parameter control text boxes (FromDate and ToDate) to test the procedure. When the same users open the form in a new session, the values they entered previously should automatically appear in the text boxes.

Note: The above procedure has not been extensively tested for bugs or side effects; use it at your own risk. If you encounter any issues, please share them. The procedure assumes that the database is implemented with Microsoft Access Security.

For unsecured databases, visit the page “Unsecured Database and Users Log” to learn how to retrieve a user’s name. In an unsecured database, the CurrentUser() function will always return the username as Admin.

Creating Using Form Custom Property

a.p.r. pillaiNovember 30, 2009Forms, Process Controls 2 comments

Creating Using A Form Custom Property.

Normally, parameter controls are provided to users for entering data filter criteria when generating MS Access reports. The parameter control fields can be referenced in the criteria row of the report’s source query to filter data based on the user’s input. A sample image of such a parameter control form is shown below.

Right-click to open the Large Image in a New Tab/Window.

The Report Parameter Control Form makes it easy to specify a date range before opening one of the two available report options. When the user clicks the Preview command button, the report opens with the data filtered for the Date Range specified in the parameter controls.

To store these date range values, a small table is created with just two fields and a single record, which serves as the record source for the form. The purpose of this table is to preserve the last-used report parameters, allowing users to recall the previous reporting period when they open the form.

This parameter table can also be referenced in the report source query, either by linking it to the main data table or by using its field values directly in the criteria row to filter data.

To ensure the table always contains only one record, the following form property settings must be configured:

Allow Additions = No
Allow Deletion = No

Multiuser Environment.

This method works fine when the Database is a single-user version.

However, when the database is shared over a network, this method introduces a major issue. Multiple users may open the same Report Parameter Form simultaneously (especially when a single shared front-end is used across the network).

In such cases, all users are referencing the same parameter table as the form’s record source. As a result:

When one user modifies the parameter values (for example, changing the FromDate or ToDate),
The same record is being edited by other users concurrently.

This situation can result in record edit-lock conflicts or, more critically, allow other users to modify the parameter values. As a result, reports generated by different users may be based on incorrect or mixed filter criteria, producing inaccurate or inconsistent output.

Even though each user technically opens a separate instance of the form on their own machine, they’re all connected to the same back-end table, which causes this conflict.

We are now focusing on this specific issue — how to safely provide the Report Parameter Control to multiple users without conflicts.

You’ve probably guessed the right solution by now: avoid using a shared Parameter Table to store report criteria values. Instead, use two unbound text boxes on the Form (for example, FromDate and ToDate).

This approach ensures that each user works within their own isolated instance of the Parameter Form, preventing overlapping or clashing parameter values.

The only minor drawback of this method is that the last used parameter values cannot be automatically preserved for display the next time the user opens the form.

At least one set of parameter values must be available when the form is opened next time. If these controls are left empty and the Preview option is run without entering any values, the Report will be generated without data.

As a result, the controls in the Report that contain expressions for summary or calculated values will display #Error, since the underlying dataset is empty.

To prevent this, we need a mechanism to retain or restore the last-used parameter values each time the form is opened — ensuring that the Report always has a valid date range or criteria to work with.

I published an article earlier on how to open a Report without triggering this #Error condition when the Report Source Table or Query output is Null.

You can [click here to read that article] for the complete method and example.

We can create Custom Properties in the Parameter Form and save the unbound TextBox parameter values in the custom Properties. It works like the Tag Property of a control.

These Custom Properties serve as hidden variables stored within the Form itself. They can be created, modified, and accessed through VBA, but they are not visible in the standard Property Sheet displayed in Design View.

Click here to learn more about Custom Properties and the method we used earlier to open a Form with the last edited record automatically set as the current record when the Form loads.

The Custom Property.

To manage user data directly on the Form—without using a Table as its Record Source—follow the procedure below:

Create two Custom Properties on the Form named DateFrom and DateTo, with the Date/Time data type and initial values.
- This step needs to be done only once.
- You’ll need a small VBA program in a Standard Module to create these Custom Properties.
- The program will require the Form name as a reference, but it’s not necessary to open the Form in Design View to create the properties.
When the Parameter Form is closed after normal use, the values entered in the unbound text boxes are automatically saved into the Custom Properties during the Form_Close event.
The next time the Form is opened, these saved values are loaded back into the unbound text boxes from the Custom Properties, restoring the user’s last-used parameter values.

The Design Task of Custom Property.

To try out this method, open a new Form and create two Unbound TextBoxes.
Click on the first TextBox and display its Property Sheet (View -> Properties).
Change the Name Property Value to fromDate.
Change the Name Property Value of the second TextBox to ToDate.
Close and save the Form named RptParameter.

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

Click inside the pasted VBA code and press F5 to run it. This will create two Custom Properties on the Form—DateFrom and DateTo—with the Date/Time data type and an initial value set to the current system date.

Wondering how to confirm whether these properties were actually created? Simply run the program again. This time, the program will display a message indicating that the 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
```
Open the RptParameter Form in Design View.
Display the VBA Code Module of the Form (View -> Code).

Copy and paste the following subroutines 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.

Open the RptParameter Form in Normal View and enter some Date Range values into the 'fromDate' and 'toDate' Unbound TextBoxes.
Close the form and open it 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 satisfied with it. The reason is that it preserves only one set of values, so if multiple users work with the Form at the same time, only one value is saved.

“As a user, I would like the last value I entered in the Report Parameter fields to appear the next time I open the Form, rather than being overwritten by a value set by someone else.”

Is this possible? Yes, it is, and we will explore how to achieve this next week.

Office Assistant and MsgBox Menus
Color and Picture in MsgBox
Microsoft Excel Power in Access
Unsecured Database and Users Log
MS-Access Report and Page Total

MS-Access And Data Processing-2

a.p.r. pillaiNovember 23, 2009msaccessprocess, Process Controls 5 comments

Continued from Last Week's Post.

This is the continuation of an earlier article published on this subject last week. Click here to visit that page.

Last week, we explored sample data processing methods and attempted to solve the same problem from different angles to arrive at the same result. Reports are the primary output delivered to the User, with critical information for analyzing business activities and making informed business decisions. Transforming raw data into a meaningful design as a Report is a real challenge for any Project.

If you attain some working knowledge of different types of Queries available in MS Access, you can do most of these tasks without touching the VBA Code. Depending upon the complexity of processing steps, you can use several Queries, create intermediate temporary Tables, and use those tables as the source for other Queries to overcome issues that may arise as hurdles in the processing steps.

We will examine this issue to help you understand the challenges involved in producing the final report. Complex data-processing tasks of this kind can be automated by arranging the individual steps in a macro and executing it from a command button or a VBA subroutine.

Process Flowcharts.

It is essential to create and maintain flowcharts for processes that involve multiple queries and tables, clearly showing the input and output at each step, to the final report data. Over time, you may build hundreds of queries in a database for different reports and forget how a particular one was structured. If a user later points out an error in the output, a well-documented flowchart helps you to easily trace each step and identify where the problem occurred.

Last week, I posed a question: how can we display Revenue, Expenses, and Profit/Loss on a month-by-month basis if the sample data includes separate Year and Month fields? The image below shows the sample source table (Transactions2).

Right-click to open the Large Image in a New Tab/Window.

The image of the Report Output Created and presented to you last week is shown below:

Right-click to open the Large Image in a New Tab/Window.

We can transform the sample data in the first image above into the Report output form in the second image in two steps. The numbers appearing as a Suffix to the Column headings represent the Month Value. For example, Revenue1 is January Revenue, and Profit/Loss2 is February.

We can arrive at the above result in two steps, and the SQL String of those two Queries is given below:

Query Name: Method2_1

TRANSFORM Sum(Transactions2.Amount) AS SumOfAmount
SELECT Transactions2.Location,
 Transactions2.Year
FROM Transactions2
GROUP BY Transactions2.Location,
 Transactions2.Year
PIVOT IIf([type]="R","Revenue","Expenses") & [Month];

Copy and paste the above SQL String into the SQL Editing Window of a new Query and save it with the name Method2_1.

Open the Query and view the output as it is transformed with the Cross-Tab Query.

Query Name: Method2_2

SELECT Method2_1.Location,
 Method2_1.Year,
 Method2_1.Revenue1,
 Method2_1.Expenses1,
 [Revenue1]-[Expenses1] AS [Profit/Loss1],
 Method2_1.Revenue2,
 Method2_1.Expenses2,
 [Revenue2]-[Expenses2] AS [Profit/Loss2]
FROM Method2_1;

Copy and paste the above SQL String into the SQL Editing Window of a new Query and save it with the name Method2_2.
We are using the first Query as input to the second Query for the final Report output.
Open the Method 2_2 Query and view the output.

Even though we can get the desired results with the two queries, the second query must be modified each time new monthly data records are added to include the new Profit/Loss column. Since the Profit & Loss Report is based on this query, it also needs to be updated to include the corresponding Revenue, Expenses, and Profit columns for the new period.

This approach is not ideal, especially when the goal is to automate all database processes so that users can produce reports with a single click.

We can automate this data processing task permanently with the following few simple steps:

Create a second Report Table with Revenue and Expenses Fields for all twelve months.
Change the second Query created above (Method2_2) to an append query and add the output data of available months into the Report Table.
Create a SELECT Query, using the Report Table as the source, to calculate Profit/Loss Values for all twelve months, only once. This is possible because we have all twelve months' data fields in the Report Table, even if some of them will have only zero values till December.
Design the P&L Report with all twelve months' Revenue, Expenses, and Profit/Loss Fields using the Query created in Step 3 as the source.

Once you implement this method, you don't have to make any changes to the Queries or Reports when new data records are added to the Source Table. All you have to do is automate this process, like deleting the old data (for this action, we will need a Delete Query) from the Report Table and bringing in fresh Report data from the source table Transactions2.

Designing a Report Table.

Create a Table with the following Field Structure and save it with the name PandLReportTable.
The Data Fields R1 to R12 and E1 to E12 will hold Revenue and Expenses Values for the period from January to December, respectively.
Note: Don't forget to set the Default Value Property of all Number Fields with 0 values as shown in the Property Sheet below the Field Structure. This will prevent adding data fields with Null Values when data is not available for those fields. Remember, when you write expressions using Numeric Fields with Null values combined with fields with values, the end result will be Null.
Right-click to open the Large Image in a New Tab/Window.

We have modified the first Query above to simplify the data field names.

Copy and paste the following SQL String into a new Query's SQL Editing Window and save it with the name Method3_1.

TRANSFORM Sum(Transactions2.Amount) AS SumOfAmount
SELECT Transactions2.Location,
 Transactions2.Year
FROM Transactions2
GROUP BY Transactions2.Location,
 Transactions2.Year
PIVOT [type]&[Month];

Copy and paste the SQL string given below into a new Query and save it with the name Method3_2.
```
INSERT INTO PandLReportTable
SELECT Method3_1.*
FROM Method3_1;
```

Copy and paste the following SQL String into a new Query and save it with the name PandLReportQ.

SELECT PandLReportTable.Location,
 PandLReportTable.Year,
 PandLReportTable.R1,
 PandLReportTable.E1,
 [R1]-[E1] AS P1,
 PandLReportTable.R2,
 PandLReportTable.E2,
 [R2]-[E2] AS P2,
 PandLReportTable.R3,
 PandLReportTable.E3,
 [R3]-[E3] AS P3,
 PandLReportTable.R4,
 PandLReportTable.E4,
 [R4]-[E4] AS P4,
 PandLReportTable.R5,
 PandLReportTable.E5,
 [R5]-[E5] AS P5,
 PandLReportTable.R6,
 PandLReportTable.E6,
 [R6]-[E6] AS P6,
 PandLReportTable.R7,
 PandLReportTable.E7,
 [R7]-[E7] AS P7,
 PandLReportTable.R8,
 PandLReportTable.E8,
 [R8]-[E8] AS P8,
 PandLReportTable.R9,
 PandLReportTable.E9,
 [R9]-[E9] AS P9,
 PandLReportTable.R10,
 PandLReportTable.E10,
 [R10]-[E10] AS P10,
 PandLReportTable.R11,
 PandLReportTable.E11,
 [R11]-[E11] AS P11,
 PandLReportTable.R12,
 PandLReportTable.E12,
 [R12]-[E12] AS P12
FROM PandLReportTable;

Design a Report using PandLReportQ as the Source File, similar to the sample design image given below.
The sample report currently displays columns for January and February only. However, you can easily extend the same design to include all twelve months. The value from the Year field is used to generate the report headings, ensuring the headings are automatically updated each year when the report is printed—without requiring any manual modifications to the report design.

Right-click to open the Large Image in a New Tab/Window.

The Report in Print Preview.
Right-click to open the Large Image in a New Tab/Window.

We will now automate the Report preparation process so that the Profit and Loss (P&L) Report reflects the updated data whenever new Revenue and Expense entries are added to the source table.
As part of this automation, we’ll create a Delete Query to clear the existing data from the PandLReportTable before inserting the latest, revised records.
Create a new Query with the following SQL String and name the Query as PandLReportTable_Init.

DELETE PandLReportTable.*
FROM PandLReportTable;

Isn't it easy enough to prepare the P & L Report with the above simple Queries and with a supporting Report Table for any number of Locations that you add to your main Source Table, Transactions2? As you can see now, you don't need any complicated programs to prepare this Report.

Action Queries in Macro.

If you look at the Queries we have created, you can see that only two are action queries among them (Delete and Append Queries). We can put these two Queries into a Macro to automate the P&L Report preparation procedure. But first, let us examine the logical arrangement of this Report preparation procedure with a Process Flow Chart.

Right-click to open the Large Image in a New Tab/Window.

In Step 1, the PandLReportTable_Init Delete Query clears all previously generated report data from the PandLReportTable.

In Step 3, the Append Query (Method3_2) takes the output from the CrossTab Query in Step 2 and appends it to the PandLReportTable.

We have already defined expressions in the PandLReportQ (SELECT Query) to calculate the Profit/Loss values. The report automatically retrieves all available data from this query, while other columns will remain blank until new records are added to the Transactions2 source table.

To streamline the process, both Action Queries can be combined into a Macro (or a VBA Subroutine) so the user can generate an updated P&L report each month simply by clicking a CommandButton — producing results within seconds.

The sample image of the Macro with the Action Queries in the sequence is given below for reference:

If you can further simplify this procedure, please share that idea with me, too.

MS-Access and Data Processing

a.p.r. pillaiNovember 16, 2009msaccessprocess, Process Controls 8 comments

MS Access and Data Processing.

Designing Forms and Reports in Microsoft Access can be learned quickly by experimenting with the available Design Tools. However, data processing presents an entirely different challenge, as it requires flexibility and varies from one project to another.

Table and Query designs are critical and must be carefully planned to ensure efficient information retrieval and prevent data duplication. Establishing appropriate relationships between tables is equally important for combining and processing related information from multiple tables.

If these principles are ignored — if you design casually and enter data as you might in Microsoft Excel — you’ll likely face serious difficulties when you try to prepare reports later.

A good example of proper database design and relationships can be found in the sample database:

C:\Program Files\Microsoft Office11\Samples\Northwind.mdb

Open the Northwind sample database and select Relationships from the Tools menu to view the structure of the various tables and how they are organized and related to one another. Use this as a reference and guide when planning your own projects.

Each field name shown in bold represents a Primary Key in its respective table. These keys establish one-to-many relationships between tables, ensuring that the required information can be accessed efficiently from related tables when generating reports.

The above notes are a reminder for your future projects. To illustrate the importance of proper table design, consider the sample image of a poorly structured table shown below. In that design, Location Names and Description values are directly entered in the same table — a mistake that leads to data duplication and maintenance difficulties.

Instead, each of these — Location and Description — should be maintained in separate lookup tables, each with its own unique code. You can then use Combo Boxes in the Transactions table structure and insert them in the fields, ensuring data consistency and eliminating redundancy, as demonstrated in the corrected design below.

Right-click to open the Large Image in a New Tab/Window.

Approaching the Data Processing Task.

In this exercise, we will explore and learn the data processing steps required to derive meaningful information from the table shown above.

The second field, Type, contains transaction category codes — R for Revenue and E for Expenses. These codes were intentionally included in the table design to allow us to group transactions by category and tabulate the Revenue and Expense values. The Description field lists the actual Account Heads under which each transaction is recorded.

Our task is to prepare a Location-wise Profit and Loss (P&L) Statement. To achieve this, we need to sum all Revenue values and subtract the total of all Expense values for each location. The final result should display the Profit or Loss amount alongside the corresponding Location Name, as shown in the sample report image below.

Now, here’s a question to consider:
How many queries or steps do you think are needed to produce this report?

Surprisingly, the entire process can be completed in just a few well-designed steps — ultimately yielding the final Profit/Loss summary by Location.

Right-click to open the Large Image in a New Tab/Window.

The first thought that comes to one's mind is: how can you subtract the value of one row from another? If that’s what you’re thinking, you’re already on the right track.

Now, let’s talk about the number of steps. If you say it takes four steps, I won’t consider that the best approach — but if it gets the correct result, that’s fine. After all, the end result is what really matters to the User.

If you can solve it in three steps, I’ll be curious to see your method. If you can do it in two steps, then you clearly have a solid grasp of MS Access techniques. And if you can do it in one step, then you are an MS Access expert.

If you’re truly interested in this challenge, here’s my suggestion:
Stop reading here, and try it out on your own database. Create the Transactions Table with the structure and sample data given above. Work through the problem, then compare your solution with the examples provided.

And if you solve it differently but still arrive at the correct result, please share your approach — it could give everyone a fresh perspective!

One-step solution

Copy the following SQL String into the SQL Editing Window of a new Query and save it with a name you prefer.

SELECT Transactions.Location,
 Sum(IIf([type]="E",-[Amount],[Amount])) AS Profit
FROM Transactions
GROUP BY Transactions.Location;

Open the Query in Normal View, and you will see the result of the Query as shown in the second Image given above.

The Two-Step Solution.

Create a Query with the following SQL String and name the Query as Query_Step1.
```
SELECT Transactions.*,
 IIf([Type]="E",-[Amount],[Amount]) AS Amt
FROM Transactions;
```
The Query output will look like the image given below:
Right-click to open the large image in a New Tab/Window.
Tip: The Query Amt Column is formatted to display Negative Values in Color and in brackets. Open the Query in Design View. Highlight the Column and click the Properties Toolbar Button or select Properties from the View Menu to display the Property Sheet of the Column. Type 0.00;[Red](0.00);0.00;0.00 into the Format Property and save the Query. If you open the Query now, the output will appear in color.

The Format Property Values are expressed in four segments separated by semicolons. The first segment dictates how to display positive values, the second segment stands for Negative values, the third segment says what to display when the field value is zero, and the fourth segment displays zero when the Field/Column contains Null. The third and fourth segments can be set as a literal string, 0.00;[Red](0.00); "Zero"; "Null" to display these values rather than 0.00. You can set the Field Format Property values on the Table Structure, on Forms, or on Reports. You don't need to use all four segments of the Format Property Values all the time.
Create another Query with the following SQL String, using Query_Step1 as Source Data, and save the Query with the name PL_Report:
```
SELECT Query_Step1.Location,
 Sum(Query_Step1.Amt) AS Amount
FROM Query_Step1
GROUP BY Query_Step1.Location;
```
Open the PL_Report Query in the normal view, and the result will be the same as the second image given above.

Three-Step Solution.

If you need more clarity on how the results are formed in the final report, then try this method.

You can use the first Query under the two-step solution as the first step here.
Use the following SQL String, which uses the first step Query's output as source data, and create the second step Query with the name Query_Step2:
```
SELECT Query_Step1.Location,
 Query_Step1.Type,
 Sum(Query_Step1.Amt) AS Amt
FROM Query_Step1
GROUP BY Query_Step1.Location, Query_Step1.Type
ORDER BY Query_Step1.Location, Sum(Query_Step1.Amt) DESC;
```
The second Query result is given below.
Right-click to open the Large Image in a New Tab/Window.
Create a third Query for the final result, with the SQL String given below, using the second step Query (Query_Step2) as Input:

SELECT Query_Step2.Location,
 "Profit/Loss" AS Description,
 Sum(Query_Step2.Amt) AS Amt
FROM Query_Step2
GROUP BY Query_Step2.Location;

The Query output is below with a three-column result, replacing the Type Column with Description.

Right-click to open the large image in a New Tab/Window.

Doing It Differently

How about doing it differently and arriving at the following Result with Queries in two Steps?

Right-click to open the Large Image in a New Tab/Window.

Create the first Query Method2_1 with the following SQL String:

TRANSFORM Sum(Transactions.Amount) AS SumOfAmount
SELECT Transactions.Location
FROM Transactions
GROUP BY Transactions.Location
PIVOT IIf([Type]="R","Revenue","Expenses");

Create the Report Query Method2_2 with the following SQL String that uses Method2_1 as Source:

SELECT Method2_1.Location,
 Method2_1.Revenue,
 Method2_1.Expenses,
 [Revenue]-[Expenses] AS [Profit/Loss]
FROM Method2_1;

Open the Method2_2 Query in Normal View and check the output carefully.

As demonstrated in the examples above, there are often multiple ways to approach a problem in MS Access and still arrive at the same result.

If your solution requires several steps to the final Report output, it’s good practice to create the process step flowchart. This way, if you later notice an issue with the Report, you can follow the flowchart to backtrack and identify the source of the error.

A sample Flow Chart of the Three-Step Solution is given below:

If the Transactions Table has Year and Month Fields and both locations have January and February 2009 data records, how can you create the Month-wise Report?

Try it out on your own, and check it out with my examples next week. A sample image of the output is given below for reference.

Form Bookmarks And Data Editing-3

a.p.r. pillaiNovember 09, 2009Forms, msaccess controls No comments

Continued on Form Bookmarks.

This is the third part of our discussion on Form Bookmarks, revisiting the records visited earlier. The links to the earlier Articles are given below:

The discussion on Bookmarks would be incomplete without exploring how to search for and locate records using the RecordsetClone property.

In our earlier examples, we used the Find control (Ctrl+F or Edit → Find...) to locate a record and, once found, stored its Bookmark in an array variable for later use.

This time, we’ll take a different approach — we’ll find the record and bring it directly into Form view using the Bookmark of the Form’s RecordsetClone.

For this method, we will use an Unbound TextBox to enter the search key and a CommandButton click to find the record. To keep the VBA Code simple, we will use the Customers table rather than the Order Details table because the Order Details Table has several records with the same OrderIDs.

When we use the Order Details table, we need to establish a relationship between ProductID and OrderID to create a unique Key and find a specific record, among several records, within the same OrderID. This method was already used in the earlier example, which displays all the records we have retrieved and edited.

Review of Bookmarks.

As mentioned in earlier articles, when the Form opens with a Table, Query, or SQL statement as its Record Source, MS Access assigns each record displayed on the Form a unique identification tag called a Bookmark.

We can create a copy of this recordset in memory—called a RecordsetClone—and work with it independently of the Form. Using this RecordsetClone, and with the Form’s Bookmark attached to each record, we can locate any desired record through VBA code using a specific search key value. Once the target record is found in memory, you can read its Bookmark and assign it to the Form’s Bookmark property, making that record the one currently displayed on the Form.

However, it’s important to note that this relationship works only in one direction: you cannot read the Form’s Bookmark property value and use it to locate the same record within the RecordsetClone.

The Ease of Use.

From the User's Point of View, enter the Search Key-Value (CustomerID) into the Unbound TextBox and click the CommandButton next to it to find that record and bring it up on the Form.

Look at the sample VBA Code given below that runs on the CommandButton Click (with the Name cmdFind) after setting the Search Key-Value (CustomerID) in an Unbound Text Box with the name xFind.

Private Sub cmdFind_Click() 
Dim m_Find, rst As Recordset
m_Find = Me![xFind]
Set rst = Me.RecordsetClone
rst.FindFirst "CustomerID = '" & [m_Find] & "'"
If Not rst.NoMatch Then 
     Me.Bookmark = rst.Bookmark 
End If

End Sub

The line that reads Set rst = Me.RecordsetClone copies the Form's Recordset into the Recordset Object Variable rst in Memory, and the next line runs the FindFirst method of the Recordset Object to search and find the record with the given CustomerID Value.

In the next three lines, we are testing whether the 'rst.FindFirst' method was successful in finding the record or not. If found, then the Bookmark of the current record in the Recordset is transferred to the Bookmark Property of the Form to make that record Current on the Form.

There is an article on this method posted a few months ago, titled: Animating Label on Search Success. You may visit that Page to copy the complete VBA Subroutine code segment given above and try it out.

You must import the Customers Table and Customers Form from the C:\Program Files\Microsoft Office\Office11\Northwind.mdb sample Database and modify the Form to add a Text Box and a Command Button at the Footer of the Form.

The 'rst.FindFirst' statement finds the record and makes it current on the Form; a Label at the bottom of the TextBox will flash a few times with a message indicating that the search operation was successful and the record is current on the Form. If it is not successful, then the Label will flash a few times with the message: Sorry, not found.

This method, added to the above program, gives the User a quick indication of whether the search was successful. To visit the Page and try it out, click here.