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

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.


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.

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!

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