Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Form Recordset and Bookmarks

Bookmarks are stored on individual records of a Recordset, when loaded into memory on a Form.  When a Table or Query linked to a Form is open a unique Id is generated and stored in the Bookmark Property of each record.  When you close the Form this is cleared.  Bookmarks are a two Byte data of String Type.  They are not displayable or printable characters, when printed on screen it simply displays a ? character.

Not all Recordsets have Bookmarks and this can be checked by reading it’s  Bookmarkable Property Value.  If the Bookmarkable Property value is false then this Recordset doesn’t have bookmarks.

If you create a Recordsetclone in VBA from a Form’s (say Form-A) Recordset the Recordsetclone’s bookmark and Form’s Recordset bookmarks will be identical. You can use the StrComp() Function to compare Bookmarks.  Use 0 (zero) as third argument of the function.

But, if you load the same Table on a different Form (say Form-B) at the same time both form’s Recordset bookmarks will not be identical.  When you close and open the Form with same table a second time both session’s bookmarks of records will not be identical.

When an attached Table having no Primary Key is open in a Form that Recordset will not have any bookmarks.

When Form doesn’t have a Record Source Value then addressing Form’s Bookmark Property will trigger an error.  But, when a Table or Query is loaded into the Record Source property of the Form, the Form will have a Bookmark Property only for the Current Record.  You can move the records on the Form and read their bookmarks and save them into different Variables to come back to those records later through VBA.

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 Norwind sample database.
  2. Create a Tabular Form for Employees Table.
  3. On the Footer Section of the Form create two Command Buttons.
  4. Select the first Command Button.
  5. Display it’s 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 bkMark Variable.
  14. Now, move 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 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 Table
Share:

Activity Dates and Quarterly Reports

There are four Quarters in a Year:

Jan - Mar = 1st Quarter
Apr - Jun = 2nd
Jul - Sep = 3rd
Oct - Dec = 4th

First three months of the year is first quarter, next three months belongs to second Quarter and so on.

Usually, when we prepare a Quarterly Report (for a period of three months based on the table above) we use date-range value to filter the required data for the report.

For example: To prepare Sales Report for the Second Quarter of Sales Year 2017 we will set the date range from April 1st, 2017 to June 30, 2017 as data filtering criteria in a SELECT Query. Probably we may use a Date-Picker control on the parameter entry Form to make it easier to pick and set the date values, rather than typing the date range manually.

If the Report preparation procedure is created on the above fixed pattern then the task can be made easier by creating a small Function and use it on the data filtering Query

GetQrtr() Function Code is given below:


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 takes the date value as parameter. The Month() Function extracts the month number from date and uses it as parameter for the Choose() Function to pick the correct Quarter Number from it's list.

When the month value is 1,2 or 3 the GetQrtr() function returns 1 to the calling procedure. The Function can be called from a Query, from other Functions, from Form control or from a Report Control by passing date as parameter. When date value passed to the function belongs to April, May, June will returns 2. These months belongs to the Second Quarter of the Year. Dates from next three months returns 3 and so on.


Let us see how we can use this Function in a Sales Query to extract data for Second Quarter 2017 Sales Report. Sample SQL of a data filtering Query is given below:


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


The GetQrtr() function extracts the Quarter number from all the Sales Record Dates, based on the Values we have lined up in the Choose() Function inside the GetQrtr() Function, compares them with the criteria parameter value 2 and filters the records for that period.


You may setup a Parameter Variable within the Query so that it will prompt for the criteria value, when the Query Runs, and can input the required value directly to filter the data for report.


When Financial Year is from April to March next Year (Jan - Mar become 4th quarter) still the filter criteria will be 1 to extract the data for the fourth quarter. The report heading Labels will indicate that the report is for the fourth quarter of Financial Year 2017-18.

Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts