Solved

Getting the result for a text box from a question?

Posted on 2011-02-17
14
258 Views
Last Modified: 2012-05-11
Hi all,

I have a button on my main menu to launch frm_form1

One of the fields in this form is called txt_textbox1 which has three options in it.

When the user presses the button to launch the form can a question message box open with the three options and capture this result directly from the question?

Regards

Chris
0
Comment
Question by:smods
  • 9
  • 4
14 Comments
 
LVL 11

Expert Comment

by:Guru Ji
Comment Utility
You can try creating a Custom Message Box

http://gregmaxey.mvps.org/Custom_MsgBox.htm
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Something like

Private Sub Form_Load()

    Dim strPrompt As String, strText As String
    Dim varResponse As Variant

PromptUser:
    strPrompt = "Please select one of these options:" & vbCrLf & "1 -- This is the first option" & vbCrLF & "2 -- This is the second option" & vbCrLF & "3 -- This is the third option" & vbCrLf & "Enter 1, 2 or 3"
    varResponse = InputBoox(strMsg, , "Make your choice")
        'test the response to make sure we got what we wanted
    Select Case varResponse
        Case "1"
            strText = "some text here"

        Case "2"
            strText = "some other text here"

        Case "3"
            strText = "you get the idea"

        Case Else
            strPrompt = "Apparently you missed the part about entering a 1, 2 or 3"
            MsgBox strPrompt, , "Hello McFly!"
            GoTo PromptUser

    End Select

End Sub


OM Gang
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Oops, forgot something and noticed one typo

should be varResponse = InputBox(strMsg, , "Make your choice")


     End Select

        'set the text for form field
    Me.txt_textbox1 = strText

End Sub

OM Gang
0
 

Author Comment

by:smods
Comment Utility
Thanks

I get a message box with an ok and cancel button and a space to type a value but it doesn't list the values in the box?  Also I'd ideally want three buttons on the box.  One labelled Option 1, Option 2, Option 3.  Is that possible?
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
First, I just provided an example.  You can replace the text that's displayed in the input box with the values from the list box on the form.

Second, we're pretty limited on what we can do with an actual message box or input box.  We can display text but not option buttons, etc.  To do this you can create a custom pop-up form to be used instead.  The link that write2mohit provided describes how to do this.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
What's the row source for the list box on your form?
OM Gang
0
 

Author Comment

by:smods
Comment Utility
It's a value list in a combo box

Row Source "Choice 1";"Choice 2";"Choice 3"

The message box is looking like this?
ee.JPG
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 28

Expert Comment

by:omgang
Comment Utility
My bad.  should be
varResponse = InputBox(strPrompt, , "Make your choice")

Now, to change the strPrompt so that it uses the values from your combo box you'll need to parse the row source.  Let me get back to you in a bit.
OM Gang

0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
Comment Utility
OK, I built a test form so I could give you accurate code.  The Option Explicit statement goes at the top of the form module just under the Option Compare Database statement.  Having Option Explicit makes sure all your variables are declared and would have caught the error where I gave you strMsg instead of strPrompt.

Note that this works for your current situation but if you add values to the combo box the Select...Case statements will need to be modified (because they currently are only set to check for 1, 2 or 3).  A better solution will be to test the response from the user against the array indexes (plus one), e.g. if the user enters 279 but the array only has 1 - 10 (0 - 9 plus one) indexes then you'd display the message telling them to pay attention.
OM Gang


Option Explicit

Private Sub Form_Load()

On Error GoTo Err_Form_Load

    Dim lngIndex As Long
    Dim strPrompt As String, strText As String, strRowSource As String
    Dim varResponse As Variant, varArray As Variant
   
        'get row source from combo box
    strRowSource = Me.Combo0.RowSource      '<--- you need to use the name of the actual combo box on your form
   
        'we'll split the string on the semicolons and populate an array with the values
    varArray = Split(strRowSource, ";")

PromptUser:
    strPrompt = "Please select one of these options:"
        'enumerate combo box array and add a line to the message prompt for each option
    For lngIndex = 0 To UBound(varArray)
        strPrompt = strPrompt & vbCrLf & lngIndex + 1 & " -- " & varArray(lngIndex)
    Next
    strPrompt = strPrompt & vbCrLf & "Please enter 1, 2, 3, etc."
    varResponse = InputBox(strPrompt, , "Make your choice")
        'test the response to make sure we got what we wanted
    Select Case varResponse
        Case "1"
            strText = "some text here"

        Case "2"
            strText = "some other text here"

        Case "3"
            strText = "you get the idea"

        Case Else
            strPrompt = "Apparently you missed the part about entering a 1, 2 or 3"
            MsgBox strPrompt, , "Hello McFly!"
            GoTo PromptUser

    End Select
   
    Me.txt_textbox1 = strText

Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Form_Load of VBA Document Form_Form19"
    Resume Exit_Form_Load
   
End Sub
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
OM Gang
inputbox.jpg
0
 

Author Comment

by:smods
Comment Utility
Thanks OM Gang!

Nearly there.  How would I put a carriage return after please select one of these options: text?

The user is still presented with the error message when pressing cancel instead of the action cancelling?

and finally my choices have quotes around them? but I cannot see why?

Private Sub Form_Load()

On Error GoTo Err_Form_Load

    Dim lngIndex As Long
    Dim strPrompt As String, strText As String, strRowSource As String
    Dim varResponse As Variant, varArray As Variant
    
        'get row source from combo box
    strRowSource = Me.Type.RowSource      '<--- you need to use the name of the actual combo box on your form
    
        'we'll split the string on the semicolons and populate an array with the values
    varArray = Split(strRowSource, ";")

PromptUser:
    strPrompt = "Select the type of count you want to perform:"
        'enumerate combo box array and add a line to the message prompt for each option
    For lngIndex = 0 To UBound(varArray)
        strPrompt = strPrompt & vbCrLf & lngIndex + 1 & " -- " & varArray(lngIndex)
    Next
    strPrompt = strPrompt & vbCrLf & "Please enter 1, 2, 3, etc."
    varResponse = InputBox(strPrompt, , "Make your choice")
        'test the response to make sure we got what we wanted
    Select Case varResponse
        Case "1"
            strText = "AM"

        Case "2"
            strText = "PM"

        Case "3"
            strText = "Spot Check"

        Case Else
            strPrompt = "Please select option 1, 2 or 3"
            MsgBox strPrompt, , "Error!"
            GoTo PromptUser

    End Select
    
    Me.Safe_Type = strText

Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Form_Load of VBA Document frm_Count"
    Resume Exit_Form_Load

Open in new window

0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 500 total points
Comment Utility
<<Nearly there.  How would I put a carriage return after please select one of these options: text?>>
vbCrLF is a carriage return character and line feed character.  I use this when building the strPrompt to display in the Input box.  To add another linefeed where you request do this
strPrompt = "Select the type of count you want to perform:" & vbCrLF


<<The user is still presented with the error message when pressing cancel instead of the action cancelling?>>
You need to add a piece to your code to handle what happens when the user clicks Cancel.  Something like

        Case "3"
            strText = "you get the idea"
           
        Case ""
                'user cancelled so we'll close the form  '<--- what do you want to happen when the user clicks Cancel in the Input box?
            DoCmd.Close acForm, Me.Name
            GoTo Exit_Form_Load

        Case Else
            strPrompt = "Apparently you missed the part about entering a 1, 2 or 3"
            MsgBox strPrompt, , "Hello McFly!"
            GoTo PromptUser

    End Select



<<and finally my choices have quotes around them? but I cannot see why?>>
I believe you have quotes around them in the RowSource for your combo box.  Literally Row Source "Choice 1";"Choice 2";"Choice 3"
Change the RowSource to Row Source Choice 1;Choice 2;Choice 3

OM Gang

0
 

Author Comment

by:smods
Comment Utility
Wicked solution OM Gang.

Thanks

Chris
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Glad it's working for you.
OM Gang
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 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

13 Experts available now in Live!

Get 1:1 Help Now