Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Form Bookmarks and Data Editing

You want to edit information on 25 records on the Form randomly. You will open the main data editing Form. Search for the record by entering the Key Value to find (say Employee Code or OrderID Value) in the Find (Ctrl+F) control and edit the record when found on the Form. This procedure is repeated for all 25 records for the first time because you don't have any other choice to find and edit the information.

But, the information you have changed is very critical and any mistakes in them may lead to serious issues. Mistakes can easily creep-in when you go through the finding and editing procedure in a hurry. It is important that you should have a second look at each record to verify and ensure the accuracy of changes.

Going through the same procedure to find all those 25 records again by displaying the Find control, keying in the Key Values and clicking on the Find button to reach the required record is not as enjoyable as it did for the first time.

But, if you can reach all those distant records one by one, in the same order of editing, without going through the above cumbersome procedure then it will be a great relief and can finish the work fast.

Why I said in the same order during editing because you are holding the source document of changes in the same order of your first visit to the records.

We will develop a trick with the Form's Bookmarks to make this kind of work easier for our Application Users.

FORM BOOKMARKS

When you open a Form attached to a Table, Query or SQL Statement each record on the Form is marked by MS-Access with a unique identifying tag known as Bookmark (a two Byte string Value). This is happening every time you open the Form with the above record sources. The Bookmarks are valid only on the current session of the Form and not stored in Tables.

We can read the Bookmark of any record from the Form's Bookmark Property, when the Record is Current on the Form, and store it in Variables in memory. Bookmarks saved this way can be used again to go back quickly to the same record we have visited earlier.

I have created a Function with the name myBookMarks() for this purpose and you can implement this method on any Form that has a Recordset attached to it by creating a Combo Box and a Command Button and four lines of Code in the VBA Code Module of the Form to run the Function.

You will definitely get a pat on your back from the User of your MS-Access Application for implementing this simple feature. So, let us start with our sample Project.

SAMPLE PROJECT

  1. Open your VBA Editing Window (Alt+F11).
  2. Create a new Standard Module (Insert - -> Module), Copy and Paste the following Code of myBookMarks() Function into the Module and Save it:
    Option Compare Database
    Option Explicit
    
    Public Const ArrayRange As Integer = 25
    Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer
    
    Public Function myBookMarks(ByVal ActionCode As Integer, ByVal cboBoxName As String, Optional ByVal RecordKeyValue) As String
    '-----------------------------------------------------------------
    'Author : a.p.r. pillai
    'Date   : October-2009
    'URL    : www.msaccesstips.com
    'Remarks: All Rights Reserved by www.msaccesstips.com
    '-----------------------------------------------------------------
    'Action Code : 1 - Save Bookmark in Memory
    '            : 2 - Retrieve Bookmark and make the record current
    '            : 3 - Initialize Bookmark List and ComboBox contents
    '-----------------------------------------------------------------
    Dim ctrlCombo As ComboBox, actvForm As Form, bkmk As String
    Dim j As Integer, msg As String, bkmkchk As Variant
    Dim strRowSource As String, strRStmp As String, matchflag As Integer
    Dim msgButton As Integer
    
    On Error GoTo myBookMarks_Err
    
    If ActionCode < 1 Or ActionCode > 3 Then
       msg = "Invalid Action Code : " & ActionCode & vbCr & vbCr
       msg = msg & "Valid Values : 1,2 or 3"
       MsgBox msg, , "myBookMarks()"
       Exit Function
    End If
    
    Set actvForm = Screen.ActiveForm
    Set ctrlCombo = actvForm.Controls(cboBoxName)
    Select Case ActionCode
        Case 1
            bkmk = actvForm.Bookmark
            'check for existence of same bookmark in Array
            matchflag = -1
            For j = 1 To ArrayIndex
               matchflag = StrComp(bkmk, bookmarklist(j), vbBinaryCompare)
               If matchflag = 0 Then
                   Exit For
               End If
            Next
            If matchflag = 0 Then
               msg = "Bookmark of " & RecordKeyValue & vbCr & vbCr
               msg = msg & "Already Exists. "
               MsgBox msg, , "myBookMarks()"
               Exit Function
            End If
            'Save Bookmark in Array
            ArrayIndex = ArrayIndex + 1
            If ArrayIndex > ArrayRange Then
              ArrayIndex = ArrayRange
              MsgBox "Boookmark List Full. ", , "myBookMarks()"
              Exit Function
            End If
            bookmarklist(ArrayIndex) = bkmk
    
            GoSub FormatCombo
    
            ctrlCombo.RowSource = strRowSource
            ctrlCombo.Requery
        Case 2
            'Retrieve saved Bookmark and make the record current
            j = ctrlCombo.Value
            actvForm.Bookmark = bookmarklist(j)
        Case 3
            'Erase all Bookmarks from Array and
            'Delete the Combobox contents
            msg = "Erase Current Bookmark List...? "
            msgButton = vbYesNo + vbDefaultButton2 + vbQuestion
            If MsgBox(msg, msgButton, "myBookMarks()") = vbNo Then
                Exit Function
            End If
            For j = 1 To ArrayRange
               bookmarklist(j) = ""
            Next
            ctrlCombo.Value = Null
            ctrlCombo.RowSource = ""
            ArrayIndex = 0
    End Select
    
    myBookMarks_Exit:
    Exit Function
    
    FormatCombo:
    'format current Bookmark serial number
    'and OrderID to display in Combo Box
    strRStmp = Chr$(34) & Format(ArrayIndex, "00") & Chr$(34) & ";"
    strRStmp = strRStmp & Chr$(34) & RecordKeyValue & Chr$(34)
    
    'get current combobox contents
    strRowSource = ctrlCombo.RowSource
    
    'Add the current Bookmark serial number
    'and OrderID to the List in Combo Box
    If Len(strRowSource) = 0 Then
         strRowSource = strRStmp
    Else
         strRowSource = strRowSource & ";" & strRStmp
    End If
    Return
    
    myBookMarks_Err:
    MsgBox Err.Description, , "myBookMarks()"
    Resume myBookMarks_Exit
    End Function
  3. Import the following Tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample Database:
    • Order Details
    • Products

    NB: We are not using the second table directly, but the Order Details table references the Products Table for Product Description.

  4. Click on the Order Details Table to select it.
  5. Select Form from Insert Menu and select AutoForm : Tabular from the displayed list.
  6. MS-Access creates a Tabular Form quickly. Save the Form with the name Order Details.

    A sample image of a Tabular Form in Design View is given below.

  7. Open Order Details Form in Design View.
  8. Expand the Form Header area and move the Field Headings down for enough space to create a Combo Box and a Command Button as shown on the sample design above.
  9. Display the ToolBox (View - ->ToolBox), if it is not already visible.
  10. If the Control Wizards Tool Button is already in selected state then click on it to de-select it.
  11. Select the Combo Box Tool from the ToolBox and draw a Combo Box in the Header Section of the Form as shown on the design above.
  12. While the Combo Box is still in selected state; select Properties from View menu to display the Property Sheet of the Combo Box.
  13. Change the following Property Values as shown below:
    • Name = cboBMList
    • Row Source Type = Value List
    • Column Count = 2
    • Column Widths = .5";1"
    • Bound Column = 1
    • List Rows = 8
    • List Width = 1.5"
  14. Change the Caption of the Child Label, attached to the Combo Box, to Bookmark List: .
  15. Create a Command Button to the right side of the Combo Box.
  16. Display the Property Sheet of the Command Button.
  17. Change the following Property Values as shown Below:
    • Name = cmdReset
    • Caption = << Reset
  18. Display the Code Module of the Form (View - -> Code).
  19. Copy and Paste the following VBA Code into the Module; Save and Close the Form:
    Option Compare Database
    
    Private Sub Form_DblClick(Cancel As Integer)
        'Save Current Record's Bookmark in memory
        myBookMarks 1, "cboBMList", Me![OrderID]
    End Sub
    
    Private Sub cboBMList_Click()
        'Retrieve the bookmark using the
        'index number from the Combobox List
        'and make the respective record current
        myBookMarks 2, "cboBMList"
    End Sub
    
    Private Sub cmdReset_Click()
        'Initialize Bookmarks and Combobox contents
        myBookMarks 3, "cboBMList"
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)    
    'Remove all Bookmarks from Memory
        myBookMarks 3, "cboBMList"
    End Sub
  20. Open the Order Details Form in Normal View.
  21. Double-Click on one of the record-selectors at the left side of the Form.
  22. Click on the Combo Box Drop-down control to check whether the OrderID value of the Record that you have double-clicked is added into the Combo Box List with a sequence number in the first Column or not.
  23. Make few more double-click on different Record selectors up or down on the form as you like.
  24. Check the Combo Box contents again to ensure that all these record references are added into the Combo Box with running serial numbers.

    Now, let us check whether we can jump quickly to one of these records we have visited earlier by using the saved Bookmarks List appearing in the Combo Box.

  25. Click on the drop-down control of the Combo Box to display the list of Bookmarks and click on one of the item from the list.

    The selected OrderId record will become the Current Record on the Form. Even if there are several records with the same OrderID it will correctly pick the record that you have visited earlier because we are using Bookmark and not the Find method with the OrderID Value to find the record. If OrderID was used then it will stop at the first record with the same OrderID numbers, not on the same record you have visited earlier.

    You may try out other items appearing in the list for now. You may implement this method on Forms with Column Format too.

    Here, I would like to remind you that we are saving the List of Bookmarks in the BookMarkList Array in myBookMarks() Function in the Standard Module. The Combo Box is only added with the Index Number of the Array elements already filled with Bookmarks out of a Total of 25 elements dimensioned for use.

    Check the following declarations of the Function in the Global area of the Module:

    Public Const ArrayRange As Integer = 25
    Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer

    You can change the Value 25 to a higher or lower desired value according to your specific needs.

    Let us continue by adding few more distant record bookmarks into the existing list.

  26. Scroll down the vertical scrollbar of the Form and double-click on the Record-Selectors of few more records from distant area of the Recordset.
  27. Now, try to reach any of these Bookmarks we have added into the list by selecting them one by one from the Combo Box List.

    Isn't it very easy to revisit all those records a second time?

  28. If you want to erase all those Bookmarks from the BookmarkList Array in memory click on the << Reset Command Button. After this, you can create a fresh list of Bookmarks.

The OrderID Field Value added to the Combo Box along with the Index Number of the Bookmark Array can be used to cross check with the retrieved record value to ensure correctness.

Let us look the Sub-Routines we have copied into the Form Module and check what they are doing.

Private Sub Form_DblClick(Cancel As Integer)

    myBookMarks 1, "cboBMList", Me![OrderID]

End Sub

When you double-click on the Record Selector of a record the above Sub-Routine calls the main Function myBookmarks() with the following parameters:

Action Code : 1 - indicates to fetch the Current Bookmark (a two byte string value consists of displayable/non-displayable characters) from the Active Form and save it in Memory in BookMarkList Array after incrementing the Array index number in Variable ArrayIndex. The Action Code is tested in the Select Case. . .End Select segment in the myBookMarks() Function. The Bookmark value itself is not displayed anywhere.

Combo Box Name : "cboBMList" - to display the Index Number of the BookmarkList Array in the Combo Box. The Name of the Control is enough to reference it on the Active Form.

Record Field Value : OrderID - to display the Record Field Value in the Combo Box along with the BookMarkList Array Index number. You can use any Field Value of your Table so far as it serves the purpose of checking the correctness of the record retrieved using the Bookmark.

The third parameter of myBookMarks() Function is defined as Optional and is omitted while calling the Functions to retrieve the Bookmark or to erase the Bookmarks List in the following three Sub-Routines respectively:

Private Sub cboBMList_Click()
  myBookMarks 2, "cboBMList"

End Sub

Private Sub cmdReset_Click()
  myBookMarks 3, "cboBMList"

End Sub

'Erases the Bookmarks when the Form is closed

 Private Sub Form_Unload(Cancel As Integer)

  'Remove all Bookmarks from Memory

  myBookMarks 3, "cboBMList"

  End Sub

Since, the main Function myBookMarks() references the Active Form you can implement this method on any Form without directly passing any Form Name to the Function.

NB:

  1. Saving, retrieving and using Bookmarks for finding records is valid only for the current session of the Form.
  2. Re-querying the Form's contents (not Refreshing) re-creates the Bookmarks for the Recordset on the form and earlier saved Bookmarks may not be valid after that. You must create fresh Bookmark List to use correctly.
  3. This method will not work on Forms attached to external Data Sources, linked to Microsoft Access, that doesn't support bookmarks.
Share:

Run Slide Show when Form is Idle

Dear Readers,

I am very sorry about last two weeks unexpected events and could not come out with the weekly Articles. I had to go to India on an emergency on 27th September 2009 and the Website also failed on the same day itself due to some errors in the Web Server Configuration changes made by Administrators.

I have noticed this serious state of the Website on 8th of October and could bring up the Site only on 9th of October 2009. I deeply regret the inconvenience caused to you.

This week, we will create a Slide Show of images on a Form that runs when the Form remains idle for a certain period of time. It works something like the following:

  • When the Main Switchboard Form remains idle for about one minute the Slide Show runs and each image will change on a fixed interval of about 6 Seconds. The Idle Time and Interval Time can be changed according to your specific needs.
  • The idle time of the Form is calculated based on the inactivity (i.e. no interaction with the User) of the Main Switchboard Form and the active control on the Form remains active for more than one minute.
  • If you click on a Command Button or List box or any other control other than the active control on the Main Switchboard Form then the Form comes out of idle state and the Slide Show stops.
  • Checking for the idle state of the Form starts again from that point onwards with the new active control. If the same control remains active for more than one minute then the slide show starts again.
  • If you open another Form over the Main Switchboard Form then the slide show sleeps until the Main Switchboard From becomes active again.
  • You need few Bitmap Images of Landscapes or Photos and you can use as many images as you like for the Slide Show. All the images should have the same name with a Sequence Number at the end like Image1.bmp, Image2.bmp, Image3.bmp and so on. You can use .jpg or .GIF and other images but this will display a progress bar for a brief moment while loading the image into the Image Control. All images must be of the same type.

A sample Main Switchboard Form with the active Slide Show is given below:

Click to Enlarge

  1. To try out this interesting trick; first organize few Bitmap Images into a folder as explained above.
  2. Make a copy of your existing Main Switchboard Form (Control Screen).
  3. Open the Form in Design View.
  4. Make enough room on the Form to create an Image Control on the Form.
  5. Display the Toolbox (View - - >Toolbox), if it is not visible.
  6. Select Control Wizards button on the Toolbox to make it active.
  7. Select Image control Tool from the Toolbox and draw an Image Control on the Form where you want the Slide Show to appear.
  8. The Common Dialog Control will display. Browse to the Images location and select the image with Serial Number 1 (Image1.bmp).

    The selected image will appear in the Image Control.

  9. While the Image control is still in selected state; display the Property Sheet (View - -> Properties) and change the following Property Values as indicated below:
    • Name = ImageFrame
    • Size Mode = Zoom

    NB: We need part of the Picture Property Value (the location address of the Images) to make changes in the Program that we are going to introduce into the Code Module of the Form. So, note down the image location address on paper from the Picture Property or copy and paste it into a Notepad Document for later use.

  10. Display the Code Module of the Form (View - -> Code).
  11. Press Ctrl+A to highlight the existing VBA Code in the Module, if present, and press Delete to remove them.
  12. Copy and Paste the following Code into the Form Module.
    Option Compare Database
    Option Explicit
    
    Dim actForm As String, actControl As String, idletime As Integer
    Dim oldForm As String, oldControl As String, imageNo As Integer
    
    Private Sub Form_Activate()
        Me.TimerInterval = 1000
    End Sub
    
    Private Sub Form_Deactivate()
        Me.TimerInterval = 0
        Me.ImageFrame.Visible = False
    End Sub
    
    Private Sub Form_Load()
    DoCmd.Restore
    idletime = 0
    Me.ImageFrame.Visible = False
    Me.TimerInterval = 1000
    End Sub
    
    Private Sub Form_Timer()
    actForm = Screen.activeForm.Name
    actControl = Screen.activeForm.activeControl.Name
    
    If actForm = oldForm And actControl = oldControl And actForm = "Control" Then
        idletime = idletime + 1
    Else
        oldForm = actForm
        oldControl = actControl
        idletime = 0
        Me.ImageFrame.Visible = False
        DoEvents
    End If
    
    If idletime > 60 Then
       If idletime Mod 6 = 0 Then
            Me.ImageFrame.Visible = True
            imageNo = imageNo + 1
     imageNo = IIf(imageNo < 1 Or imageNo > 9, 1, imageNo)
     Me.ImageFrame.Picture = "E:\D\Movie\Britanica_Scenes\scene" & imageNo & ".bmp"
            DoEvents
       End If
    End If
    
    End Sub

    You have to make changes on few lines in the program that appears in bold letters.

  13. Change the word "Control" in the first line (with bold letters) with your own Main Switchboard Form Name.
  14. Change Number 9, in the second line with bold letters, to match with the number of Images you have saved for the Slide Show as explained above.
  15. Copy the Image Location Address and Image Name except Serial Number from the image location address we have saved in Notepad Document and Paste over the text E:\D\Movie\Britanica_Scenes\scene in the Program.
  16. If your pictures are not Bitmap Images then change the file extension ".bmp" to indicate your image type.
  17. Save and Close the Form.
  18. Open the Main Switchboard Form in Normal View.
  19. Wait for one minute to begin the Slide Show.

When the Slide Show is running, if you click on a Control other than the active control on the Form the Slide Show will stop and the image will disappear.

If you don't click on any other control on the Form and the time elapsed is more than one minute, since your last click on a Control, the Slide Show will run again.

If you want to increase or decrease the idle time of the Form then change the value in the VBA line If idletime > 60 Then to an appropriate value you prefer.

Each Image stays displayed for about 6 seconds. If you want to increase or decrease the display time then change the Value in the VBA line

If idletime Mod 6 = 0 Then to the required value you prefer.
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