Unbind an Access Form

You may use an Access form bound to a specific table as a way to update that table's records. But you may want to use the same form as a data entry-only form, using VBA to validate fields before writing data to a record on a table. Here's a procedure that unbinds most of the controls that may be found on a form, and optionally remove the form's record source.

It works on combo boxes, text boxes and list boxes, and is similar to the IsComplete procedure I posted in Miscellaneous Access VBA Macros. All we are doing here is passing in a reference to a Form object, then looping through each of its controls and determining its type with the Typename function.

Once we know the type of control, we set a typed reference to it, so we can use Intellisense to select the ControlSource property. By setting its value to "", we clear out any existing control source for that control object. This effectively unbinds the control from the table source.

I've also included an option to remove the form's Record Source, if you want to sever it completely from a table it may be bound to. This option is assumed false unless otherwise specified, so you can skip this parameter if you don't want to use it.

Sub UnbindForm(ByRef frm As Access.Form, _
Optional removeRecordSource As Boolean = False)
' unbinds (most of) the controls on whatever form is passed to it
' if removeRecordSource is true, Record Source property is reset

Dim ctl As Access.Control

Dim cbo As Access.ComboBox
Dim lst As Access.listbox
Dim txt As Access.TextBox
Dim chk As Access.CheckBox

If removeRecordSource Then
  frm.RecordSource = ""
End If

For Each ctl In frm.Controls
  Select Case TypeName(ctl)
    Case "ComboBox"
      Set cbo = ctl
      cbo.ControlSource = ""
    Case "ListBox"
      Set lst = ctl
      lst.ControlSource = ""
    Case "TextBox"
      Set txt = ctl
      txt.ControlSource = ""
    Case "CheckBox"
      Set chk = ctl
      chk.ControlSource = ""
ControlSource
  End Select
Next ctl

End Sub

Note that according to Access Visual Basic Help, "the ControlSource property doesn't apply to check box, option button, or toggle button controls in an option group. It applies only to the option group itself." So if your checkboxes appear in an option group, the code should either crash or fail to work on those controls.

Usage:

Sub RunMe()

Dim frm As Access.Form
Set frm = Forms!frmMy_Form_Name

Call UnbindForm(frm)

End Sub

Related Articles:

• April 29, 2008 -- Contribute to this site!
• September 2, 2009 -- Office Assistant object model examples
• December 10, 2009 -- CC without attachments using Outlook VBA