Create a Form to Return the Value Selected by a user from an Option Group

Posted on 2004-11-18
Last Modified: 2012-05-05
I have a form created in Access that has an Option Group with 12 options.  I want the captions of these options and number of options displayed  to be dynamic.  I have been able to create and call a function on a form to dynamically hide and show the appropriate options as shown below.

Public Function ShowOptions(strOptnList() As String)

    Dim intx    As Integer
    Dim strOptnLbl  As String
    Dim frm As Form_frm_OptionList

    For intx = 1 To UBound(strOptnList)
        strOptnLbl = "lblOptn" & intx
        Me(strOptnLbl).Visible = True
        Me(strOptnLbl).Caption = strOptnList(intx)
        Me("Optn" & intx).Visible = True
    Next intx
End Function

What I would really like to do, however, is to call this form from anywhere within my program and have it return the selected option to the calling function.  I know this can be accomplished by creating a global variable to store the option value in, but I was hoping there was some way of making this form(function) more portable to prevent the need of always declaring a global variable in any application I use such a form in.  

Is there a way to call this form and have it return back the user's selection without declaring a global variable.

I found code to accomplish a similar thing in VB, but have not been able to do in Access.


Question by:cjaney
    LVL 58

    Accepted Solution

    You can have your form write to an object instead of a global variable. For example, use something like this:

    Dim gctlCalling As Control

    Private Sub Form_Close()
        gctlCalling.Tag = "no comment!"
    End Sub

    Private Sub Form_Open(intCancel As Integer)
    On Error Resume Next
        Set gctlCalling = Screen.ActiveControl
        intCancel = gctlCalling Is Nothing
    End Sub

    Normally, the ActiveControl during the form_open event is still the calling button.

    Another solution is to pass an OpenArgs string and use that to identify where to write the answer...

    Good Luck

    Hope this helps!

    Assisted Solution

    Here's a simple example of a form that will return a value on exit.  It does this by simulating the action of a modal dialog box -- that is, it pauses processing in the calling routine by holding its "Done" property to "False" until the user clicks the "OK" button.  When "Done" is set to "True", the calling routine can exit from its Do Loop and read the form's "Value" property before the form is closed.  Here's how:

    First, create a pop-up type form named "InputPrompt".  It will contain a "Value" property and a "Done" property:

    Dim sValue As String
    Dim bDone As Boolean

    Private Sub Form_Load()
    End Sub

    Private Sub cmdOK_Click()

        sValue = Nz(Me.txtValue, "")
        If Not sValue = "" Then
            bDone = True
        End If

    End Sub

    Public Property Get Value() As Variant
        Value = sValue
    End Property

    Public Property Get Done() As Variant
        Done = bDone
    End Property

    Next, create a function that opens the form and reads the properties:

    Public Function promptForValue()

        Dim formName As String: formName = "InputPrompt"
        Dim retVal As String
        DoCmd.OpenForm formName, acNormal
        With Forms(formName)
            Do While Not .Done
            retVal = .Value
        End With
        DoCmd.Close acForm, formName
        promptForValue = retVal
    End Function


    Mark Pemburn
    Bel Air, MD

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 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

    23 Experts available now in Live!

    Get 1:1 Help Now