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