MANIPULATING CONTROLS THROUGH CODE

Using VBA to manipulate controls is easy. Access lets you control the various properties needed, even at runtime.

Figure 10.31 shows a form that's used for a main switchboard. This form uses an option group with toggle buttons to emulate a tabbed menu.

Examining the Pieces of the Option Group Menu Form

Three different types of Access objects make up this Option Group menu form: a table named Menus, a form named ManipulatingControlsExample, and the VBA code behind the form. Each category used on this menu has a number of tools assigned to it. This is done by using the Tag property of each command button, which will be discussed in a moment. First, look at the Menus table, which is used to keep track of the number of tools for each category. The current contents of this table are as follows:

The MenuID field is used for locating the corresponding tab value during program execution. The NumberOfTools field is used to control an array. A description is there for your own purposes, such as documentation.

Introducing the ManipulatingControlsExample Form

Figure 10.32 shows the ManipulatingControlsExample form in Design view. Notice that most of the controls are stored on page two of the form, after the Page Break control, and are moved around when the form is loaded.

Also notice in Figure 10.32 that the Tag property of the first command button contains 1;1. The first 1 represents which toggle button this command button corresponds with; the second 1 refers to the order on the menu. The second command button on the first tab choice will have a 1;2 for its Tag property, and so on.

The next items to examine are the various code routines used. All the code is behind the ManipulatingControlsExample form.

Looking at the Code Behind the Form

Before looking at the individual routines, look at the declarations section, which contains various dimension statements:

Option Compare Database   'Use database order for string comparisons
Option Explicit
Option Base 1

Dim snpMenus As Recordset
Dim aryCurrTools() As String
Dim dblTopStorage As Double

The Option Base 1 line sets to 1 the base for arrays used for this form. The following table lists the variables and describes their purposes. Note that all these variables are used throughout the form.

The first subroutine to look at is attached to the form's OnOpen event. This code establishes the reference to the Menus table with the RecordSet type variable, snpMenus, which is shown in the following code:

Private Sub Form_Open(Cancel As Integer)

   Set snpMenus = CurrentDb.OpenRecordset("Menus", dbOpenSnapShot)

End Sub

The Form_Load routine in Listing 10.11 establishes the first view of controls for the menu.

Listing 10.11. Chap10.mdb: Loading the Initial Menu Settings

Private Sub Form_Load()

   Dim i As Integer
   Dim ctlCurrTool As Control

   '-- Establish where to store the prior tab choice by
   '-- picking the first tool top property
   dblTopStorage = Me!cmdTool1.Top

   '-- Establish the maximum number of controls for this tab choice
   ReDim aryCurrTools(snpMenus!NumberOfTools + 1) As String

   For i = 1 To Me.Tag

      Set ctlCurrTool = Me("cmdTool" & LTrim(Str(i)))

      '-- If the tool is used on the first menu pull it in
      '-- to the active tool array
      If GetToolMenu(ctlCurrTool.Tag) = 1 Then

         aryCurrTools(GetToolOrder(ctlCurrTool.Tag)) = _
                ctlCurrTool.Name

      End If

   Next

   '-- Add on the Close button
   aryCurrTools(snpMenus!NumberOfTools + 1) = "cmdClose"

   '-- Moves the Buttons to their positions.
   SetToolForm

End Sub

This routine performs the following steps:

dblTopStorage = Me!cmdTool1.Top

ReDim aryCurrTools(snpMenus!NumberOfTools + 1) As String

For i = 1 To Me.Tag
   Set ctlCurrTool = Me("cmdTool" & LTrim(Str(i)))

   '-- If the tool is used on the first menu pull it in
   '-- to the active tool array
   If GetToolMenu(ctlCurrTool.Tag) = 1 Then

     aryCurrTools(GetToolOrder(ctlCurrTool.Tag)) = _
            ctlCurrTool.Name

   End If

Next

Private Function GetToolMenu(strToolTag As String) As Integer

    '-- Strips of the Menu level portion of the tools command
    '-- button tag property

    GetToolMenu = CInt(Left$(strToolTag, InStr(strToolTag, ";") - 1))

End Function

Private Function GetToolOrder(strToolTag As String) As Integer

    '-- Strips off the order portion of the tools command button
    '   tag property

    GetToolOrder = CInt(Mid$(strToolTag, InStr(strToolTag, ";") + 1))

End Function

aryCurrTools(snpMenus!NumberOfTools + 1) = "cmdClose"

Private Sub SetToolForm()
'-- Moves the tools command buttons around on the form

   Dim dblCurrStart As Double, dblTop As Double
   Dim intCurrTool As Integer

   '-- Establish the initial positions of the Controls
   dblCurrStart = Me!recToolBox.Left + 25
   dblTop = Me!recToolBox.Top + 25

   '-- Loop through the array and set the layout properties
   '-- for each of the controls
   For intCurrTool = 1 To UBound(aryCurrTools)

     Me(aryCurrTools(intCurrTool)).Top = dblTop
     Me(aryCurrTools(intCurrTool)).Left = dblCurrStart
     dblCurrStart = dblCurrStart + _
        Me(aryCurrTools(intCurrTool)).Width + 10

   Next intCurrTool

End Sub

These routines are run when the form is first opened. Some of these routines are also used when a new toggle button is chosen. The main routine, shown in Listing 10.15, is attached to the After Update event of the option group control named optTabs.

Listing 10.15. Chap10.mdb: Switching Menu Choices

Private Sub optTabs_AfterUpdate()
   Dim iNumControls As Integer, intIndex  As Integer
   Dim ctlCurrTool As Control

   '-- Moves the prior level tools off the first page.
   ClearCurrTools

   '-- Get the total controls, and moves to the current menu level
   iNumControls = CInt(Me.Tag)
   snpMenus.FindFirst "[MenuID] = " & Me!optTabs

   '-- Stores the initial number of controls for the new menu level
   ReDim aryCurrTools(snpMenus!NumberOfTools + 1) As String
   For intIndex = 1 To iNumControls

      Set ctlCurrTool = Me("cmdTool" & LTrim(Str(intIndex)))

      '-- If for the current option group choice
      If GetToolMenu(ctlCurrTool.Tag) = Me!optTabs Then

         aryCurrTools(GetToolOrder(ctlCurrTool.Tag)) = ctlCurrTool.Name
      End If

   Next

   '-- Add on the Close button
   aryCurrTools(snpMenus!NumberOfTools + 1) = "cmdClose"

   '-- Moves the Buttons to their positions.
   SetToolForm

End Sub

Because this routine is like the Form_Load routine in Listing 10.11, you can follow what's happening. One difference between this routine and the Form_Load routine is that this routine calls the ClearCurrTools routine to move the previous command buttons off the page, shown in Listing 10.16.

Listing 10.16. Chap10.mdb: Clearing the Current Settings

Private Sub ClearCurrTools()
   '-- This routine clears the previous tab choice by
   '-- storing them on the second page
   Dim intIndex As Integer

   For intIndex = 1 To UBound(aryCurrTools)
      Me(aryCurrTools(intIndex)).Top = dblTopStorage
   Next

End Sub

The other difference is that the optTabs value is used rather than the hard-coded value of 1 in the Form_Load routine.

If you open the ManipulatingControlsExample form and click the three toggle buttons, you'll see how the controls change with the option group. You can create many other form types by using methods like this one.