Creating Using Form Custom Property

Introduction

Normally, parameter controls are provided to users for entering data filter criteria when generating MS Access reports. The parameter control fields can be directly 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 gives users the flexibility 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, containing the filtered data according to the Date From and Date To values entered 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 the next time the form is open.

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 lead to record edit-lock errors, or worse, the parameter values may overlap or get overwritten. Consequently, the reports printed by different users may be using incorrect or mixed-up filter criteria.

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 any possibility of overlapping or clashing parameter values.

The only minor drawback with 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 the 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, all 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 have already published an article earlier on how to open a Report without triggering this #Error condition when the Report Source Table or Query happens to be empty.

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 act like hidden variables attached to the Form itself. They can only be created, modified, or read through VBA, and are not visible in the usual Property Sheet that we work with 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 Text Boxes.

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

3. Change the Name Property Value to fromDate.

4. Change the Name Property Value of the second Text Box to to-Date.

5. Close and save the Form with the name RptParameter.

6. Display the VBA Editing Window (Alt+F11), and copy and paste the following VBA Code into the Standard Module. If necessary, create a new Module (Insert -> Module).

   VBA Code to Create Custom Property

   Public Function CreateCustomProperty()
   Dim cdb As Database, doc As Document
   Dim prp As Property
   
   Set cdb = CurrentDb
   Set doc = cdb.Containers("Forms").Documents("RptParameter")
   Set prp = doc.CreateProperty("DateFrom", dbDate, Date)
   doc.Properties.Append prp
   
   Set prp = doc.CreateProperty("DateTo", dbDate, Date)
   doc.Properties.Append prp
   doc.Properties.Refresh
   
   Set prp = Nothing
   Set doc = Nothing
   Set cdb = Nothing
   
   End Function

7. Click anywhere 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 Text Boxes.

    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

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 approach the same problem from different angles to arrive at the same result. Reports are the primary output component that is delivered to the User with critical information for analyzing business activities and making informed business decisions. Transforming raw data into a meaningful form as a shareable 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 Method 2_2 Query and view the output.

Even though we can get the desired results using the above 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) as 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 type 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.

   NB: 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 dynamically, ensuring that the headings automatically update 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 Profit and Loss (P&L) Report preparation process so that the report automatically reflects 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.

Actions 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

MS-Access and Data Processing

Introduction

Designing Forms or Reports in Microsoft Access can be learned quickly by mastering the Design Tools available, even without delving deeply into programming. However, data processing is a different challenge altogether — it demands flexibility and varies from project to project, making it impossible to standardize.

The data table design is crucial and must be carefully planned to ensure easy information retrieval and to prevent data duplication. Establishing proper relationships between tables is equally important to join related information together.

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 all 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 example 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 to insert these codes into the appropriate fields, ensuring data consistency and eliminating redundancy, as demonstrated in the corrected design below.

Right-click to open Large Image in 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 separately 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 Large Image in 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 I know you’re someone who understands MS-Access.

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, and 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

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

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

1. 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 Large Image in 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 on 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 with a literal string, like 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.

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

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

1. You can use the first Query under the two-step solution as the first step here.
2. 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 Large Image in New Tab/Window.

3. 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 output of the above Query is given below with a three-column result, replacing the Type Column with Description.

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

1. 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");
   

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

3. 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 reach the final Report output, it’s a good practice to create a Flow Chart of the process steps. 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 too, and both locations have January and February 2009 data in them, then how will you create the Report Month-wise?

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.

• Microsoft Excel Power in Access
• Unsecured Database and Users-Log
• MS-Access Report and Page Total
• Detail and Summary from the Same Report
• Hiding Report Lines Conditionally-3

Form Bookmarks And Data Editing-3

Continued on Form Bookmarks.

This is the continuation of our discussion on the usage of Form Bookmarks to revisit the records that we have already visited earlier. The links to the earlier Articles are given below:

1. Form Bookmarks And Data Editing
2. Form Bookmarks And Data Editing-2

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 view on the Form by using the Bookmark of the form’s RecordsetClone.

For this method, we will use an Unbound Text Box to enter the search key value and a Command Button click to find the record. To keep the VBA Code simple, this time 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 the ProductID and OrderID to form a unique Key to 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 a Form is opened with a Table, Query, or SQL statement as its Record Source, each record displayed on the Form is assigned a unique identification tag by MS Access known as 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, its Bookmark can be read and assigned to the Form’s Bookmark property, making that record the current one 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 Usage.

From the User's Point of View, enter the Search Key-Value (CustomerID) into the Unbound Text Box and click the Command Button 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 Command Button 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 Code of the subroutine given above and try it out.

You must import the Customers Table and Customers Form from 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 becomes current; a Label at the bottom of the Unbound Text Box will flash a few times with a message indicating that the search operation was successful and the record is current on the Form. If the search operation failed, 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 or not. To visit the Page and try it out, click here.

Earlier Post Link References:

• Unsecured Database and Users-Log
• MS-Access Report and Page Total
• Detail and Summary from the Same Report
• Hiding Report Lines Conditionally-3
• Hiding Report Lines Conditionally-2

Form Bookmarks And Data Editing-2

Continued from Bookmarks and Data Editing

In the first part of this article, we used saved Bookmarks to revisit previously accessed records, allowing us to review and verify the accuracy of any edited information.

The function myBookMarks(), which we created for that purpose, can be enhanced with an additional option—alongside the existing ones (1 = Save Bookmark, 2 = Retrieve Bookmarks, 3 = Initialize Bookmark List)—to display all the edited records together in Datasheet View.

However, this approach comes with certain side effects that must be understood before implementation. Recognizing these potential issues early will help you design effective workaround strategies when necessary. In this section, we’ll explore these challenges using the Order Details table as an example.

In the previous example, we used the Bookmark index number along with the OrderID value to verify that the correct records were being retrieved.

In many cases, a single purchase order may include multiple products, and consequently, all the records related to that order will share the same OrderID. If we use only the OrderID as a criterion in a query, it will return several Products associated with that order, regardless of which specific record we had previously visited.

This limitation didn’t arise when we relied on Bookmarks, since each record has a unique Bookmark that precisely identifies it. In those earlier cases, the OrderID served merely as a reference point to verify that the retrieved record was the correct one.

However, our current approach involves using the OrderID values saved in the Combo Box list as query criteria to retrieve all edited records at once. This can lead to unwanted duplicates or ambiguous results.

We can overcome this issue by ensuring that each item in the Combo Box list represents a unique record. If the table already contains a unique field (such as a primary key), we can use that instead of the OrderID. Otherwise, we can combine multiple field values to create a unique identifier for each record, and store this composite value in the Combo Box list,  along with the Bookmark index number.

This enhancement will form the basis of the fourth option in the myBookMarks() function.

Unique ID Value(s) as Key.

We will now combine the OrderID and ProductID values and store them together in the Combo Box list. Since the same product code will never appear twice under a single purchase order, this combination guarantees that each entry in the Combo Box represents a unique record.

The purpose of this new approach is to dynamically generate a query using the values stored in the Combo Box list, allowing us to display all edited records from the Order Details table with a single click.

In the fourth option of the myBookMarks() function, we will build an SQL statement using the combined OrderID–ProductID values as query criteria. This SQL string will then replace the SQL of a pre-defined SELECT query, enabling it to retrieve the corresponding records.

To trigger this functionality, we’ll add another Command Button beside the << Reset button. When the user clicks this button, all the edited records are displayed in Datasheet View.

Before proceeding, let’s write the code segment that implements this specific option. We’ll begin by declaring the required objects and variables in the declaration section of the function.

Dim db as Database, QryDef as Querydef
Dim strSql as String, strSqltmp as String, strCriteria as a String
.
.
.
Select Case ActionCode
.
.
.
Case 1
.
Case 2
.
Case 3
.
Case 4

strSqltmp = "SELECT [Order Details].* "
strSqltmp = strSqltmp & "FROM [Order Details] "
strSqltmp = strSqltmp & "WHERE ((([OrderID]" & "&" & Chr$(34)
strSqltmp = strSqltmp & "-" & Chr$(34) & "&" & "[ProductID]) In ('"

strCriteria = ""
For j = 0 To ArrayIndex -1
   If Len(strCriteria) = 0 Then
   strCriteria = ctrlCombo.Column(1, j)
Else
   strCriteria = strCriteria & "','" & ctrlCombo.Column(1, j)
End If

Next

strCriteria = strCriteria & "')));"

Set db = CurrentDb
Set Qrydef = db.QueryDefs("OrderDetails_Bookmarks")
strSql = strSqltmp & strCriteria
Qrydef.SQL = strSql
db.QueryDefs.Refresh
DoCmd.OpenQuery "OrderDetails_Bookmarks", acViewNormal
End Select 

We begin by defining the static portion of the SQL statement in the variable strSqltmp. Next, we loop through the items in the Combo Box, extracting the values from its second column, which contains the combined OrderID and ProductID separated by a hyphen (-). Inside the For…Next loop, these values are used to build the criteria string in the variable strCriteria.

Once the criteria are fully constructed, we redefine the SQL statement of the OrderDetails_BookMarks query, inserting the newly built criteria before opening the query to display the extracted records.

Remember that Combo Box columns in Access use zero-based indexing. This means that the second column is referenced as Column(1, j), where j is the current row index. The statement

strCriteria = strCriteria & "-,'" & ctrlCombo.Column(1, j) 

therefore retrieves the combined OrderID–ProductID string value from the second column and appends it to the criteria string used for record selection.

Modified VBA Code.

The modified Code of the myBookMarks() Function with the above Option is given below.

1. You may copy the Code and paste it into the Standard Module, replacing the earlier code, or rename the earlier Function and save this Code separately, with the original name.

   Public Const ArrayRange As Integer = 25
   Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer
   
   Public Function myBookMarks(ByVal ActionCode As Integer, ByVal cboBoxName As String, Optional ByVal RecordKeyValue) As String
   '-----------------------------------------------------------------
   'Author : a.p.r. pillai
   'Date   : October-2009
   'URL    : www.msaccesstips.com
   'Remarks: All Rights Reserved by www.msaccesstips.com
   '-----------------------------------------------------------------
   'Action Code : 1 - Save Bookmark in Memory
   '            : 2 - Retrieve Bookmark and make the record current
   '            : 3 - Initialize Bookmark List and ComboBox contents
   '            : 4 - Filter Records and display in Datasheet View
   '-----------------------------------------------------------------
   Dim ctrlCombo As ComboBox, actvForm As Form, bkmk As String
   Dim j As Integer, msg As String, bkmkchk As Variant
   Dim strRowSource As String, strRStmp As String, matchflag As Integer
   Dim msgButton As Integer
   
   Dim db As Database, Qrydef As QueryDef
   Dim strSql As String, strSqltmp As String, strCriteria As String
   
   'On Error GoTo myBookMarks_Err
   
   If ActionCode < 1 Or ActionCode > 4 Then
      msg = "Invalid Action Code : " & ActionCode & vbCr & vbCr
      msg = msg & "Valid Values : 1 to 4"
      MsgBox msg, , "myBookMarks"
      Exit Function
   End If
   
   Set actvForm = Screen.ActiveForm
   Set ctrlCombo = actvForm.Controls(cboBoxName)
   Select Case ActionCode
       Case 1
           bkmk = actvForm.Bookmark
           'check for existence of same bookmark in Array
           matchflag = -1
           For j = 1 To ArrayIndex
              matchflag = StrComp(bkmk, bookmarklist(j), vbBinaryCompare)
              If matchflag = 0 Then
                  Exit For
              End If
           Next
           If matchflag = 0 Then
              msg = "Bookmark of " & RecordKeyValue & vbCr & vbCr
              msg = msg  & quot;Already Exists."
              MsgBox msg, , "myBookMarks()"
              Exit Function
           End If
           'Save Bookmark in Array
           ArrayIndex = ArrayIndex + 1
           If ArrayIndex > ArrayRange Then
             ArrayIndex = ArrayRange
             MsgBox "Boookmark List Full.", , "myBookMarks()"
             Exit Function
           End If
           bookmarklist(ArrayIndex) = bkmk
   
           GoSub FormatCombo
   
           ctrlCombo.RowSource = strRowSource
           ctrlCombo.Requery
       Case 2
           'Retrieve saved Bookmark and make the record current
           j = ctrlCombo.Value
           actvForm.Bookmark = bookmarklist(j)
       Case 3
           'Erase all Bookmarks from Array and
           'Delete the Combobox contents
           msg = "Erase Current Bookmark List...?"
           msgButton = vbYesNo + vbDefaultButton2 + vbQuestion
           If MsgBox(msg, msgButton, "myBookMarks()") = vbNo Then
               Exit Function
           End If
           For j = 1 To ArrayRange
              bookmarklist(j) = ""
           Next
           ctrlCombo.Value = Null
           ctrlCombo.RowSource = ""
           ArrayIndex = 0
       Case 4
           strSqltmp = "SELECT [Order Details].* "
           strSqltmp = strSqltmp & "FROM [Order Details] "
           strSqltmp = strSqltmp & "WHERE ((([OrderID]" & "&" & Chr$(34)
           strSqltmp = strSqltmp & "-" & Chr$(34) & "&" & "[ProductID]) In ('"
           strCriteria = ""
           For j = 0 To ArrayIndex - 1
               If Len(strCriteria) = 0 Then
                   strCriteria = ctrlCombo.Column(1, j)
               Else
                   strCriteria = strCriteria & "','" & ctrlCombo.Column(1, j)
               End If
           Next
           strCriteria = strCriteria & "')));"
    
          Set db = CurrentDb
           Set Qrydef = db.QueryDefs("OrderDetails_Bookmarks")
           strSql = strSqltmp & strCriteria
           Qrydef.SQL = strSql
           db.QueryDefs.Refresh
           DoCmd.OpenQuery "OrderDetails_Bookmarks", acViewNormal
   End Select
   
   myBookMarks_Exit:
   Exit Function
   
   FormatCombo:
   'format current Bookmark serial number
   'and OrderID to display in Combo Box
   strRStmp = Chr$(34) & Format(ArrayIndex, "00") & Chr$(34) & ";"
   strRStmp = strRStmp & Chr$(34) & RecordKeyValue & Chr$(34)
   
   'get current combobox contents
   strRowSource = ctrlCombo.RowSource
   
   'Add the current Bookmark serial number
   'and OrderID to the List in Combo Box
   If Len(strRowSource) = 0 Then
        strRowSource = strRStmp
   Else
        strRowSource = strRowSource & ";" & strRStmp
   End If
   Return
   
   myBookMarks_Err:
   MsgBox Err.Description, , "myBookMarks()"
   Resume myBookMarks_Exit
   End Function

   
   

   A Select Query and some Changes in the Form.

   You can test this new option by making a few minor changes to the Form we created earlier (see the design view image below). To begin, create a Command Button and a simple SELECT Query that we’ll use to display the bookmarked records.

   First, create a new Query with the following SQL statement and save it under the name OrderDetails_BookMarks:

   SELECT [Order Details].* 
   FROM [Order Details];
   

   Next, open the Order Details Form in Design View and add a new Command Button next to the existing (<) Button, as shown in the sample Form design below. This new button will be used to run the procedure that retrieves and displays all the edited records from the Order Details table in Datasheet View.

   

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

2. Click on the newly added Command Button to select it, and open the Property Sheet by choosing View → Properties from the menu.

   In the Property Sheet:

   • Set the Name property to cmdShow.

   • Set the Caption property to View Records.

   Next, locate the On Click property. From its drop-down list, select [Event Procedure], and then click the Build (…) button. This will open the Form’s Code Module, where Access automatically inserts the empty procedure skeleton, as shown below:

   Private Sub cmdShow_Click()
   
   End Sub

3. Write the middle of the Sub-Routine as shown below:

   Private Sub cmdShow_Click()
       myBookMarks 4, "cboBMList"
   End Sub
   

   Perform a Trial Run

4. Save and close the Order Details Form and open it in Normal View.

5. Double-click on the Record Selector of a few records on the Form to add the Bookmark List to the Combo Box.

6. Click on the drop-down control of the Combo Box to ensure that the selected Item Codes are added to the Combo Box List.

7. Click on the View Records Command Button to open the Query OrderDetails_Bookmarks in Datasheet View with the records that match the Combo Box Values.

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

Examine the sample image displaying the Query result overlapping the Form, all records corresponding to the values stored in the Combo Box list.

Notice that the Product field displays the Product Description instead of the Product Code that appears in the Bookmark Combo Box on the main form. This is because the Display Width of the Combo Box’s first column (Product Code) is set to 0", effectively hiding it from view in Datasheet mode. However, when you select an item from this Combo Box, the Product Code—not the description—is stored in the Order Details table, since it is the bound column.

When you double-click on a record selector, the stored ProductID value (rather than the displayed description) is retrieved and combined with the OrderID value to update the Combo Box list correctly.

Let us find out how to open a Form with the last record you were working on in an earlier session: Click here.

Want to find out how to use Combo Boxes and List Boxes in different ways? Visit the following Links:

1. Selected ListBox Items and Dynamic Query
2. Create a List from another ListBox
3. ListBox and Date: Part-1
4. ListBox and Date: Part-2
5. ComboBox Column Values
6. External Files List in Hyperlinks
7. Refresh Dependent ComboBox Contents

• MS-Access Report and Page Total
• Detail and Summary from the Same Report
• Hiding Report Lines Conditionally-3
• Hiding Report Lines Conditionally-2
• Hiding Report Lines Conditionally