Revised A Couch 9th October 2011 : recently we spotted that there can be a performance issue caused by changing the ControlSource on controls when the form has a RecordSource, the solution is to first unbind the form setting the RecordSource = "", then define the ControlSource for each control, after which we set the form's RecordSource.

If you are designing forms which are bound to data sources, such as a table or a query, whilst that will work for a native Access database, if you then connect to a data source such as SQL Server/Oracle or DB2 you may need to introduce a mechanism for searching and filtering the data; otherwise you will suffer from performance issues (the inefficiency of using ctrl-f to search for data was discussed in the article http://www.upsizing.co.uk/Art33_DesignChanges2.aspx). There are a number of ways to tackle this problem. In this article we look at one possible solution which involves switching a form between using bound and unbound controls. This method mimises the amount of design work, in that it uses the same controls on the form to both show the data (when bound) and then search for the data (when unbound). It also allows the user to efficiently search by entering values in any combination of the available controls.

The methods described here works for both single and continuous forms.

Before diving into the code, we need to understand how data is bound to a form, and the controls on the form.

Record Source and Control Source Data on a Form

We start with a very simple form as shown below:-

This form has a Record Source property specifying the table, query or block of SQL which supplies the data, the example below shows that this form is bound to the Customers table.

Bound controls, then have a Control Source property which refers to one of the columns in the Record Source.

Dynamically Unbinding and Binding a Form

If we take this form and unbind the form clearing the value in the Record Source, and then unbind each control by clearing the the Control Source. Then we have the perfect search form for users to use when searching for data, as shown below:-

On the form we have created three buttons which call code in a general module. The first button called “Search” calls the module function modDM_UnbindTheForm().

Function modDM_UnbindTheForm()
  'This routine will unbind the form from a source of data
  DoCmd.Echo False
  Dim frm As Form
  Set frm = Screen.ActiveForm
  ' unbind the form
  frm.RecordSource = ""
  ' unbind the controls
  Dim ctrl As Control
  For Each ctrl In frm.Controls
    On Error Resume Next
    If ctrl.Tag <> "X" Then
    ctrl.ControlSource = ""
    ' clear values if changing search criteria
    ctrl.Value = Null
  Else
    ' Hide any controls with a tag marked X
    ctrl.Visible = False
  End If
  Next
  DoCmd.Echo True
End Function

In the above code we are using the controls Tag property, when set to “X” to indicate that a control is not available for searching and will be hidden (for example in command buttons or calculated fields).

We have also added code in a general module linked to the “Show All” button which can rebind the form and all the controls on the form, this function is called passing the SQL block or table/query to be displayed in the form.

Function modDM_BindTheForm(strSQL As String)
  ' This routine will bind the form from to a source of data
  ' We do this by opening the recordset and matching control names
  DoCmd.Echo False
  Dim frm As Form
  Set frm = Screen.ActiveForm
  Dim rst As Recordset
  Dim db As Database
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
  Dim fld As Field
  ' Bind the controls
  For Each fld In rst.Fields
    ' Attempt to bind the control
    On Error Resume Next
    frm.Controls(fld.Name).ControlSource = fld.Name
  Next
  ' show any controls with a tag marked X
  Dim ctrl As Control
  For Each ctrl In frm.Controls
    If ctrl.Tag = "X" Then
      ctrl.Visible = True
    End If
  Next
  ' Now rebind the data
  frm.RecordSource = strSQL
  rst.Close
  Set rst = Nothing
  db.Close
  DoCmd.Echo True
End Function

To make the binding process simple, we have ensured that the control names match the fields available in the record source; this means we can use a simple assignment frm.Controls(fld.Name).ControlSource = fld.Name to bind a control.

Using the Tag Property to assist in searching

The Tag property, associated with the form, and all controls on the form, provides you with a property for you to make it mean whatever you wish, and we will make extensive use of this property in this article. If you look at the code above you can see that it indicates controls to be hidden during searching (when it contains “X”).

For searching we will need to build up an SQL Where clause to filter the results. But we need to construct the filter syntax based on whether the field is a numeric, text, date or Boolean (Yes/No) data type. This information will be encoded in the Tag property for the bound/unbound controls.

In a module called DM, we have some test code which will open the form in design and then run through the controls on the form setting a value in the Tag field to indicate the type of data to be held in the control. We work this out by looking at the field data types in the form’s recordset, and having the convention that the control names match the underlying field names.

Sub modDM_TestExampleSettingTheTags()
  modDM_SetFormTagsToControlDataType "Suppliers"
End Sub

Sub modDM_SetFormTagsToControlDataType(strFormName As String)
  ' open a form in design view
  ' For any control which does not have a tag X
  ' Save the datatype for the control in the tag field
  ' X - Ignore control as this is a calculated control
  ' D - Date field
  ' T - Text field
  ' N - Numeric field
  ' B - Boolean

  Dim ctrl As Control
  Dim frm As Form
  Dim rst As Recordset
  Dim fld As Field
  Dim strSQL As String
  DoCmd.OpenForm strFormName, acDesign
  Set frm = Forms(strFormName)
  strSQL = frm.RecordSource
  Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
  For Each fld In rst.Fields
    On Error Resume Next
    Set ctrl = frm.Controls(fld.Name)
    If Err = 0 Then
      ' Set the controls tag to match the fields data type
      Select Case fld.Type
        Case dbDate: ctrl.Tag = "D"
        Case dbMemo: ctrl.Tag = "T"
        Case dbText: ctrl.Tag = "T"
        Case dbBoolean: ctrl.Tag = "B"
        Case Else: ctrl.Tag = "N"
      End Select
    End If
    Err.Clear
  Next
  ' now set all other controls to have a tag of X
  For Each ctrl In frm.Controls
    If TypeOf ctrl Is CommandButton _
    Or TypeOf ctrl Is SubForm Or TypeOf ctrl Is CheckBox Then
    ' exclude our search buttons
      If ctrl.Name <> "cmdSearch" And ctrl.Name <> "cmdSearchResults" _
      And ctrl.Name <> "cmdShowAll" Then
        ctrl.Tag = "X"
      End If
    End If
  Next
  RunCommand acCmdSave
  DoCmd.Close
  Set frm = Nothing
End Sub

In the above we have also chosen not to hide three special command buttons called “cmdSearch”, “cmdShowAll” and “cmdSearchResults”.

The example below shows the result of executing this against our orders form, where the tag value “T” has been added to the CustomerID text control.

In this example the subform control has been manualy been assigned a Tag “X”, to hide the subform during searching.

Another useful routine (which is almost identical to the code we used earlier for unbinding controls) in the module DM, will open the form in design view and unbind the form, and all controls, except those controls marked with the tag “X” which are hidden. This means our form will open unbound, ready for searching.

Sub modDM_TestExampleUnbindTheForm()
  modDM_PrepareUnboundForm "Orders"
End Sub

Sub modDM_PrepareUnboundForm(strFormName As String)
' Prepare a form by unbinding it and preparing for opening to search for data
  Dim frm As Form
  DoCmd.OpenForm strFormName, acDesign
  Set frm = Forms(strFormName)
  ' unbind the form
  frm.RecordSource = ""
  ' unbind the controls
  Dim ctrl As Control
  For Each ctrl In frm.Controls
    On Error Resume Next
    If ctrl.Tag <> "X" Then
      ctrl.ControlSource = ""
    Else
      ' Hide any controls with a tag marked X
      ctrl.Visible = False
    End If
  Next
  RunCommand acCmdSave
  DoCmd.Close
  Set frm = Nothing
End Sub

Searching

The final button performs the search:-

This uses the following code to search for results, and then bind the form and controls to display the search results.

Function modDM_SearchTheForm(strSQL As String)
  ' The basic table or query of sql string to be searched.
  Dim frm As Form
  Dim ctrl As Control
  Dim strFullSQL As String
  Dim strCriteria As String
  Set frm = Screen.ActiveForm
  strCriteria = ""
  ' Unbind the recordsource
  If frm.RecordSource <> "" Then
    ' User has pressed this button without first searching
    Exit Function
  Else
    frm.RecordSource = ""
  End If
  For Each ctrl In frm.Controls
    If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ListBox Or TypeOf ctrl Is ComboBox Then
      If ctrl.Tag <> "X" And ctrl.Value <> "" Then
        If strCriteria <> "" Then
          strCriteria = strCriteria & " AND "
        End If
        ' we could wild card the matches here
        strCriteria = strCriteria & "[" & ctrl.Name & "]"
        Select Case ctrl.Tag
          Case "D": strCriteria = strCriteria & " =#" & _
               Format(ctrl.Value, "mm/dd/yyyy") & "#"
          Case "T": strCriteria = strCriteria & " like '*" & _
               Replace(ctrl.Value, "'", "''") & "*'"
          Case "B":
            If ctrl.Value = 1 Then
              strCriteria = strCriteria & " <> 0"
            End If
          Case "N": strCriteria = strCriteria & " = " & ctrl.Value
          Case Else
        End Select
      End If
    End If
SkipControl:
  Next
  strFullSQL = "SELECT * FROM " & strSQL & " WHERE " & strCriteria
  If strCriteria = "" Then
    MsgBox "No search criteria specified", vbInformation, "No Search Criteria"
    Exit Function
  End If
  ' clear the search boxes unless you want them shown in the new record position
  For Each ctrl In frm.Controls
  If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ListBox Or _
      ctrl Is ComboBox Then
    If ctrl.Tag <> "X" And ctrl.Value <> "" Then
      ctrl.Value = Null
    End If
  End If
  Next

  ' optional test that we get a result
  Dim rst As Recordset
  Dim fld As Field
  Set rst = CurrentDb.OpenRecordset(strFullSQL, dbOpenDynaset, _
              dbSeeChanges)
  If rst.EOF Then
    MsgBox strCriteria, vbInformation, "No Records Matched"
    Exit Function
  End If
  DoCmd.Echo False
  For Each fld In rst.Fields
    ' Attempt to bind the control
    On Error Resume Next
    frm.Controls(fld.Name).ControlSource = fld.Name
  Next
  ' Now rebind the data
  frm.RecordSource = strFullSQL
  DoCmd.Echo True
End Function

Note that for searching on text fields we are using the Replace function to avoid problems with embedded apostrophes.

Comments

Opening unbound forms and controls is fast and you have ultimate flexibility in viewing your data. To put the concept in a nutshell means we needed the following procedures:

• Search routine
• Unbind routine
• Bind routine
• Tagging of controls

You may well find that you want to hide Yes/No, checkbox fields from the searching, or at least give them more careful consideration. You probably want a checkbox to either search for True, False or not be included in the search, but a checkbox gives a two-state result true or false, so knowing when to exclude it from the search can be problematic.

You could also consider using the BuildCriteria function to help in your searches when forming valid search criteria, but in our example we have chosen to explicitly code these operations.

Download Code

The sample code can be downloaded from here for Access 2007/2010 Databases.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page