Reference the .Tag of active button in an option group

Posted on 2011-10-04
Last Modified: 2012-05-12
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

Question by:Raland9966
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    <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


    Author Comment

    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.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    LVL 74

    Accepted Solution

    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...
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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

    Author Comment

    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.

    LVL 74

    Expert Comment

    by:Jeffrey Coachman



    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now