Order of Events in an Option Group

[cheekybuddha]

I'm pretty sure that a Select Case in the Click event would be better.

Since on every click of an option you need to address all the toggle buttons per frame, I still think the frame's AfterUpdate is the best place for the code.

 

[cheekybuddha]

I was talking about the Click event of the option frame. The options do NOT have click events

Yes, we have established that.

When working with option frames it is traditional to do your processing in the AfterUpdate event (or BeforeUpdate if doing local/immediate validation), so I just suggested the AfterUpdate (of the frame) is also appropriate to muck around with the toggle buttons' appearance, especially in this specific instance where you not only change the appearance of the clicked button, but also must change/reset the appearance of the others.

 

[JMongi]

Where's my ruler...no arguing in class...WHACK!

Everyone's comments are duly noted and my code is working as it should. Thanks!

To wrap things up I used a Select Case block and put it in the Click event of the Option group because it fires last and because it would seem logical to someone coming after me to maintain this.

I'm off early today to a doc appt (I'm fine). Cheers!

 

[cheekybuddha]

For validation I ALWAYS use the Form's BeforeUpdate event.

Very sensible, though we know that some devs like to validate their users on input in certain circumstances, rather than wait till the whole record is saved. Equally sensible if applicable to their particular situation.

Changing the appearance of the buttons (the OP's actual requirement here) would need to occur on input, but is dependent on the inputted data, so AfterUpdate is appropriate, as is Click.

 

[cheekybuddha]

To wrap things up I used a Select Case block and put it in the Click event

So, you then have to hardcode the toggle button names?

 

[cheekybuddha]

 

[cheekybuddha]

@JMongi - you have three option groups in your form in the image in post #21

To change the bevel of the toggle buttons will require repeating your code three times in each frame's AfterUpdate or Click event.

So, it would be worth creating a generic function to do the heavy lifting, along the lines of:

Function FormatToggleButtons( _
  fraOpts As OptionGroup, _
  PropToChange As String, _
  PropValueIfSelected As Variant, _
  PropValueIfUnselected As Variant _
) As Boolean

  Dim ctl As Control, vValue AS Variant

  With fraOpts
    vValue = .Value
    For Each ctl In .Controls
      With ctl
        Select Case .ControlType
        Case acOptionButton, acCheckbox, acToggleButton
          .Properties(PropToChange) = IIf(.OptionValue = vValue, PropValueIfSelected, PropValueIfUnselected)
        End Select
      End With
    Next ctl
  End With
  FormatToggleButtons = (Err = 0)

End Function

If you want to do this for consistent effect across your database you can place this code in a standard module. Or, if it will only apply to this particular form, then make the function Private and place it in the form's module.

Then, in the AfterUpdate (or Click) events for each frame you can put:

Private Sub Frame0_AfterUpdate()
  Call FormatToggleButtons(Me.Frame0, "Bevel", 7, 9)
End Sub

Private Sub Frame1_AfterUpdate()
  Call FormatToggleButtons(Me.Frame1, "Bevel", 7, 9)
End Sub

Private Sub Frame2_AfterUpdate()
  Call FormatToggleButtons(Me.Frame2, "Bevel", 7, 9)
End Sub

You pass in as arguments:

• the option frame,
• the name of the property of the option controls you wish to change,
• the property value for the selected control,
• and the property value for all the other unselected controls.

There is no need to know and hard-code the names of the option controls within the option frame.

hth,

d

 

[moke123]

I was going to make a similar suggestions to David's although for a different reason.

If your option groups are bound you will also have to call the code in the OnCurrent event, or the toggles will not format when opening the form or moving from record to record.

 

[JMongi]

Here is my final code that seems to be working very well. I put it all in a module called modButtonPress:

Option Compare Database
Option Explicit
'These subs format option group toggle buttons based on their selection status
'This provides the look of a depressed button when selected
'Modify the following constants to match the bevel format for a selected and unselected button
'See https://learn.microsoft.com/en-us/office/vba/api/access.commandbutton.bevel
'This code can be modified to format other button properties
Public Const cProperty = "Bevel"
Public Const cSelected = 2
Public Const cUnselected = 1

Public Sub FormatToggleButtons(fraOpts As OptionGroup)
Dim ctl As Control, iValue As Integer

'Store the value of the option group
iValue = fraOpts.Value

'Cycle through the controls in the option group
For Each ctl In fraOpts.Controls
    Select Case ctl.ControlType
        Case acToggleButton
            'Compare the .OptionValue of each control to iValue (the value of the option group)
            ctl.Properties(cProperty) = IIf(ctl.OptionValue = iValue, cSelected, cUnselected)
    End Select
Next ctl

End Sub

Public Sub FormatOnLoad(frm As Form)
Dim ctl As Control

'Cycle through the controls in the form
For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acOptionGroup
        Call FormatToggleButtons(ctl)
    End Select
Next ctl

End Sub

Place the following line in each Click event of the option group.
Call modButtonPress.FormatToggleButtons(Me.ActiveControl)

Place the following line in the Current event of the form.
Call modButtonPress.FormatOnLoad(Me)

 

[cheekybuddha]

Nice!

One minor question (that has no effect on the operation) - why did you name the variable in FormatOnLoad() 'ctlForm'?

Seems un-intuitive to me, when you revisit the code later you might glance at it and expect it to contain forms as you cycle through the controls!

Another thing - had you left the FormatToggleButtons() proc as a function you could have done something like the following which you could call in your form's Load event:

Sub SetUpOptionToggles(frm As Form)

  Dim ctl As Control

' Cycle through the controls in the form
  For Each ctl In frm.Controls
    If ctl.ControlType = acOptionGroup Then
      ctl.OnClick = "=FormatToggleButtons([" & ctl.Name & "])"
    End If
  Next ctl
 
End Sub

In the form's Load event put:

Private Sub Form_Load()
  SetUpOptionToggles(Me)
End Sub

and then you don't even need to write the code for each option group's click event in your module.

However, this would only work if you don't intend to have any other code in those events too (and FormatToggleButtons() must be a function).

 

[JMongi]

One minor question (that has no effect on the operation) - why did you name the variable in FormatOnLoad() 'ctlForm'?

Might be a remnant when I had nested code that used the ctl object twice. So, I changed the name on one of them. I reworked the code to as it is now. Might work back as "ctl". I admit it is confusing a bit. I'll see if it works.

 

[JMongi]

Yep. Now that i recall the other sub, I can use more than one Dim ctl without running into a conflict.

I've edited the previously posted code to reflect the change.

As for your other note, I don't see how that works if the user is clicking on the buttons? while the form is already active?

 

[cheekybuddha]

I don't see how that works if the user is clicking on the buttons?

I guess you'll never know unless you try!

My suggestion sets up the event handlers at runtime rather than you having to do it in Design View.

Where you set up an event handler in Design View to use VBA code you add [Event Procedure] to the appropriate control's event property (.OnClick for the Click event), you can also set it to call a function instead using =SomeFunction().

So, the code I suggested would do that for you when the form loads and you don't have to do the following for each option group.

Place the following line in each Click event of the option group.
Call modButtonPress.FormatToggleButtons(Me.ActiveControl)

 

[JMongi]

I could have tried it, it would have worked, and I STILL wouldn't have understood WHY it worked

But, thanks to your explanation, I now understand. Thanks!

 

[jdraw]

Some interesting points from ChrisO (now deceased) on Event Handlers. An older thread, but he was quite technical and thorough and always contributed.

 

[cheekybuddha]

Post #1 of that thread explains the concept far better than I [could] have done - will raise a glass to Chris