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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<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 CoachmanMIS LiasonCommented:
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Something like this 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 CoachmanMIS LiasonCommented:


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.