Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Web Browsing within Access Form

Introduction

Browsing the World Wide Web is nothing new to us. But, how about organizing the Web Addresses of frequently visiting Websites in a Table and browsing the Web from within an Access Form?

Not only Internet Sites, but you can also browse the Intranet Website within your Local Area Network (Corporate LAN) too. All you have to do is to create an Access Form with a Microsoft Web Browser Control and a few lines of VBA Code.

For example, the following VBA Code will open the Gmail Web Site http://www.gmail.com/ automatically when you open the Form with the Web Browser Control, as shown in the above image.:

Simple Website Opening Code

Private Sub Form_Load()
Dim strURL As String

strURL = "http://www.gmail.com/"
Me.WebBrowser0.Navigate strURL
End Sub

Designing a Form for Web ActiveX Control

Don't know how to do it? Try the following:

  1. Open a new Form in Design View.

  2. Click somewhere on the Detail Section of the Form to select that area.

  3. Select the ActiveX Control from Insert Menu.

  4. Look for the name Microsoft Web Browser in the displayed list of ActiveX Controls and select it.

  5. Click OK to insert a Web Browser Control on the Form.

  6. While the Control is still in the selected state drag the right-bottom-corner sizing control to make it big enough on the Form so that you can view the Web Pages properly.

  7. Display the Property Sheet of the Browser Control (View - ->Properties).

  8. Change the Name Property Value to WebBrowserO to match with the name used in the above VBA Code.

  9. Select Code from View Menu to display the VBA Code Module of the Form.

  10. Copy and Paste the above Code into the Module.

  11. Save and Close the Form with the name myWebBrowser.

    Demo Run of the Form

  12. Connect your PC to the Internet.

  13. Open myWebBrowser Form in normal View.

  14. Wait for a few seconds to load the Web Page into the Control and to display.

We have used the Website Address directly in the Code. But, if you create a table with the list of all the Website addresses that you visit very often then with a Combo Box on the Form we can select the Web address to go to that site quickly. We need to make a few changes to the above code to make it flexible.

Table with Frequently Visiting Web Addresses

  1. Create a Table with a single field with the following details:

    Table Name: WebSites

    Field Name: Web Address Data Type: Text Field Size: 255

  2. Save the Table Structure and open it in Datasheet View.

  3. Add a few records with Web URLs that you visit frequently, and save and close the Table.

  4. Make a copy of the Form myWebBrowser and name the copy as myWebBrowser2.

  5. Open myWebBrowser2 in Design View.

  6. Display the Form Header Section (View - -> Form Header/Footer), if it is not already Visible.

  7. Expand the Form Header Section with enough height to create a Combo Box Control.

  8. Create a Combo Box using the Web Sites Table.

  9. While the Combo Box is still in the selected state display the Property Sheet (View - -> Properties).

  10. Change the following Property Values of the Combo Box:

    • Name : cboWeb
    • Width : 5"
    • Default Value: "http://www.gmail.com/" or any other Website Address Value you prefer.
  11. Select the child label attached to the Combo box and change the Caption Property Value to Web Address:

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

  13. Copy and Paste the following Code into the Module, replacing the existing lines.

    Form Module Code

    Option Compare Database
    Option Explicit
    
    Dim WebObj As WebBrowser
    Dim varURL As Variant
    
    Private Sub cboWeb_Click()
         GotoSite
    End Sub
    
    Private Sub cboWebLostFocus()
         GotoSite
    End Sub
    
    Private Sub Form_Load()
         Set WebObj = Me.WebBrowser0.Object
         GotoSite
    End Sub
    
    Private Function GotoSite()
        varURL = Me!cboWeb
    If Len(Nz(varURL, "")) = 0 Then
        Exit Function
    End If
    
    WebObj.Navigate varURL
    
    End Function
  14. Save the Form and open it in Normal View.

  15. The website address that you have inserted into the Default Value Property of the Combo Box will open up in the Browser Control.

  16. Select one of the other website addresses you have added to the Table from the Combo Box.

  17. The Browser Window will open the new Website.

If we open other Web Pages by clicking on the Links from the displayed page, then we can navigate (go Back or Forward) between pages by adding a few more lines of code.

Review of the VBA Code.

But, first, let us have a look at the above code to see what is happening there. We have declared a Web Browser Object Variable and a Variant Variable at the Global declaration area of the Module.

Created a separate Function GotoSite() to respond to different Events (Actions) on the Form without duplicating the code everywhere.

For example: when we open the Form the GotoSite() Function opens the Default Value URL, which we set in the Combo Box Property, through the Form_Load() Event Procedure.

When you select a URL from the Combo Box the cboWeb_Click() Event Procedure calls this Function to open the selected Web Page.

If you type a URL in the Web Address control cboWeb and move the Cursor out of the control the Lost_Focus() Event Procedure runs the Function to open the URL you typed in the Address Control.

We will create a few Command Buttons on the Header of the Form, as shown to the right of the Combo box Control on the above design, and write Subroutines with some simple Browser Object Commands to navigate between Web Pages, which we may open from the displayed Web Pages.

Web Navigation Controls

  1. Open myWebBrowser2 Form in Design View.

  2. Display the Toolbox (if it is not visible then select Toolbox from View Menu.

  3. Create five Command Buttons to the right of the Combo Box.

  4. Display the Property Sheet of the first Command Button and change the Name Property and Caption Property Values to Home.

  5. Similarly, change the Name and Caption Property Values of the other Command Buttons with the values given below.

    • Back
    • Forward
    • Refresh
    • Stop
  6. Display the Code Module of the Form (View - ->Code), copy and paste the following code into the VBA Module, below the existing Code lines.

Private Sub Home_Click()
On Error Resume Next
    WebObj.GoHome
End Sub

Private Sub Back_Click()
On Error Resume Next
    WebObj.GoBack
End Sub

Private Sub Forward_Click()
On Error Resume Next
    WebObj.GoForward
End Sub

Private Sub Refresh_Click()
On Error Resume Next
    WebObj.Refresh
End Sub

Private Sub Stop_Click()
On Error Resume Next
     WebObj.Stop
End Sub

The On Error Resume Next Statement prevents the Subroutines from running into Error when there are no Web Pages to Navigate to.

To test these Buttons you must click on a few links on the displayed Web Page to open other Pages from the same website and then use these buttons to navigate between those opened pages.

The Home Button will open the Default Home page you set in the Internet Explorer's Tools - -> Internet Options. . . Home Page - -> Address Control, not the default value you set in the Combo Box.

Share:

Text Search Filter Web Style

Introduction

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 give several pieces of text separated by, or + symbol to match any of the given text anywhere within the Web Pages and display the matching links on the screen.

Search text example1: ms-access, forms, reports, Queries

Or

Search text example2: ms-access+forms+reports+queries

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

Last week we learned the use of the BuildCriteria () Function to filter data using only one field. BuildCriteria() Function can accept only one Field as its first Parameter. We will be using this Function here also. But, we will pull a trick for using this Function for 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 Customers Table from C:\Program Files\Microsoft Offce\Office11\Samples\Northwind.mdb sample database.

  2. Create a Query using the Customers Table with all the fields and by adding a new Column with the name FilterField.

  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 with the name myQuery.

    This task you can automate with the following Program after creating a Query manually with at least one field from the Source Table and naming the Query as myQuery. You may modify the Program where the reference to myQuery name appears to implement your own preferred name if needed.

    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 Enter Key:

    CombineData "Customers"

    This will modify the design of myQuery by joining all the fields, except HyperLink, Object, Yes/No, and Memo Fields, from Customers Table, or any other Table that you use, and will create a new column with the name FilterField. If you need Memo Field contents too, then you must add that Field manually in Query design. The CombineData() Program will not do this because HyperLinks, Object & Memo fields fall into the same field Type category and the validation check bypasses it.

    The Sample Datasheet view image of the FilterField in myQuery is given below:


    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 the columns like Region, Fax, etc., or any other column that you feel are not required for our Test Runs so that all the required columns will fit on one screen properly.

  10. Expand the Form Header Section and drag the column headings down so that we will get 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.

    Example1: FRANK, Elizabeth Brown, Brazil

    Example2: FRANK+Elizabeth Brown+Brazil

    Example3: frank+ Elizabeth Brown, Brazil

    NB: Do not use brackets "()" in the search text. These will run into errors when used in the BuildCriteria() Function. Users may type extra spaces between the text separator character, or + symbol and the next search text item and these spaces will be removed by the program before going into search and filter operations. Embedded spaces in phrases will be left alone in the search text.

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

  21. You may inspect the filtered records to ensure that one or more of the search text you have entered into the Text Control appears in all the filtered records. They can appear in any field or fields in any Record, but all the records filtered will have these texts/numbers/phrases on them.

Share:

Filter with BuildCriteria Function

Introduction

Any method that helps to find data quickly on a Form is always welcome by Users. There are several options available when you are in Form View Mode.

When you Right-click on a Field a Shortcut Menu will pop up showing four data filter options as shown on the sample image given below.

The third option Filter For can accept a Criteria Expression like >10200 AND <=10300 or similar to filter a Range of Values from the selected field.

If you would like to see more options, then, point to the on-Filter option in Record Menu. There you can find two new Options: Filter by Form and Advance Filter/Sort. Filter by Form allows you to set up Criteria in more than one field to select records based on Form fields.

The Advanced Filter/Sort will open up the Filter Design (Query Design) Window with the Source Table/Query of the Form with whatever criteria you have entered into the Filter for or Filter by Form Options earlier if any. You can further modify the filter conditions and Sorting Order and select Apply Filter from the Records Menu to view the result.

But, if you would like to build a Custom Filter Option for the User; based on a particular field on the Form then you can use the built-in Function BuildCriteria() and write a VBA Sub-Routine around this Function to filter the data. Run the Sub-Routine on a Button Click Event Procedure and let the User input the Filter Criteria in various ways as he likes.

The Usage of the Function is very simple. Let us try a few examples of the Function directly in the Debug Window (Immediate Window) to understand 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 needs three Function Parameters. OrderID is the Data Field Name, dbLong indicates that OrderID is a Numeric Field with values of Long Integer Type and the last Value 10200 is the OrderID criteria value for selecting records. The OrderID Field Name will be inserted in appropriate locations in the Criteria expression by the BuildCriteria() Function.

The last parameter value we can use in several ways depending on how we want the result. Let us see a few more examples before we implement this method on a Form. Type the following expressions in the Debug Window to see how it works:

? 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 on Form to Filter Data

After getting the result text from the BuildCriteria() Function all we have to do is to 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 C:\Program Files\Microsoft Office\Officell\Samples\Northwind.mdb sample database.

  2. Click on Orders Table to Select it and select Form from 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 Header of the Form.

  8. While the Command Button is still in the selected state display its 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 on the Command Button.

The limitation of the BuildCriteria() Function is that it can accept only one field as the first parameter. But, there is a way to use more than one field 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 txtOrderFilter and txtShipNameFilter Variables.

If both Variables have filter conditions in them, then the User response is collected to check whether he/she needs the result set that meets both conditions (AND) or the result set that meets any one of the conditions or both (OR).

The Filter Strings are joined, accordingly to obtain the intended result. It is not necessary that the User should always provide both sets of Criteria Values (for Orderld and ShipName) all the time. They can use only one Field for entering Criteria and ignore the other.

Share:

Save User Specific Parameter values

Introduction

Last week we have prepared a Report Parameter Control Form for concurrent users on the Network without clashing with other User's Report Data Filter Parameter Values while preparing different Versions of the same Report. To do this we have discarded the usage of a Parameter Table as Record Source in the Parameter Form and created two Unbound Text Boxes for keying in Parameter Values for setting Filter condition in the Report Source Query. Besides that, we have seen how to save the Parameter Values in the Custom Properties of the Form, when the Form is closed.

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

Last week we have used the Parameter Control's Text Box Names FromDate and ToDate for entering Date-Range values as Criteria for filtering Data in Report Source Query. The Custom Property Names we have created giving slight changes to the Text Box Names, like DateFrom and DateTo.

One Question we raised last week was; is it possible to Save and Retrieve the Report Parameter Values set by each User on the fromDate and toDate TextBox Controls, in different instances of the same form, without getting overwritten by somebody else value? In last week's example, there is only one set of custom properties created for all the users and for all instances of the Form. The last user of the Network who closes his instance of the form will overwrite the Custom Property values of all other earlier instances and will be loaded back into the TextBox control next time when the form is loaded.

User-based Custom Property Name Format.

The solution is simple; create Custom Properties on the Form for each User and save the value set by him/her when their side instance of the Parameter Form is closed. To identify each User's value; add the User-Name along with the TextBox Name, to make the names unique, and create the Custom Property Name.

For example:

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

 We can get the User Name of the current instance of the Form with the Function CurrentUser().

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

Another point we have to keep in mind 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 the VBA Routines in such a way that whenever a new user starts working with the RptParameter Form; the User must be identified as a newcomer and Custom Properties in that User must be created automatically, rather than creating it manually as we did in the earlier example.

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 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 you can see from the Form_Load() Event Procedure that the Custom Property Creation Program CreateCustom_Property() is called only when the User (New or existing) opens the RptParameter2 Form. So, the Custom properties are not created for all the Users of the Database if their activity doesn't involve this particular Form.

The Report Parameter Form must be tested from different machines at the same time by different Users and set their own values in the Parameter Control Text Boxes: fromDate and toDate to try out the procedure. When the Form is open by the same Users next time the values set by them in the earlier session should appear in the fromDate and toDate Text Box Controls.

NB: The above Procedure is not extensively tested to trap Bugs or side effects and you may use it at your own risk. If you find any bugs or issues, please share them with me too. The procedure assumes that the Database is implemented with Microsoft Access Security.

Visit the Page: Unsecured Database and Users Log to find out how to get a User's Name when the shared database is an Un-secured one. In un-secured databases, the CurrentUser() Function will always return the User Name as Admin.

Share:

Creating Using Form Custom Property

Introduction

Normally, Parameter Controls are provided to the Users for entering data Filter criteria values for preparing MS-Access Reports. A reference to the parameter control fields can be set in the criteria row of the Report Source Query directly to filter the data. A sample image of such a Parameter Control is given below.

The above Report Parameter Control gives flexibility to the User to set a Date Range in the fields provided on the screen before opening one of two Report Options provided. When the user clicks on the Preview Command Button the Report will be opened with data filtered using the Parameter Control Date Range values set as Criteria on the Source Query.

To record the Date Range values (Date From and Date To) a Small Table with two fields is created with a single record and used as Record Source for the above Form. The idea behind the use of a Table is to preserve the last used Report Parameter Values in the Table. Next time when we open the Form we will know for which period we have prepared the Report earlier. The parameter table can also be used in the Query to link to the Data Table or use its value as criteria in the criteria Row of the Report Source Query. The following two Form Property Values must be set as shown below to prevent adding new Records to the Table and not to delete the existing ones:

  • Allow Additions = No
  • Allow Deletion = No

Multiuser Environment.

This method works fine when the Database is a Single User one.

But, when the database is shared on a Network there is a problem with this method. Different Users may open the same Parameter Screen at the same time (assuming that a single Front-End is shared on a Network) to prepare their version of the same Report. They will attempt to change the parameter values at the same time. This action will end up with a record, edit-lock error or the values set can cross over and the Report printed can go wrong too. Even though the Users can open different instances of the same Form on their machines the Record Source Table is the same.

We are focusing on this particular aspect to see how we can safely provide the above Parameter Control to the Users to work safely without clashing with each other.

Perhaps, you have the right solution to this problem by now. Yes, do not use the Parameter Table to store the Report criteria values; instead create two Unbound Text Boxes on the Form, as you have rightly guessed. This will ensure that all Users will work independently on their own instances of the Report Parameter Form and no chance of clashing with each other's value.

There is only a minor drawback in this method; you cannot store the last used Report Parameter Value so that it will be displayed next time when you open the Form.

At least one set of values is required when you open the Form next time. If these controls remain empty and if you run the Preview option without setting the parameter values, then the Report will be empty and will end up showing #Error in all controls with expressions created for Summary Values.

I have already published an article on this topic earlier on how to open the Report without this Error condition when the Report Source Query or Table is empty. Click here to find out.

We can save the values, from the Unbound Text Box controls, in the Parameter Form itself in Custom Properties, which we can create on the Form. Managing data in Custom Properties can be done only through VBA and these Property Names and their Values are not visible on the Property Sheets that we normally work with.

Click here to find out more details on Custom Properties and a method that we used earlier to open the Form with the last edited record as current on the Form.

The Custom Property

We have to go through the following procedure to manage the User data on the Form itself without the use of a Table as a Record Source:

  1. Create two Custom Properties on the Form with the names DateFrom and DateTo with the Data Type Date/Time and with an initial value.

    This is required only once. A small VBA Program is required in the Standard Module to create the Custom Properties on the Form. In the Program, the Parameter Form Name is required for reference. Not necessary to keep the Form in Design View to create the Custom Properties.

  2. When the Parameter Form is closed after normal use, the values set on the Unbound TextBoxes are Saved in the Custom Properties, during the Form Close Event.
  3. The saved values are loaded back into the Unbound Text Boxes from the Custom Properties when the Report Parameter Form is open next time.

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 somewhere within the pasted VBA Code and press F5 to Run the Code and create two Custom Properties with the Names DateFrom and DateTo with the Data Type Date/Time and with the initial value of System Date.

    How do you know whether these Properties are created or not? Try running the Program again and this will tell you that these Property names already exist on the Form.

    VBA Code to Delete Property if needed.

    If you want to Delete these Properties from the Form then Run the following Code:

    Public Function DeleteCustomProperty()
    Dim cdb As Database, doc As Document
    Dim prp As Property
    
    Set cdb = CurrentDb
    Set doc = cdb.Containers("Forms").Documents("RptParameter")
    doc.Properties.Delete "DateFrom"
    doc.Properties.Delete "DateTo"
    doc.Properties.Refresh
    
    Set prp = Nothing
    Set doc = Nothing
    Set cdb = Nothing
    
    End Function
  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 again 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 happy with it. Because, it will preserve only one of the Values, set by different Users working with the Form at the same time.

What I would like to see as a User is that the last value that I set in the Report Parameter Field is appearing on the Form again when I open the Form next time, not the value set by someone else. Is it possible? Yes, it is possible. We will see how to do this next week.

Share:

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 the sample data processing methods and tried to approach the same problem in different ways to arrive at the same result. Reports are the main output component that goes to the User with critical information for analysis of business activities and for making serious business decisions. Transforming raw data into a meaningful form and providing them on Reports is a real challenge of 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 look into such an issue here so that you will know what I meant by hurdles in creating the final Report. Such complex data processing steps can be automated, sequencing each step in a Macro, and running that Macro from a Command Button Click or from VBA Sub-Routines.

Process Flowcharts.

It is absolutely necessary to create and maintain Flow Charts, of processes that involve several Queries and Tables, clearly indicating the Input and Output in each step, arriving at the final Report Data. You may create hundreds of Queries in a Database for different Reports. After some time we may forget what we did for a particular Report. If the User points out any flaw in the output, then we can easily backtrack the steps using the Flow Chart and debug the problem.

Last week I raised a question as to how we will show Revenue, Expenses, and Profit/Loss month-wise if the sample data are added with Year and Month Fields. The image of the sample Table (Transactions2) Source data is given below:

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

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 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 are 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 how 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 Method2_2 Query and view the output.

Even though we could arrive at the sample result with the above two Queries we have to modify the second Query every time to create a Profit/Loss Column when new data records are added for subsequent months. The P & L Report is created using the second Query then that also has to undergo changes to add Revenue, Expenses, and Profit Columns for the new period.

This cannot be a good method when we are expected to automate every process in the Database so that the User can prepare Reports with the click of a button.

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 month's 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&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 to 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 source table Transactions2.

So, let us get to work and do it.

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

    We have modified the first Query above for simplifying 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 Source File, like the sample design image given below.

    The sample image shows the Columns in January and February only. But, you may design the Report for all twelve months in a similar way. The Value of Year field is used for creating headings so that it automatically changes when the Report is printed next year without modification to the Report.


    The Report in Print Preview.

    We will automate the P&L; Report preparation procedure to get updated data on the Report when new data of Revenue and Expenses are added to the Source Table. As part of the automation procedure, we need a Delete Query to remove the earlier data from the PandLReportTable before adding revised data into it.

  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 how you don't need any complicated programs to prepare this Report.

Actions Queries in Macro.

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

In Step-1 the PandLReportTable_Init Query removes earlier Report Data from the PandLReportTable.

In Step-3 the Append Query (Method3_2) takes the Cross-Tab Query output from Step-2 and adds them to the Report Table PandLReportTable.

We have already written expressions in PandLReportQ SELECT Query to calculate Profit/Loss Values. The Report will automatically get all available data from this Query and other Columns on the Report will remain empty till fresh data Records are added in the Source Table Transactions2.

If we can add both the Action Queries into a Macro (or VBA Subroutine) then the user can click on a Command Button to run it every month to create the Report with added data 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?

Share:

MS-Access and Data Processing

Introduction

Designing Forms or Reports can be learned quickly by mastering the use of Design Tools available in MS-Access, keeping aside the programming aspects. But, data processing is something that demands diversity in each Project and cannot be standardized. The data Table design is very important and these must be carefully planned and created for easier retrieval of information as well as to avoid duplication. Proper relationships must be established between Tables to join related information together.

Ignoring the importance of these considerations, designing with a casual approach, and filling up data with them like you do in Microsoft Excel will land you in trouble when you attempt to prepare reports out of them.

You can see a good example of database design in the C:\Program Files\Microsoft Office11\Samples\Northwind.mdb sample Database.

Open this sample database and select Relationships from Tools Menu to view the structure of various Tables and how they are organized and related to one another. Use it as a reference point and guide when you plan for a new Project.

Each Field Name in bold is defined as the Primary Key in their respective Table and established One-to-many Relationships with one another. This will ensure the availability of required information when needed from related tables for Reports.

The above lines were only a reminder of your future projects. You can see an example image of a bad Table design below. The Location names and Description values should have been in the Tables of their own with appropriate Codes. Two Combo Boxes can be created in the Transactions Table Structure to insert those Codes into the fields to avoid duplication of information as shown below.


Approaching the Data Processing Task.

But here, we are going to concentrate on learning the data processing steps using the above Table.

The second data field Type contains transaction type Codes. R stands for Revenue and E for Expenses. These category Codes are introduced in the Table keeping in mind that we must be able to Group the transactions on Category and Tabulate the Revenue and Expenses Values separately. The Description field shows the actual Account Heads under which each transaction is recorded.

We have been asked to prepare a Location-wise Profit/Loss Statement. Subtracting the Total of all Expenses from the Total of all Revenue figures will give us the required result. How many Queries or steps do you require to solve this problem, any idea? We require only the final Profit/Loss value with the Location Name in the Report, like the image below:

The first thought in your mind, I presume maybe, is how you can subtract the value of one Row from the other. Then you are thinking in the right direction.

If you say in four steps I will not accept that as a good approach, but if you can solve the problem and come out with the result then that is OK with me. After all, the correct end result is all that matters as far as the User is concerned.

If you say in three steps I will be happy to see how you do it. If you say in two steps, then I know you have some grip on things around here. If you say in one step, then I know you are somebody with MS-Access.

If you are really interested in taking up this simple challenge then stop reading further down from here and start trying out this in a database of your own. Come back with your own solution and compare it to the examples given here. If you do it differently, but arrive at the same result, then share that idea with me too.

Create the Transactions Table with the Structure and sample data given above.

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.

Two Steps 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:

    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 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 semi-colons. 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. It is not necessary that you should 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 being 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, that 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 output of the second Query is given below.

  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 Type Column with Description.


Doing It Differently

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

  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 Method2_2 Query in Normal View and check the output.
    As you have seen in the above examples you can approach a problem in MS-Access differently and arrive at the same result. If you have to create several steps to get the final Report output, then it is a good idea to create a Flow Chart of the Process Steps. Later on, if you find something is not right with the Report you can always follow this path and backtrack to find 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 you will 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.

Share:

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 which 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 is not complete without touching on the subject of Searching and Finding records and showing them as current-record on the Form using RecordsetClone.

In our earlier examples, we have used the Find Control (Ctrl+F or Edit - ->Find. . .) to search for a record and when found save its Bookmark in an Array Variable in Memory for later use.

This time we will use a different method to find the record and bring it to the Form with the use of the Bookmark of the RecordsetClone of the Form.

For this method, we will use an Unbound Text Box to enter the search key value and a Command Button to click and 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.

If we use the Order Details table then we have to combine the ProductID with OrderID to form a unique value to retrieve a specific record among several records with the same OrderIDs. This method we have already used in the earlier example is to select and display all the records we have retrieved and edited.

Review of Bookmarks.

As I have pointed out in the earlier Articles when we open a Form with a Table, Query, or SQL Statement each record on the Form is marked by MS-Access with a unique identification tag known as Bookmark. We can create a copy of this Recordset into memory (RecordsetClone) and work with it independently. Using the RecordsetClone, with the Form Bookmark attached to each record, we can find the required record with VBA Code using the search Key Value. Once we find the target record in memory we can read that record's Bookmark and insert it into the Form's Bookmark Property to make that record current on the Form.

But remember, you cannot read the Form's Bookmark Property Value and insert it into the RecordsetClone to find the same record in memory.

The Ease of Usage.

From the User's Point of View, all she has to do is to 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 back with the Title: Animating Label on Search Success. You may visit that Page to copy the complete VBA Code of the Sub-Routine given above and try them 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.

When the rst.FindFirst method finds the record and makes it 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 that record is made 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 go to the Page to try out the Program Click here.

Earlier Post Link References:

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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