Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Back Tracking Open Forms

On Internet we may open several web pages, by visiting links on web pages. When we do that we will be provided with buttons on the browser to move from the current page to the previously opened pages or move forward one page at a time from earlier visited pages.

Similarly, if we need to keep several forms opened at the same time in Microsoft Access, to work with them by moving back from one form to the other and vice-versa then there is a way to do it.

When there are several forms to open for normal operations then it is a good idea to open all those forms once, immediately after the application is open, and keep them in memory in hidden state.  This may cause a slight delay at the time of opening the application to become ready for normal operations but later on they will be best performed when displaying them from memory, rather than opening and closing them every time.  All the opened forms in memory can be closed easily with a small VBA program before shutting down the application.

If jumping from one form to the other is required in a predictable sequence then all the forms can be opened through a simple macro by arranging the opening sequence in the required order.  See an image of a sample macro given below that opens several forms in the required order.

The first OpenForm Action opens the first Form (Employees4) in Normal View mode, others are in hidden mode to keep them in memory.  The required form can be made visible, at the same time the active form can be kept hidden in memory too.  By doing this only one form will be in visible state rather than crowding all the forms in the application window. 

Let us try an example before exploring other aspects of this interesting method.

  1. Import the Employees Table and Employees Form from Northwind.mdb (or Northwind.accdb) sample database.

    Check the sample image given below.

  2. Rename the Employees form as Employees1.
  3. Open the Employees1 Form in Design View.
  4. Add a label control on the Header of the Form and change the Caption value to 1, change the Font-size to 16 and the fore-color to White or any other bright color suitable for the background.
  5. Expand the Footer of the Form.
  6. Add two Command Buttons in the Form Footer as shown above.
  7. Click on the left side Button to select it.
  8. Display it’s Property Sheet (F4).
  9. Change the Name property value to Back and change the Caption property value to << (two less than symbols).
  10. Select the On Click Event property and select [Event Procedure] from the drop-down list and click on the build (. . .) button to open the VBA editing window with the empty Sub-routine starting and ending lines: Private Sub Back_Click() . . . End Sub.
  11. Copy the following Code and Paste them over-writing the sub-routine lines in the VBA Module:

    Private Sub Back_Click() ForwardBack "B", Me.Name End Sub

    Note: ForwardBack() is a Function we are going to write and add to the Standard Module.

  12. Repeat step-7 to step-10 for the right-side Command Button by changing the Name property value to Forward and the Caption property value to >> (two greater than symbols).
  13. Copy the following Code and Paste them over-writing the sub-routine starting and ending lines in the VBA Module:

    Private Sub Forward_Click() ForwardBack "F", Me.Name End Sub

  14. Save and close the Form.
  15. Copy and paste the following Code into a Standard Module in your Database and save it:
    Public Function ForwardBack(ByVal strStatus As String, ByVal strForm As String)
    Dim frmCount As Integer, j As Integer
    
    On Error GoTo ForwardBack_Err
    
    'get count of open forms in memory
    frmCount = Forms.Count - 1
    For j = 0 To frmCount
    Select Case strStatus
          Case "B" 'Move Back
            If Forms(j).Name = strForm And j - 1 >= 0 Then
               DoCmd.SelectObject acForm, Forms(j - 1).Name, False
               Forms(strForm).Visible = False
               Forms(j - 1).Visible = True
               Exit For
            End If
         Case "F" 'Move Forward
            If Forms(j).Name = strForm And frmCount > j Then
               DoCmd.SelectObject acForm, Forms(j + 1).Name, False
               Forms(strForm).Visible = False
               Forms(j + 1).Visible = True
               Exit For
            End If
    End Select
    Next
    
    ForwardBack_Exit:
    Exit Function
    
    ForwardBack_Err:
    MsgBox Err & ":" & Err.Description, , "ForwardBack()"
    Resume ForwardBack_Exit
    End Function

    ForwardBack() Function needs two parameters when called:

      First parameter can be either "B" or "F".

    1. Use “B” as the first parameter value, when called from the << (Go Back) labeled Command Button Click Event Procedure and with "F" for >> (Go Forward) labeled Command Button click Event Procedure.
    2. The second parameter is the active Form's name, can be passed with the Me.Name statement.
  16. Make 4 more copies of the Employees1 Form and name them as Employees2 to Employees5. The number in the header labels also change to 2,3,4 and 5 in their respective Forms.

    Since, all the forms are copies of the same form this number will help us to distinguish one from the other.

  17. Create a Macro similar to the sample image shown on top of this page, to open the forms and keep them hidden in memory except one.  You may define any one of the form’s Window Mode as Normal to make that form visible, in the Application Window, while all other forms stays hidden.
  18. Save the Macro with the name Macro1.

Now, it is time to test our Project.  First let us test our Project manually without using Macro1.

  1. Open Forms Employees1 to Employees5 manually, one by one from the Navigation Pane.
  2. You have now all the Forms opened in the Application Window.  The Employees5 form is on top, with the form header label showing number 5.

    When you open several forms this way they are placed in memory in the opened FORMS Collection Object in an indexed order.  The first form opened is addressable with zero index number as Forms(0) or Forms(“Employees1”) in VBAForm’s Name can be retrieved from the Name property of the opened form, like Forms(0).Name and similarly other properties of the form can be addressed as wellThe second Form opened will have index number 1 and so on.   The suffix numbers we have added to the Employees Form have nothing to do with this internal index numbers.  You can open those Forms in any order you like, not necessary that you should start with Employees1 and end with 5.  It is better that way initially to test the program.

  3. Click on the Command Button with the >> (Go Forward) symbols on it to move forward to the next form, but nothing will happen because this is the last form in the opened Forms Collection now.
  4. Click on the Command Button with the << (Go Back) symbols to make Employees4 form visible and to place the current form Employees5 in hidden state in memory.
  5. Repeat step-2 to make Employees3 form visible and continue doing this till you reach the Form Employees1. 

    At this stage clicking on the back button (<<) on this Form will not show any respons because this is first form we have opened.  But, if you have opened these forms in different order then it will work.  When you reach the Employee1 form all the other Forms are in hidden state in memory because our main program is made to do that.

  6. Try to move forward, by clicking on the >> button, to the other forms and make them visible one by one, hiding the earlier forms.

Tip: If you want to make some changes on any of these forms, while the form is in hidden state in memory, you may right-click on the Form name in the navigation pane and select Design View.  The Form will appear in Design View.

You can easily close all the opened forms (both hidden as well as visible ones) with a single click, while shutting down the Application with a simple VBA routine CloseAllForms(). The CloseAllForms() program can be called from a Command Button click Event Procedure and before executing the DoCmd.Quit statement.  You may even run the program directly from the VBA window, while testing the above procedures.  Copy and paste the following Code into a Standard Module of your Project:

Public Function CloseAllForms()
Dim j
'when no forms are in open state
'then forms.count -1 returns -1 and
'the For...Next loop is not executed

'When a form is closed
'other forms in memory are re-indexed automatically
For j = 0 To Forms.Count - 1
  DoCmd.Close acForm, Forms(0).Name
Next
End Function

Click in the middle of the Code and press F5 to run it and close all the opened forms.

For the above test we have opened all Forms manually.  You may run the macro (Macro1), we have created earlier, to open all the forms at once and keep all of them hidden except one.

If you want to run this macro automatically, immediately on opening the database, to open all the forms and keep them hidden then rename the macro (Macro1) as Autoexec.

Opening of Forms can happen during normal operations by Users and they will be in the order of their opening sequence in memory.  Users can work with the open Forms by clicking on the Command Button with the symbols >> (to Go Forward) or by clicking on the Command Button with the symbols << (to Go Back) Command Buttons on the Form.

Share:

2 comments:

  1. Hi a.p.r.
    Why do you need
    DoCmd.SelectObject acForm, Forms(j - 1).Name, False
    ?

    ReplyDelete
  2. DoCmd.SelectObject acForm, Forms(j – 1).Name, False

    The above command brings the Form that you have opened earlier than the current form, and kept hidden in memory, into view, rather than opening it directly from the navigation pane. The False Parameter at the end says not to open the Form from the Navigation Pane. Form(j-1).Name can refer to any form that you have opened before and you don't need to know the Form Name to address it.

    Please go through the Article and your suggestions, if any, are welcome for a better method.

    Thanks,

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

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 Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Property Query Combo Boxes Custom Wizards DOS Commands Data Objects VBA ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation Class Module Data Type msaccessprocess security advanced Access Security Custom Functions Macros Menus Object Reference Report Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Expression Field Type Fields Form Form Instances Join Methods Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading 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

Base Class and Derived Object Variants

Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in...

Labels

Blog Archive

Recent Posts