PrimaryKey usage with Many Fields

PrimaryKey usage with Many Fields.

When designing a master table in Microsoft Access, ensure that unique values are in a column to make data retrieval easier. For example, in the Employees table of the Northwind.mdb sample database, each employee is identified by a unique employee code. But who ensures that the uniqueness of the values entered into the employee-code field?

It is possible to validate user input using VBA before saving the record. Database systems already have this functionality of a primary key index. The primary key prevents duplicate values and organizes the data automatically in ascending or descending order. 

Usage of Indexes in Programs.

We use these field values in programs to retrieve the specific information very fast.  Let us write a small VBA Routine to see how this is used in programs.

Public Function PrimaryKey_Example1(ByVal EmpCode As Integer)
Dim db As Database, rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.Index = "PrimaryKey" 'activate the Index on Employee Code

rst.Seek "=", EmpCode 'we are looking for the record of Employee Code provided
'Let us test whether the search for Employee Code was successfull or not
If Not rst.NoMatch Then
    MsgBox "EC: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name]
Else
    MsgBox "EC: " & EmpCode & " Not found!"
End If

Set rst = Nothing
Set db = Nothing

End Function 

The statement rst.Index = "PrimaryKey" activates the index named PrimaryKey. A primary key index can include more than one field, and a table may contain several indexes within the Indexes collection, each defined with different field combinations. If a single field is not sufficient to ensure uniqueness, additional related fields can be included in the index. You can activate the appropriate index based on how you want the data organized before continuing with the processing steps.

The Primary Key Field contents don't always need to be numbers only; they can be any value such as FirstName or LastName fields, or both, or any combination of field types: text, date, number, etc., except the Memo field.  You can give any suitable name to the Index in the Index Name column and use it to activate a specific Primary Index.

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

The Recordset's Seek() method is used for search operations on the Table.  One of the Indexes should be active before the Seek() operation can be executed. 

In the above example, the rst.Seek "=", the Empcode statement checks for the Employee Code passed to the Index_Example1() function.  The statement looks for an exact match ("=" ) very quickly.

When the Index is defined with more than one field, then search keys must be separated with commas in the Seek() method.  Let us modify the above program to use multiple values for the Index Keys in the Seek method, assuming that FirstName and LastName fields are the members of MyIndex.

Index having more than one Field Value.

Public Function PrimaryKey_Example2(ByVal strFirstName As String, ByVal strLastName As String)
Dim db As Database, rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.Index = "MyIndex" 'activate the Index

'Search for the Employee record
rst.Seek "=", strFirstName, strLastName

'Let us test whether the search for the Employee was successfull or not
If Not rst.NoMatch Then
    MsgBox "Employee: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name]
Else
    MsgBox "Employee: " & strFirstName & " " & strLastName & " Not found!"
End If

Set rst = Nothing
Set db = Nothing

End Function

• Learn the Binary Numbering System
• Scaling the Chart Object
• User and Group Check
• User-Defined Data Type
• Budgeting and Control

Read More

Budgeting and Control

Budgeting and Control.

The local Charity Organization for Children allocates funds for disbursement under various categories to eligible individuals or entities. The Accounts Section oversees these disbursement activities and ensures that the total payments made under each category do not exceed the allocated budget.

We have been asked to develop a computerized system to monitor the payment activity and verify that the cumulative value of all payments for a given category remains within the approved budget limit.

Below is a sample screen used for recording payment details:

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

As shown in the screen above, a Budget Amount of $10,000 has been allocated to the Poor Children’s Education Fund. This amount is distributed to eligible individuals or deserving institutions after careful evaluation of their cases. The payment records are entered in the datasheet subform below. Both the Main Form and the Subform are linked through the Category Code, an AutoNumber field in the main table.

When a new record is entered in the subform with a payment amount, the program calculates the total of all payment records, including the current entry, and compares it against the budget amount on the main form. If the total payment amount exceeds the allocated budget, an error message is displayed. In such cases, the program automatically deducts the excess amount from the current payment value.

After this adjustment, the focus is set to the Amount field, allowing the User to review the correction and take appropriate action if necessary.

In this example, users are not restricted from modifying the Budget Amount. However, the field can be locked immediately after a new main record is created for the budget value. If authorized modifications are required at a later stage, special access rights can be granted to designated Users through Microsoft Access Security features. For the time being, let us keep aside the security aspect; let us take a closer look at the design and implementation of the datasheet subform and the associated procedures.

An image of the Payment Record Sub-Form Data Sheet Design View is given below:

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

A TextBox with an Active Record not yet saved.

We created a Text Box in the Subform Footer Section with an expression to calculate the total of all payment records for the current category, excluding the current new record. This happens because the Sum() function does not include the new record value until it is saved in the table.

For example, the Text Box expression:

=Sum([Amt])

will correctly total all saved records. Although this control is not visible in Datasheet View, it can still be referenced in VBA procedures. (For additional techniques with Datasheet Forms, see the article Event Trapping and Summary on Datasheet.)

To include the value of the current (unsaved) record in the total, we can read it directly from the field (Me![Amt]) and add it to the result of the Sum() function. This gives us the Total of all disbursement records, including the current entry.

We can then compare this calculated total against the Budget Amount on the main form before accepting the new record. If the total exceeds the budget, the program can alert the user. This ensures that no payment entry pushes the cumulative disbursement beyond the allocated amount.

The Sub-Form Module Code.

The VBA Program Code written in the Sub-Form Module is given below:

Option Compare Database
Option Explicit
'Gobal declarations
Dim Disbursedtotal As Currency, BudgetAmount As Currency, BalanceAmt As Currency
Dim errFlag As Boolean, oldvalue As Currency

Private Sub Amt_GotFocus()
'Me!TAmt is Form Footer Total except the new record value
Disbursedtotal = Nz(Me!TAMT, 0)
BudgetAmount = Me.Parent!TotalAmount
oldvalue = Me![Amt]
End Sub

Private Sub Amt_LostFocus()
Dim current_amt As Currency, msg As String, button As Long

On Error GoTo Amt_LostFocus_Err
Me.Refresh
'add current record value to total and cross-check
'with main form amount, if the transactions exceed
'then trigger error and set the focus back to the
'field so that corrections can be done
current_amt = Disbursedtotal + Nz(Me!Amt, 0)
BalanceAmt = BudgetAmount - current_amt
errFlag = False
If BalanceAmt < 0 And oldvalue = 0 Then
    errFlag = True
    button = 1
        GoSub DisplayMsg
ElseIf oldvalue > 0 Then
    current_amt = (Disbursedtotal - oldvalue) + Nz(Me!Amt, 0)
    BalanceAmt = BudgetAmount - current_amt
    If BalanceAmt < 0 Then
        errFlag = True
        button = 1
          GoSub DisplayMsg
    End If
Else
    Me.Parent![Status] = 1
End If

Amt_LostFocus_Exit:
Exit Sub

DisplayMsg:
    msg = "Total Approved Amt.: " & BudgetAmount & vbCr & vbCr & "Payments Total: " & current_amt & vbCr & vbCr & "Payment Exceeds by : " & Abs(BalanceAmt)
    MsgBox msg, vbOKOnly, "Amt_LostFocus()"
Return


Amt_LostFocus_Err:
MsgBox Err.Description, , "Amt_LostFocus()"
Resume Amt_LostFocus_Exit
End Sub

Private Sub Form_Current()
Dim budget As Currency, payments As Currency

On Error Resume Next

budget = Me.Parent.TotalAmount.Value

payments = Nz(Me![TAMT], 0)

If payments = budget Then
 Me.AllowAdditions = False
Else
  Me.AllowAdditions = True
End If

End Sub

Private Sub Remarks_GotFocus()
If errFlag Then
  errFlag = False
  Me![Amt] = Me![Amt] + BalanceAmt
  BalanceAmt = 0
  Me.Parent![Status] = 2
  Me.Amt.SetFocus
End If

End Sub

Performing Validation Checks.

During data entry in the Payment Subform, if the cumulative value of all payment records reaches the allocated Budget Amount, the form will prevent adding any more payment records. However, existing payment records may still be opened and edited.

Similarly, when any Budget Category record becomes current on the Main Form, the program checks whether the total of its related payment records already equals the budgeted amount. If this condition is met, the Payment Subform is locked against new entries, but existing payment records remain editable.

The following VBA procedure, written in the Main Form’s module, enforces this rule and ensures that users cannot enter payment records once the budget is fully utilized:

Main Form Module Code.

Option Compare Database

Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub Form_Load()
DoCmd.Restore
End Sub

Private Sub Form_Current()
Dim budget As Currency, payments As Currency
Dim frm As Form
On Error Resume Next

Set frm = Me.Transactions.Form
budget = Me!TotalAmount
payments = Nz(frm![TAMT], 0)

If payments = budget Then
 frm.AllowAdditions = False
Else
  frm.AllowAdditions = True
End If

End Sub

Demo Database Download.

Click the following link to download a Demonstration Database with the above Code.

Download Demo BudgetDemo.zip

• Creating a User Account with VBA
• Microsoft Date/Time Picker Control
• Form and Report Open Arguments
• Indexing and sorting with VBA
• Data Upload Controls

Read More

Indexing and Sorting with VBA

Indexing and Sorting with VBA.

A table is usually created with a Primary Key or an Index to organize its records in a specific order for viewing or processing. A Primary Key or Index can include one or more fields to ensure that each record has a unique key value, especially when a single field alone cannot guarantee uniqueness.

For example, if you open the Employees table in the Northwind.mdb sample database (located in *C:\Program Files\Microsoft Office\Office11\Samples*), and switch to Design View, you’ll see that the EmployeeID field is defined as the Primary Key.

To create an Index manually and define it as a Primary Key:

1. Open the Table in Design View.

2. Click on the left side of the Field Name to select it.

3. Click on the Indexes Toolbar Button.

4. You may give any suitable name in the Index Name Field, replacing the PrimaryKey text, if you would like to do so.

If the values in the selected field are not unique, you can include additional fields—up to a maximum of ten—to create a composite key that ensures uniqueness for the Primary Key.

To do this, click and drag over the adjoining fields to select them, or hold down the Ctrl key and click individual fields to select non-adjacent ones.

This process creates a Primary Key Index for the table. You can define multiple indexes in a table, but only one Primary Key can be active at any given time.

Creating an Index with VBA.

We can activate an existing index in a table or create a new one through VBA and use it for data processing.

In this exercise, we’ll learn how to:

1. Create a new index named myIndex for a table through VBA.

2. Activate the required index for data processing.

3. Delete the index once processing is complete.

Before creating a new index, we’ll first check whether it myIndex already exists in the table’s Indexes collection.

• If it exists, we’ll activate it.

• If not, we’ll create it, activate it, and proceed with processing.

For this example, we’ll use the Orders and Order Details tables from the Northwind.mdb sample database. The Order Details table will be organized in Order-Number Sequence. The total value of all items for each order is calculated and updated in the corresponding record of the Orders table.

The Data Processing Steps

The following are the data processing steps, which we follow in the VBA Routine to update the Orders Table with order-wise Total Value from the Order Details Table:

1. Open the Orders Table for Update Mode.

2. Open Orders Details Table for Input.

3. Check for the Index name myIndex in the Order Details Table. If found, then activate it; otherwise, create myIndex and activate it as the current Index.

4. Initialize the Total to Zero.

5. Read the first record from the Order Details Table.

6. Calculate the Total Value of the item using the Expression: Quantity * ((1-Discount%)*UnitPrice).

7. Add the Value to the Total.

8. Read the next record and compare it with the earlier Order Number. If the same, then repeat steps 6 and 7 until the Order Number changes or there are no more records to process from the Order Details Table.

9. Find the record with the Order Number in the Orders Table.

10. If found, then edit and update the Total to the TotalValue field in the Orders Table.

11. Check for the End Of File (EOF) condition of the Order Details Table.

12. If False, then repeat the Process from Step 4 onwards; otherwise, Close files and stop running.

Prepare for a Trial Run.

1. To try the above method, Import Orders and Order Details Tables from 'C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb' (Access 2003) or 'C:\Users\User\My Documents\Northwind 2007.accdb' (Access 2007; if not available, then you must create from Local Templates)

2. Open the Orders Table in Design View.

3. Add a new Field named Total Value with a Numeric (Double) data Type in the Orders Table.

   You may display the Index List of this Table to view its Primary Key Index on the Order ID field.

4. Save the Orders Table.

5. Open the VBA Editing Window (Alt+F11).

6. Create a new Standard Module from the Insert Menu.

7. Copy and Paste the following VBA Routine and save the Module.

   The CreateIndex() Function.

   Public Function CreateIndex()
   Dim db As Database, fld As Field, tbldef As TableDef
   Dim idx As Index, rst As Recordset, PreviousOrderID As Long
   Dim CurrentOrderID As LongDim xQuantity As Long, xUnitPrice As Double
   Dim xDiscount As Double, Total As Double, rst2 As Recordset
   
   On Error Resume Next
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Order Details", dbOpenTable)
   'Check for presence of myIndex, if found set as current
   rst.Index = "myIndex"
   If Err = 3800 Then
   'myIndex not found
       Err.Clear
       GoSub myNewIndex
   End If
   
   On Error GoTo CreateIndex_Err
   
   Set rst2 = db.OpenRecordset("Orders", dbOpenTable)
   rst2.Index = "PrimaryKey"
   PreviousOrderID = rst![Order ID]
   CurrentOrderID = PreviousOrderID
   Do Until rst.EOF
       Total = 0
       Do While CurrentOrderID = PreviousOrderID
           xQuantity = rst![quantity]
           xUnitPrice = rst![unit price]
           xDiscount = rst![discount]
   
           Total = Total + (xQuantity * ((1 - xDiscount) * xUnitPrice))
           rst.MoveNext
           PreviousOrderID = CurrentOrderID
           If Not rst.EOF Then
               CurrentOrderID = rst![Order ID]
           Else
               Exit Do
           End If
       Loop
       rst2.Seek "=", PreviousOrderID
       If Not rst2.NoMatch Then
           rst2.Edit
           rst2![totalvalue] = Total
           rst2.Update
       End If
       PreviousOrderID = CurrentOrderID
   Loop
   
   rst.Close
   rst2.Close
   
   'Delete temporary Index
   Set tbldef = db.TableDefs("Order details")
   tbldef.Indexes.Delete "myIndex"
   
   CreateIndex_Exit:
   Exit Function
   
   myNewIndex:
   rst.Close
   Set tbldef = db.TableDefs("Order Details")
   Set idx = tbldef.CreateIndex("myIndex")
   
   Set fld = tbldef.CreateField("Order ID", dbLong)
   idx.Fields.Append fld
   Set fld = tbldef.CreateField("Product ID", dbLong)
   idx.Fields.Append fld
   tbldef.Indexes.Append idx
   tbldef.Indexes.Refresh
   Set rst = db.OpenRecordset("Order Details", dbOpenTable)
   rst.Index = "myIndex"
   Return
   
   CreateIndex_Err:
   MsgBox Err.Description, , "CreateIndex()"
   Resume CreateIndex_Exit
   
   End Function

8. Click somewhere in the middle of the VBA Routine and press F5 or click the Run Command Button to execute the Code and update the Orders Table.

At the beginning of the code, we attempt to activate one of the indexes (myIndex) in the Order Details table. Since myIndex has not yet been created, this action triggers an error. The error is trapped, and control is passed to a subroutine that creates myIndex and adds it to the table’s Indexes collection. The new index is then activated in preparation for data processing. 

The next steps calculate Order-wise Total Values and update them in the Orders Table.

At the end of the process, myIndex is deleted from the Indexes Collection of the Order Details Table.

Earlier Post Link References:

• Creating Using Form Custom Property
• MS-Access and Data Processing-2
• MS-Access and Data Processing
• Form Bookmarks and Data Editing-3
• Form Bookmarks and Data Editing-2

Read More

Data Upload Controls

Data Upload Controls.

In some projects, we need to regularly import data from external sources such as dBase, Excel, or flat files like CSV and text. These external files can remain linked to the project, allowing their data to be added to a local Microsoft Access table for reporting purposes.

For example, assume that we have an MS Access application that generates monthly business profitability reports. To prepare these reports, we need to upload new data each month from a LAN location, where the file is updated and replaced from a remote source in one of the supported formats.

Our Application has a mechanism to identify when the existing linked file on the LAN Server is replaced with a new file having fresh data. When a new data file is introduced on the Server and replaces the old one, the System detects the new data and enables the CommandButton. This CommandButton is disabled after uploading the data and remains disabled till new external data file overwrites the old one. 

When the MS Access application opens, it compares the previously stored file parameters with those of the current file on the server. If the parameters match, the application assumes that the data has already been uploaded and keeps the DataUpload Command Button disabled. If the parameters do not match, it assumes that new data is available and enables the DataUpload Command Button to allow the upload process.

So, how do we detect new data in the attached file? Depending on the file type, different approaches can be used. One common method is to check the continuity of a control value, such as an Invoice Number, Last Receipt Date, or any other unique identifier that reliably distinguishes new records. These control values from the last uploaded data can be compared with the corresponding values in the attached file. If the values match, we can assume that the data has already been uploaded; otherwise, we can proceed to upload the new records.

To perform this verification, you can design a few queries to extract and filter these key values from both sources, then use a VBA routine to compare them and control the next steps in the upload process.

However, I use a simpler method to check for the new data in the attached file. Before explaining that, we need to consider a few important factors.

The Access Application's back-end Database and the upload data source both must be on the LAN Server. Only one authorized Front-End Database user is permitted to execute the upload process.

Taking these considerations into account, we must design a reliable, controlled method to ensure that data is uploaded correctly and prevent duplication.

External Data Source Files.

I have several applications that upload data from various sources — including IBM AS400 systems, dBase, Excel, and even AS400 report spool files. Over time, I’ve experimented with different methods to detect new information in these files, using queries that compare control data from internal database tables and linked tables.

You might be wondering how I handle the AS400 report spool files, which often have hundreds of pages saved directly to the LAN by the EDP department. These files cannot be linked directly to the database because they don’t follow a proper table structure — except for the detail lines that contain the actual data.

I have developed VBA procedures that will read the spool file line by line and discard unwanted lines, headers, footers, underlines, blank lines, etc., and take only the data lines, cut into text fields in a Table initially before converting each field value into its respective data types and writing it out into a new table.

But the question remains: how can we track the presence of a new report spool file that cannot be directly attached to the database? The solution is quite simple. At the end of each upload operation, I create a control file by copying the first 50 lines of the current spool file. Whenever the application is opened, a small routine compares the first 50 lines of both the current spool file and the control file. If no differences are found, it means the data has already been uploaded into the system. If any variation is detected, the system recognizes it as a new file and prepares to upload the fresh data.

A Common Simple Method is suitable for all Types of Files.

After experimenting with several methods for different file types, I realized the need for a simple, universal approach that could work for all kinds of files—whether attached to the system or not. I eventually developed such a method, which I’m sharing below for your use, should you find it helpful.

We need a small table with the following Fields:

Field Name	Data Type	Field Size
FileLength	Long Integer
FileDateTime	Date/Time
UserName	Text	25
UploadDate	Date/Time
FilePath	Text	255

The sample table in Datasheet View:

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

When the file contents are uploaded, we record some basic information about the attached file—such as its size (in bytes) and its last modified date and time. In addition, we store the name of the user (if Microsoft Access security is implemented) authorized to run the upload process, along with the date and time of the last upload event.

We can read the attached file size in bytes using the Function: FileLen(PathName), and the File's last modified Date and Time can be obtained with the Function FileDateTime(PathName). After the data upload, these values can be updated in the table above to cross-check the external file to check the presence of new data. If needed, we can set the attached file's Read-Only attribute ON using the Function SetAttr(PathName, vbReadOnly), so that the file can be protected from inadvertent changes. It can be reset to Normal with SetAttr(PathName, vbNormal).

A program must be run immediately after the Main Switchboard Form is opened, and cross-check the file size and the File Date/Time recorded in the table with the attached file's attributes. If they are different, then new data has arrived and enabled the Upload CommandButton; the new data can be uploaded.

However, when the application is in open state, and the attached file is replaced by the provider with a new one, the Upload button will remain disabled because the status-checking routine runs only when the main switchboard is opened. Instead of requiring the user to close and reopen the application, a standard but inconvenient procedure, we can add another command button labeled Refresh. When the user clicks this button, the program can recheck the file attributes and enable the Upload button if a new file is detected.

A sample VBA Routine is given below that reads the information from the table and cross-checks with the attributes of the attached file, enabling/disabling the Upload Command Button.

Alternatively, we can run a Timer-Interval Subroutine (at a 1-hour or longer interval) to check the presence of a new source file when the Access Application is active.

The UploadControl() Function Code.

Public Function UploadControl(ByVal frmName As String)
'------------------------------------------------------
'Author   : a.p.r. pillai
'Date     : January-2010
'Remarks  : Data Upload control Routine
'         : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------
Dim frm As Form, lnglastFileSize, dtlastModified, txtFilePath
Dim lngExternalFileSize, dtExternalModified, authUser
Dim tblControl As String, cmdCtrl As CommandButton

tblControl = "UploadCtrl"
authUser = "LizzaMinnelli"
Set frm = Forms(frmName)
Set cmdCtrl = frm.Controls("cmdUpload")

'Read last recorded information from the Control Table
lnglastFileSize = DLookup("FileLen", tblControl)
dtlastModified = DLookup("FileDateTime", tblControl)
txtFilePath = DLookup("FilePath", tblControl)

'Get the External File information
lngExternalFileSize = FileLen(txtFilePath)
dtExternalModified = FileDateTime(txtFilePath)

If (lngExternalFileSize <> lnglastFileSize) And (dtlastModified <> dtExternalModified) Then
    If CurrentUser = authUser Then
        cmdCtrl.Enabled = True
    Else
        cmdCtrl.Enabled = False
    End If
End If

End Function

The Main Switch Board, which has a Command Button with the name cmdUpload, should call the above Program through the Form_Current() Event Procedure of the Form, passing the Form Name as Parameter, like the following example:

Private Sub Form_Current()
    UploadControl Me.Name
End Sub

If the uploading authorisation is assigned to a particular User, then the Current User's User ID (retrieved with the function CurrentUser()) can also be checked with the UserName Field Value before enabling the Command Button cmdUpload.

• Run Slideshow when Form is idle
• Filter Function Output in ListBox-2
• Filter Function Output in ListBox
• Dynamic List Box Contents
• Office Assistant and MsgBox Menus-3

Read More

Text Search Filter Web Style

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 across web pages and displays the matching results on the screen.

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

Or

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.

1. Import the Customers Table from: 
   'C:\Program Files\Microsoft Offce\Office11\Samples\Northwind.mdb sample database.'

2. Create a new Query based on the Customers table including all its fields.
   Then, add a calculated column named FilterField to the Query.

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

    Display the VBA Debug Window (Ctrl+G).

    Run the Program from the Debug Window by typing the following statement and pressing the Enter Key:

   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 Large Image in New Tab/Window.

   
   

   Design a Form.

4. Create a Tabular Form (continuous form) using myQuery as Record Source and save the Form with the name frmMyQuery.

5. Open the form frmMyQuery in the Design view.

6. Select the FilterField Column and display its Property Sheet (View ->Properties)

7. Change the Visible Property Value to No.

8. Make the FilterField column size very small on the Form (it is not visible on normal view) and resize other Columns to view their contents properly.

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

10. Expand the Form Header Section and drag the column headings down so that there is enough space to draw a TextBox and a Command Button beside it.

11. Create a Text Box above the column headings.

12. Display the Property Sheet of the Text Box (View ->Properties).

13. Change the following Property Values as given below:

    • Name = txtSearch
    • Width = 3"

14. Change the Caption of the Child Label of the Text Box to Search Text (delimiter:, or +):

15. Create a Command Button to the right of the Text Box and change the following Property Values:

    • Name = cmdGo
    • Caption = GO>
    
    

    The Form's Class Module Code.

16. Display the Code Module of the Form (View -> Code).

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

18. Save the Form and open it in Normal View.

19. Type some Text/Numbers/Phrases separated with commas (,) or plus symbol (+), 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.

20. However, spaces within phrases (embedded spaces) in the search text will remain intact.

21. Click on the GO> Command Button to search for the given text in fields/records and filter those records on the Form.

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

• Dynamic List Box Contents
• Office Assistant and MsgBox Menus-3
• Office Assistant and MsgBox Menus-2
• Office Assistant and MsgBox Menus
• Color and Picture in MsgBox

Read More

Filter with BuildCriteria Function

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

1. Press Alt+F11 to display the Visual Basic Editing Window.

2. Press Ctrl+G to display the Debug window.

3. Type the following example expressions and press the Enter key to display the output:

Sample Run of 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.

1. Import the Orders

   Table from the C:\Program Files\Microsoft Office\Officell\Samples\Northwind.mdb sample database.

2. Select the Orders Table and select Form from the Insert Menu.

3. Select Auto Form: Tabular to create a Form and save it with the name Orders.

4. Open the Orders Form in Design View.

5. Expand the Form Header Area and drag all the Field Headings down to get enough room to create a Command Button above the Field Headings.

6. Display the Toolbox, if it is not visible (View ->Toolbox).

7. Select the Command Button Tool and create a Command Button on the Form Header.

8. Display the CommandButton's Property Sheet (View -> Properties).

9. Change the Name Property Value to cmdFilter and change the Caption Property Value to OrderID Filter.

10. Display the Code Module of the Form (View -> Code).

    Run it with a Button Click.

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

12. Save and Close the Form.

13. You may click on the OrderID Filter Command Button and enter any of the examples (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 on the Form. Ask the User to enter conditions for two different fields separately, and run the BuildCriteria() Function also 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 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 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 Orderld and ShipName). They can use only one Field for entering Criteria and ignore the other.

• Office Assistant and MsgBox Menus-3
• Office Assistant and MsgBox Menus-2
• Office Assistant and MsgBox Menus
• Color and Picture in MsgBox
• Microsoft Excel Power in Access

Read More

Save User Specific Parameter values

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, preserving them for future 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 the parameter control text boxes 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 text box 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 someone else’s 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 who closes 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 as well.

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 when a new user joins the workgroup and creates a custom property for him too. 

The Custom Property Creation Code.

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

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

   Revised VBA Code - Form Load.

5. Copy and paste the following Sub-Routines 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

6. 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 custom properties are not created for all users in the database—they are only created 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.

• Office Assistant and MsgBox Menus-2
• Office Assistant and MsgBox Menus
• Color and Picture in MsgBox
• Microsoft-Excel Power in Access
• Unsecured Database and Users-Log

Read More

Creating Using Form Custom Property

Creating Using 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 Large Image in 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 one.

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 open, 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 out is Null.

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

We can store the values entered in the Unbound Text Box controls of the Parameter Form within Custom Properties that we create directly on the Form.

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:

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

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

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

1. To try out this method, open a new Form and create two Unbound TextBoxes.

2. Click on the first TextBox and display its Property Sheet (View -> Properties).

3. Change the Name Property Value to fromDate.

4. Change the Name Property Value of the second TextBox to ToDate.

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 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, confirming their successful creation.

   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 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 of their values 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

Read More

MS-Access And Data Processing-2

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 such an issue here so that you understand what I mean by the hurdles involved in creating the final report. Complex data processing steps like these can be automated by sequencing each step in a macro and running that macro 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, leading 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 Large Image in New Tab/Window.

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

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

We can transform the sample data given 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 in 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];

1. Copy and paste the above SQL String into the SQL Editing Window of a new Query and save it with the name Method2_1.
2. 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;
   

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

4. 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:

1. Create a second Report Table with Revenue and Expenses Fields for all twelve months.

2. Change the second Query created above (Method2_2) to an append query and add the output data of available months into the Report Table.

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

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

1. 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 Large Image in New Tab/Window.

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

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

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

3. 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;

4. 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;
   

5. 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 Large Image in New Tab/Window.

   
   The Report in Print Preview.

   

   Right-click to open Large Image in 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.

6. 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 there are only two 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 Large Image in 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 Command Button — 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.

• Color and Picture in MsgBox
• Microsoft Excel Power in Access
• Unsecured Database and Users-Log
• MS-Access Report and Page Total
• Detail and Summary from the Same Report

Read More