Form Recordset and Bookmarks

Introduction.

Bookmarks are stored in a form’s Recordset when the form is loaded into memory. For each record in the table or query linked to the form, a unique string-type identifier is generated and stored in the Bookmark property. When the form is closed, the Bookmark property values are cleared. Bookmarks are two-byte string values that cannot be displayed or printed directly—if printed to the screen, they appear as question mark symbols.

Not all Recordsets support bookmarks. This can be verified by checking the Bookmarkable property. If Bookmarkable = False, the recordset does not support bookmarks.

When you create a recordset clone in VBA from a form’s recordset (for example, Form-A), the clone and the form’s recordset will share identical bookmark values. You can use the StrComp() function to compare these bookmarks, with 0 (zero) specified as the third argument for a binary comparison.

However, if you load the same table into a different form (say, Form-B) at the same time, the bookmarks in the two forms’ recordsets will not be identical. Similarly, if you close and reopen a form that uses the same table, the bookmarks generated in each session will differ.

If a linked table does not have a primary key, then its recordset will not support bookmarks when opened in a form.

When a form has no RecordSource value, attempting to access its Bookmark property will trigger an error. But when a table or query is assigned as the form’s RecordSource, the form will maintain a Bookmark property for the current record. As you navigate through the records, you can read and store these bookmarks in variables, allowing you to return to specific records later through VBA.

Sample Bookmark-Based Trial Run.

Let us try a simple example to save the Bookmark of a record on the Form into a variable and use it later to come back to the bookmarked record.

1. Import the Employees Table from the Northwind sample database.

2. Create a Tabular Form for the Employees Table.

3. In the Footer Section of the Form, create two Command Buttons.

4. Select the first Command Button.

5. Display its Property Sheet (F4).

6. Change the Name Property value to cmdSave. 

7. Change the Name Property value of the second Command Button to cmdRestore.

8. Display the VBA Module of the Employees Form.

9. Copy and paste the following code into the VBA Module:

   Dim bkMark As Variant
   
   Private Sub cmdRestore_Click()
      Me.Bookmark = bkMark
      MsgBox "Bookmark Restored"
   End Sub
   
   Private Sub cmdSave_Click()
       bkMark = Me.Bookmark
       MsgBox "Bookmark saved" 
   End Sub
   
   

10. Save and Close the Form.

11. Open the Form in normal view, showing employee records.

12. Use the record navigation control to move to the 5th record.

13. Click on the Save Command button to save the Bookmark of the current record in the 'bkMark' Variable.

14. Now, move a few records forward on the Form.

15. Click on the Restore Command Button to quickly make the 5th record current on the Form, by copying the Bookmark from the 'bkMark' Variable into the Form’s Bookmark Property.  You can try this out with different records on the Form.

The following links will show you more tricks on this topic with interesting examples:

1. Form Bookmarks and Data Editing
2. Form Bookmarks and Data Editing-2
3. Form Bookmarks and Data Editing-3
4. Forms and Custom Properties
5. Saving Data on Forms, not in a Table

Activity Dates and Quarterly Reports

Introduction.

There are four Quarters in a Year:

Jan - Mar = 1st Quarter

Apr - Jun = 2nd

Jul - Sep  = 3rd

Oct - Dec = 4th

The first three months of the year make up the first quarter, the next three months the second quarter, and so on.

When preparing a quarterly report (covering three months as defined above) for a business entity, we usually apply a date range filter to extract the required data.

For example, to prepare the Sales Report for the second quarter of 2017, we would set the date range from April 1, 2017, to June 30, 2017 as the filtering criteria in a SELECT query. To make this step easier, instead of manually typing the date range, we might use a date-picker control on a parameter entry Form to select the dates conveniently.

If the report preparation process follows a fixed quarterly pattern, the task can be further simplified by creating a small function and using it directly in the query’s filtering criteria.

GetQrtr() Function Code.

Public Function GetQrtr(ByVal mPeriod As Date) As Integer

Dim mMonth As Integer

On Error GoTo GetQrtr_Err

mMonth = Month(Nz(mPeriod, 0))

If mMonth > 0 Then
    GetQrtr = Choose(mMonth, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4)
else
    GetQrtr = 0
End If

GetQrtr_Exit:
Exit Function

GetQrtr_Err:
GetQrtr = 0
Resume GetQrtr_Exit
End Function

The GetQrtr() function accepts a date value as its parameter. The Month() function extracts the month number from the given date, which is then used as an argument for the Choose() function to return the correct quarter number.

For example, if the month is January, February, or March, the GetQrtr() function returns 1 to the calling procedure. If the month falls in April, May, or June, the function returns 2, representing the second quarter. Similarly, dates in July through September return 3, and dates in October through December return 4.

This function can be called from a query, another function, a form control, or a report control, simply by providing a date as the parameter.

Using the Function in Query.

Let us now see how the GetQrtr() Function can be used in a sales query to extract data for the Second Quarter 2017 Sales Report. A sample SQL statement is shown below:

SELECT SALESTABLE.*
FROM SALESTABLE
WHERE SALESTABLE.SALESMANCODE = "ABC" 
  AND GetQrtr([SALESDATE]) = 2;

Here, the GetQrtr() function extracts the quarter number from each sales record’s date, based on the values defined in the Choose() function within GetQrtr(). The results are then compared against the criteria value, which filters the records for the second quarter.

Alternatively, you may set up a parameter variable within the query so that, when the query runs, it prompts for the criteria value. This allows you to directly enter the desired quarter number and filter the data dynamically for any report.

In cases where the financial year runs from April to March, the quarter numbering shifts. For example, January–March would be considered the fourth quarter of the financial year. In such a case, the filter criteria would still be 1 (for the first calendar quarter). But the report’s heading labels should indicate that it represents the fourth quarter of the financial year 2017–18.

Earlier Post Link References:

• Custom Calculator and Eval Function
• Calculating Workdays From Date-Range
• Useful Report Function
• Calculating Time Difference