Even more miscellaneous Access VBA routines

Here are some more VBA procedures that may be useful in your Access database.

Use the Tag Property

I tag my forms and controls with keywords I can check later for various reasons. What does that mean? Each form and control has a Tag property which can be set manually during design, or programmatically at runtime.

To edit the Tag property of a form or control, just view any form in Design view, press Alt-Enter or F4 to open the Properties list and make sure you're on the "Other" tab of the resulting dialog box.

Use the dropdown combo box to switch between the form and any of its controls. Or you can just leave the dialog box open and mouse-click on any of the form controls; the dialog box will update itself to reflect whatever control is currently selected. (Hint: that is a quick way to update the same property for multiple controls very quickly!)

For example, if I don't want a user to see a certain form, I'll tag the form with the word "hidden" and then use the following routine to check if the form is tagged before displaying it.

Function CheckTag(ByVal formOrControl As Object, ByVal tagName As String) As Boolean
' checks whether specified object (form or control) has a given string in Tag Property

  Dim frm As Access.Form
  Dim ctl As Access.Control

  Dim txt As Access.TextBox
  Dim lst As Access.ListBox
  Dim cbo As Access.ComboBox
  Dim chk As Access.CheckBox
  Dim opt As Access.OptionButton

  If InStr(TypeName(formOrControl), "Form") > 0 Then  ' it's a form
    Set frm = formOrControl
    If frm.Tag = tagName Then
      CheckTag = True
    End If
  Else  ' it's a control
    Set ctl = formOrControl

    Select Case TypeName(ctl)
      Case "TextBox"
        Set txt = ctl
        If txt.Tag = tagName Then
          CheckTag = True
        End If
      Case "ComboBox"
        Set cbo = ctl
        If cbo.Tag = tagName Then
          CheckTag = True
        End If
      Case "ListBox"
        Set lst = ctl
        If lst.Tag = tagName Then
          CheckTag = True
        End If
      Case "CheckBox"
        Set chk = ctl
        If chk.Tag = tagName Then
          CheckTag = True
        End If
      Case "OptionButton"
        Set opt = ctl
        If opt.Tag = tagName Then
          CheckTag = True
        End If
    End Select
  End If

End Function

If we pass a Form Object to the code above, the word "Form" is present in the object name. Lame, I know, but it's the only way I know to check if the object is a form (Typename returns the name of the form, not the object type!). Otherwise it's a Control object, and the code figures out what type of control it is and acts accordingly. I threw checkboxes and option buttons in there, but I can't really see a situation where they would be required.

If I want to create a set of required fields on a form, I tag each required control with the word "required" and then I can easily check if all required fields are completed before allowing the code to continue…

Function RequiredFieldsFilled(ByVal frm As Access.Form) As Boolean
' returns True if all controls tagged "required" are filled in
Dim ctl As Access.Control

' assume true
RequiredFieldsFilled = True

For Each ctl in frm.Controls
  If ((CheckTag(ctl, "required")) And (Nz(ctl.Value) = "")) Then
    RequiredFieldsFilled = False
    Exit For
  End If
Next ctl

End Function

This code (and the CheckTag() function) may be adapted for Excel VBA with minimal editing.

Setting your form's caption

When instantiating your form, you may set the form's caption at runtime, or change it as needed depending on user selection during operation of your application. These are the procedures I use to do so.

Access version:

Function SetCaption(ByRef frm As Access.Form, ByVal captionString As String)
' set any form's caption
  frm.caption = captionString
End Function

Excel version:

Function SetCaption(ByRef frm As MSForms.Userform, ByVal captionString As String)
' set any form's caption
  frm.caption = captionString
End Function

All you do is pass the Form object and the caption you want to use. For example, if I have a form called "MyForm" in my project …

Public Sub StartForm()
' show my Excel Userform
Dim frm As MyForm

Set frm = New MyForm
SetCaption(frm, "Welcome to my form!")
frm.Show

End Sub

… this code instantiates my form, sets the caption and then displays the form.

Convert TextBox values to Dates

The textboxes I use to input dates are formatted to appear as dates. The problem is that they are actually text values. The CDate() function alone didn't work, so I had to write this function. It takes a string (i.e the text date from a textbox) and returns it as a date.

Function ConvertToDate(dateString As String) As Date
' converts textbox dates formatted as dates to "real" dates
  ConvertToDate = CDate(Left$(dateString, 2) & "/" & Mid$(dateString, 3, 2) & "/" & Right$(dateString, 4))
End Function

Sample usage:

Dim dte As Date
dte = ConvertToDate(Nz(TextBox1.Value))

Check your listbox

Listboxes often have to be checked to see if anything is selected. For example, you may want to enable a command button when someone makes a selection from a listbox, or you may want to run a de-selection routine, but only if something is already selected (otherwise it's just a waste of cycles).

This function takes a ListBox Object and returns True if any of the values are selected.

Function IsAnythingSelected(lst As Access.ListBox) As Boolean
' returns true if anything in a given listbox is selected

  Dim i As Long

  For i = 0 To lst.ListCount - 1
    If lst.Selected(i) Then
      IsAnythingSelected = True
      Exit For
    End If
  Next i

End Function

Related Articles:

• January 30, 2012 -- Remove Flags from Inbox Emails
• January 9, 2012 -- Annoying Website Name Generator in VBA and PHP
• December 26, 2011 -- Validating Userform Data