[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2004-11-18
Medium Priority
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

harfang earned 592 total points
ID: 12624107
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

mpemburn earned 588 total points
ID: 14096541
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 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