• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Reference the .Tag of active button in an option group

I'm using the attached code to dynamically load the button captions to a 3 x 6 option group. This is giving me a button for each grower that we receive products from. It varies so I don't want to hard code them. The buttons are named tgOwnership(value) and their .value is 11-16, 21-26, 31-36 based on their row/column location.

Since the buttons are dynamic I need to know which button is pressed rather than the optiongroup.value in the afterupdate event,

Right now I'm using a work around:
   MsgBox (Me("tgOwner" & Me.ogOwnership.Value).Tag)

This works fine but it seems there should be an easy way to get the active control of a group?? I know this isn't the typical way to use an option group but it''s still the best control for handling this situation. I checked around the web and found nothing so it must not happen a lot. TIA

Private Sub sLoad_Ownership() '                                 LOAD OWNER
   On Error GoTo err_Handler

   Dim rs As DAO.Recordset
   Dim db As DAO.Database
   Dim strSQL As String
   Dim i As Integer

   Dim datCYStart As Date
   Dim datCYEnd As Date

   datCYStart = [Forms]![frmadmin].[tbCYRangeStart]
   datCYEnd = [Forms]![frmadmin].[tbCYRangeEnd]

   Set db = CurrentDb
   i = 11  ' Row 1 - Column 1

   strSQL = _
   "SELECT DISTINCT tblGrower.GrowerCode, tblGrower.GrowerID " & _
   "FROM tblReceive " & _
   "LEFT JOIN tblGrower ON tblReceive.Ownership = tblGrower.GrowerID " & _
   "WHERE (((tblReceive.Date) Between #" & datCYStart & "# And #" & datCYEnd & "#));"

   Set rs = db.OpenRecordset(strSQL)
   If rs.RecordCount > 0 Then
   With rs
      While Not .EOF
         Me("tgOwner" & i).Caption = rs!GrowerCode
         Me("tgOwner" & i).Tag = rs!GrowerID
         Me("tgOwner" & i).Visible = True
         If i = 16 Then i = 20 ' move to column 2
         If i = 26 Then i = 30 ' move to column 3
         If i = 35 Then MsgBox ("Maximum owners reached!") ' Skip 36 - static cancel button here
         i = i + 1
   End With
   End If

    Exit Sub
    Call LogError(Err.Number, Err.Description, Me.Form.Name & " sLoad_Ownership", , True)
    Resume exit_Handler
End Sub

Open in new window

  • 5
  • 2
1 Solution
Jeffrey CoachmanCommented:
<3 x 6 option group.>
meaning 18 buttons...?

Now you see the reason why Options groups are only really used if you have a small amount of options.

My Limit is 6

To me, any more than that it is easier to create interfaces that utilize Compoboxes or Listboxes.

Just my 2c
If I am understanding the issue here

Raland9966Author Commented:
This form is access via a touch screen to all the user input is via option groups. Im using 3 col x 6 rows because that is the max that can fit, I'm not using all 18 in every group more like 2-8. How many buttons I have isnt the issue. One this one group which has 2 or 3 active buttons I need to acess the active button when pressed.
Jeffrey CoachmanCommented:
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Jeffrey CoachmanCommented:
If this does not get you what you want, then consider (if possible) *not* putting the option buttons in a group.
Instead try putting in as individual buttons and use code logic to only have one selected at a time...
Jeffrey CoachmanCommented:
Something like this perhaps...in the forms code module to toggle the true/false values so that only one control is true?

Private strActiveControlName As String

Private Sub Option0_Click()
    strActiveControlName = Screen.ActiveControl.Name
    Call ToggleOptions
End Sub

Private Sub Option2_Click()
    strActiveControlName = Screen.ActiveControl.Name
    Call ToggleOptions
End Sub

Private Sub Option4_Click()
    strActiveControlName = Screen.ActiveControl.Name
    Call ToggleOptions
End Sub

Private Sub ToggleOptions()
Dim ctl As Control
    For Each ctl In Me.Section(0).Controls
        If ctl.ControlType = acOptionButton Then
            If strActiveControlName <> ctl.Name Then
                ctl = False
            End If
        End If
    Next ctl
    strActiveControlName = ""
End Sub
Raland9966Author Commented:
I thought of trying the Screen.ActiveControl / PreviousControl but getting the correct value gets a little tricky. I like the idea of command buttons outside of a group along with my existing loop to load the values. Easy enough to add a couple sub's to show and hide the buttons as a group.

Jeffrey CoachmanCommented:



Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now