Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Web Browsing within Access Form

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 browse the Web from within an Access Form?

Not only Internet Sites, you can 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.:

Private Sub Form_Load()
Dim strURL As String

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

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 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 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.
  12. Connect your PC to the Internet.
  13. Open myWebBrowser Form in normal View.
  14. Wait for 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 few changes in the above Code to make it flexible.

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

    Table Name: WebSites

    Field Name : WebAddress Data Type : Text Field Size: 255

  2. Save the Table Structure and open it in Datasheet View.
  3. Add few records with Web URLs that you visits frequently, 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 WebSites Table.
  9. While the Combo Box is still in 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.
    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 pages then we can navigate (go Back or Forward) between pages by adding few more lines of 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, 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 moves the Cursor out of the control the Lost_Focus() Event Procedure runs the Function to open the URL you typed on the Address Control.

We will create 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.

  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 running into Error, when there are no Web Pages to Navigate to.

To test these Buttons you must click on 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

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 piece of text separated with , 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 filter to display records from a table that matches several piece of Text/numbers/Phrases across in any field in any record.

Last week we have learned the usage of 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.

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

    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 following 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 falls into the same field Type category and the validation check bypasses it.

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

  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 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 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 Text Box 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>
  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 
  18. Save the Form and open it in Normal View.
  19. Type some Text/Numbers/Phrases separated with comas (,) 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, Brzail

    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 in for 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 text/number/phrases on them.
Share:

Filter with BuildCriteria Function

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 popup 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 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 Advance Filter/Sort will open up 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 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 Enter key to display the output:

? 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 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 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#

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 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).
  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 third Parameter into the BuildCriteria() Function above, when prompted for the Filter Condition.

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 first parameter. But, there is a way to use more than one field for 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 as 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

The first two InputBox() Functions collects the Filter Criteria for OrderID and ShipName Field values separately. Next steps Validates the User responses and Builds 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 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 set 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

Last week we have prepared a Report Parameter Control Form for concurrent usage on 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 to 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 change to the Text Box Names, like DateFrom and DateTo.

One Question we have raised last week was; is it possible to Save and Retrieve the Report Parameter Values set by each User on the fromDate and toDate Text Box Controls, in different instances of the same Form, without getting overwritten by somebody else value. In last week's example the survivor of the values written to the Custom Properties are the values of the last User who closes the Parameter Form.

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 and create the Custom Property Name with that combined text value.

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 is prefixed to the Custom Property Names we can easily save and retrieve individual User's parameter values and use 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 new comer and Custom Properties for that User must be created automatically, rather than creating it manually as we did in the earlier example.

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

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 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 it 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 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:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts