Unbind an Access Form
June 15, 2009 • JP • No Comments • Rate This Article
• Links to this article
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.
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:
Dim frm As Access.Form
Set frm = Forms!frmMy_Form_Name
Call UnbindForm(frm)
End Sub
Access ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: A VBA-Based Backup Solution
Next Post: Populate Access combo box from a VBA array
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].