?
Solved

Getting the result for a text box from a question?

Posted on 2011-02-17
14
Medium Priority
?
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
14 Comments
 
LVL 11

Expert Comment

by:Guru Ji
ID: 34918147
You can try creating a Custom Message Box

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

Expert Comment

by:omgang
ID: 34918198
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
ID: 34918232
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:smods
ID: 34918354
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
ID: 34918470
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
ID: 34918525
What's the row source for the list box on your form?
OM Gang
0
 

Author Comment

by:smods
ID: 34918573
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
 
LVL 28

Expert Comment

by:omgang
ID: 34918618
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 2000 total points
ID: 34918852
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
ID: 34918879
OM Gang
inputbox.jpg
0
 

Author Comment

by:smods
ID: 34924152
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 2000 total points
ID: 34926222
<<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
ID: 34926408
Wicked solution OM Gang.

Thanks

Chris
0
 
LVL 28

Expert Comment

by:omgang
ID: 34926497
Glad it's working for you.
OM Gang
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

764 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