REUSING FORMS TO PERFORM STANDARD TASKS WITH THE OPENARGS AND TAG PROPERTIES

When creating applications in a language that's pure code (such as C++), you want to design your code so that you can reuse it for a number of reasons, including the following:

• The main code for the reusable routine must be written only once.
  
  
• After the code is debugged, make sure only that the calling routine passes the correct parameters.
  
  

These two points are also true when you create reusable code in Access. In Access, you also can create reusable objects. The following example looks at a reusable form and VBA code. The form allows the user to search for customers or movie titles, depending on where the search form was called.

Three forms—StandardSearch, CustomersSearchSubform, and MovieTitlesSearchSubform—make up the standard search form routines. The number of SearchSubform-named forms will grow as more areas need to have a search form used. In this case, let's stick with the two, Customers and Movie Titles. To see what you want to accomplish, look at Figure 9.7. You'll start off by working with the Customers form.

By using the same form for different subjects, as in looking for customers or movie titles, you're also providing a consistent interface for your user. This is always desirable.

To look at the calling form side (the form that's initiating the opening of another form), look at two properties that Access provides for your purposes: OpenArgs and Tag.

The first property, OpenArgs, can't be accessed from Design mode. The OpenArgs property is set when a form is opened at runtime with the OpenForm method, off the DoCmd object.

The call to open the search form from the Customers form can be found in the event procedure attached to the OnClick event on the Search button. The routine in Listing 9.3 performs this call.

Listing 9.3. Chap09.mdb: Opening a Reusable Search Dialog

Private Sub Search_Click()
    On Error GoTo Error_Search_Click

    DoCmd.OpenForm "StandardSearch", acNormal, , , acNormal, _
                      acDialog, Me.Name

Exit_Search_Click:

    Exit Sub

Error_Search_Click:

    MsgBox Err.Description
    Resume Exit_Search_Click

End Sub

This routine will be the same whether it's called from the Customers or Movie Titles form. The portion of the code that makes it so versatile is the last parameter, Me.Name, found on the line containing DoCmd, where Name is the name property of the current form (Me). This parameter is the OpenArgs property and can be accessed from the form object that's being opened. Figure 9.8 shows the syntax for the OpenForm method off the DoCmd object, using the Object Browser.

Follow these steps to view a complete list of methods of the DoCmd object in the Object Browser:

You now see the complete list of methods for the DoCmd object in the Object Browser. (For complete information about the Object Browser, see Chapter 2, “Coding in Access 2000 with VBA.”)

By placing Me.Name in OpenArgs, you can copy and paste the Search control and its OnClick event procedure into other forms with no changes. The only other item you need to worry about setting up on the calling form side is the Tag property.

The Tag property is user-definable, which means that Access pays no attention to it whatsoever, and you get to use it however you want. What you'll use it for in this example is to keep track of the field on which you want the standard search routine to perform the lookup in the calling form's recordset. In the Customers form, the routine is called CustomerID. You can see how the Tag property is set in Figure 9.9.

You're now done setting up the calling form. Next, let's look at what makes up the standard search form, StandardSearch. In looking at the standard search form, you are brought back to the OpenArgs property, which the code in Listing 9.4 uses. In the OnLoad event of the StandardSearch form, set the Caption property to reflect the calling form, and set the SourceObject property for the subSearch subform control.

Listing 9.4. Chap09.mdb: Setting Form Properties at Runtime

Private Sub Form_Load()

    On Error GoTo Error_Form_Load

    Me.Caption = Forms(Me.OpenArgs).Caption & " " & Me.Caption

    Me!subSearch.SourceObject = Me.OpenArgs & "SearchSubform"

Exit_Form_Load:

    Exit Sub

Error_Form_Load:

    MsgBox Err.Description
    Resume Exit_Form_Load

End Sub

The Me.Caption line concatenates the caption of the calling form (Customers) with a space and the caption of the standard search form.

In the next line of code, the OpenArgs property of the calling form—in this case, Customers—is concatenated to the literal SearchSubform. This creates the complete name of the search subform, CustomersSearchSubform. It then assigns that to the SourceObject property of the SearchSubform subform control. You can see this subform control in Figure 9.10.

This form has two other interesting procedures. The first one is attached to the OnClick event for each of the Letter command buttons and the AfterUpdate event of the SearchText text box. You can see the SearchText text box in Figure 9.10 (the Unbound control above the property sheet). The function attached to the OnClick event, ap_Requery_SearchSub(), is shown in Listing 9.5 and is in the StandardSearch form's code behind form.

Listing 9.5. Chap09.mdb: Requerying the StandardSearch Subform

Function ap_Requery_SearchSub()

    On Error GoTo Error_ap_Requery_SearchSub

    Select Case Screen.ActiveControl.Name
        Case "txtSearchText"
            Me.Tag = Me!txtSearchText
        Case "All"
            Me.Tag = ""
            Me!txtSearchText = ""
        Case Else
            Me.Tag = Screen.ActiveControl.Caption
            Me!txtSearchText = ""
    End Select
    Me!subSearch.Requery

Exit_ap_Requery_SearchSub:

    Exit Function

Error_ap_Requery_SearchSub:

    MsgBox Err.Description
    Resume Exit_ap_Requery_SearchSub

End Function

This routine assigns a value to the StandardSearch form's Tag property based on which control was used, in the following order:

• The txtSearchText control

• The All button

• One of the Letter controls

The routine then requeries the subform control, subSearch. The value in the Tag property is used in the RecordSource property of the subform's SourceObject—in this case, CustomerSearchSubform.

The other routine mentioned is called from the subform as well. There are three main points of interest concerning the form used as the customer search subform. By using the CustomerSearchSubform form (see Figure 9.11), let's look at these points of interest in order:

• The CustomerSearchSubform's RecordSource is set to the following SQL statement:

  Select CustomerID, FirstName, LastName from Customers where
  Customers.LastName like Forms!StandardSearch.Tag & "*"
  Order by LastName, FirstName;
  

  As you can see, this query refers to the Tag property on the StandardSearch main form. This statement will change to reflect the current subform recordset, but needs to compare the recordset's fields to the standard search form's Tag property. If used by the Movie Titles form, the statement looks like this:

  Select TitleNo, Title from MovieTitles where MovieTitles.Title
  like Forms!StandardSearch.Tag & "*" Order by Title;
  

  The key fields are included in the SQL statements, even though they aren't displayed.

• The Control Source property of the ChoiceDisplay text box, also shown in Figure 9.11, is set to the expression

  ="  " & [LastName] & ",  " & [FirstName]
  

• The OnDblClick event of the ChoiceDisplay text box and the OnClick event of the Choose command button both call the same subroutine from StandardSearch. Here's the event procedure for OnDblClick:

  Private Sub ChoiceDisplay_DblClick(Cancel As Integer)
  
      dummy = Me.Parent.ap_StandardSearchChoice()
  
  End Sub
  

Listing 9.6 shows the code for ap_StandardSearchChoice(). This routine clones the calling form's recordset, looks up the key value of the chosen value in the clone, and then sets the bookmark of the calling form to that record.

Listing 9.6. Chap09.mdb: Locating the Record for the Choice Made

Public Function ap_StandardSearchChoice()

      Dim frmCalling As Form        '-- Form that called Search Form
      Dim frmSearchSub As Form      '-- Search Form
      Dim dynCalling As Recordset   '-- Recordset Clone of Calling Form

      On Error GoTo Error_ap_StandardSearchChoice

      Set frmSearchSub = Forms!StandardSearch!subSearch.Form
      Set frmCalling = Forms(frmSearchSub.Parent.OpenArgs)
      Set dynCalling = frmCalling.RecordsetClone

      '-- Check to see if the search value is numeric or not.
      '-- Search in cloned recordset for chosen value.

      If VarType(dynCalling(frmCalling.Tag)) <> vbString Then
        dynCalling.FindFirst frmCalling.Tag & " = " & _
                                  frmSearchSub(frmCalling.Tag)
      Else
        dynCalling.FindFirst frmCalling.Tag & " = '" & _
                                  frmSearchSub(frmCalling.Tag) & "'"
      End If

      '-- If a record is found, set the bookmark of the calling form.

      If dynCalling.NoMatch Then
        MsgBox "An Error has occurred, No match record found!", 32, _
                  "Search Error!"
      Else
        frmCalling.Bookmark = dynCalling.Bookmark
      End If

      dynCalling.Close

      DoCmd.Close acForm, "StandardSearch"

Exit_ap_StandardSearchChoice:

    Exit Function

Error_ap_StandardSearchChoice:

    MsgBox Err.Description, vbCritical, "Search Error"
    Resume Exit_ap_StandardSearchChoice

End Function

Now that all the code has been created, if you want to have the search routine work for the Movie Titles form, you only have to do the following:

Select TitleNo, Title from MovieTitles where MovieTitles.Title
like Forms!StandardSearch.Tag & "*" Order by Title;

="  " & [Title]

One technique demonstrated in this section was swapping in a form name into the SourceObject property of a subform. In this case, it was used to substitute the version of the search subform that was necessary for the subject being sought—Customers or Movie Titles. This technique is also used for swapping in pages used with the Tab control. The following section further discusses this technique.