MS Access VBA: Generating Code for Handlers

I’ve been working with “unbound forms” and, ay ay ay, what a pain in the butt. Unbound forms are regular forms, except that the controls aren’t associated with any data source. An unbound form is like a panel of controls, without wires behind them, and no information being shown or recorded. It’s like a prop without a performance. It’s weird to use an Access form that doesn’t seem to work, because the data structure behind it doesn’t exist — without information, there’s no animation.

Radio buttons stop automatically behaving as a group. Clicking on a radio button will toggle it on or off, but won’t affect other radio buttons. (As far as I know, there’s no way to specify an option group to Access.)

So, you have to write handlers to set the values of related radio buttons. I’m in the middle of writing a table synchronizer, and I have 22 radio buttons to handle. They’re grouped in pairs. The logic is straightforward, but the prospect of clicking a zillion times in Access, to create the event handlers, much was depressing.

I turned to code generation to write the code for me.

This generator relies on my naming the form fields consistently. For each row, the naming is consistent. The field named “webFname” is a text field. “webFnameR” is the radio button to choose that field. “accessFname” is also related, as is “accesFnameR” its radio button. The “web” fields are values from the web database, and “access” fields are from the access database.

The form looks a little like this:

  WEB                    ACCESS
fname ( ) [___________]   ( ) [_____________]
lname ( ) [___________]   ( ) [_____________]
 cell ( ) [___________]   ( ) [_____________]
phone ( ) [___________]   ( ) [_____________]
etc...

The code to handle a pair of radio buttons is like this:

Private Sub webFnameR_Click()
    Me.webFnameR.Value = True
    Me.accessFnameR.Value = False
    Me.Repaint
End Sub
Private Sub accessFnameR_Click()
    Me.webFnameR.Value = False
    Me.accessFnameR.Value = True
    Me.Repaint
End Sub

There’s not much to it, but it’s pretty verbose. It’s also very consistent, so it’s eligible for code generation.

Here’s the code generator.

    Dim fields() As Variant
    fields = Array("Fname", "Lname", "Hcode", "HPhone", "Cell", "Hstreet", _
        "Hcity", "Hstate", "Hzip", "HCountyName", "Email")
   
    For Each f In fields
           
        Debug.Print "Private Sub web" & f & "R_Click()"
        Debug.Print "    Me.web" & f & "R.Value = True"
        Debug.Print "    Me.access" & f & "R.Value = False"
        Debug.Print "    Me.Repaint"
        Debug.Print "End Sub"
        Debug.Print "Private Sub access" & f & "R_Click()"
        Debug.Print "    Me.web" & f & "R.Value = False"
        Debug.Print "    Me.access" & f & "R.Value = True"
        Debug.Print "    Me.Repaint"
        Debug.Print "End Sub"
        
    Next

That generates handlers for all the fields listed in fields.

Run it, and then take the debug window output and paste it into your program. I generated 22 event handlers with a single click (plus the up-front time to write this generator).

Incidentally, those field names are consistent in both database tables as well, so writing code to work with the data, field by field, wasn’t too huge. You just create references to the fields using the longhand notation of Me.Controls(“name”).Value instead of the more common Me.name. Example:

        Me.Controls("access" & f) = rst.fields("a." & f)
        Me.Controls("web" & f) = rst.fields("w." & f)

This is more verbose than:

    Me.accessA = rst.aA
    Me.webA = rst.wA

… but the longhand way lets you use strings to get your references to controls and fields. Example:

   for each f in fields
        Me.Controls("access" & f) = rst.fields("a." & f)
        Me.Controls("web" & f) = rst.fields("w." & f)
   next