Building a function in Access 2010 to choose between 2 values

I have a query that pulls data & displays results in a form. On the form I have the following items:
a combo box that allows choosing 2 different text values manually.
2 text boxes that displays 2 prices from the query
A command button to choose the corresponding price according to the value chosen in the combo box and displays it with a message in another textbox. So far, this is what I got but it's not working. Please troubleshoot. Thanks

Private Sub Command16_Click()
Dim msg1, msg2, msg3, msg4, msg5, sign, space, Stock_Availability, Item As String
Dim price_to_show As Double

msg1 = "Dear XYZ" + vbCrLf
msg2 = "Thank you for your  inquiry. Please find below the information"
Item = [Description]
msg3 = "Price:"
msg4 = "Status:"
msg5 = "Regards"
sign = "$"
space = "  "

If [Stock Availability] <= 0 Then
Stock_Availability = "Not Currently in Stock"
Else: Stock_Availability = "In Stock"
End If

If Me.Combo46.Text = "Public" Then
price_to_show = [Retail Price]
ElseIf Me.Combo46.Text = "Authorized Dealer" Then
price_to_show = [Authorized Dealer Price]
End If

Text17 = msg1 & vbCrLf & msg2 & vbCrLf & vbCrLf & Item & vbCrLf & msg3 & space & sign & price_to_show & vbCrLf & msg4 & space & _
Stock_Availability & vbCrLf & vbCrLf & _
Text17.Visible = True

end sub
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Try to change the Text17 = ... to Text17.Value = ... like this:
 Text17.Value = msg1 & vbCrLf & ....

Open in new window

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
thao-nhiAuthor Commented:
Having a run time error asking for combo46 to have a focus. The debugger highlight it at the line
 If Me.Combo46.Text = "Public" Then
thao-nhiAuthor Commented:
when combo46 is set focus, it runs but does not pick up the price values. It shows  0 when both prices are available from the query
This Access feature is by design. From MSDN:
While the control has the focus, the Text property contains the text data currently in the control; the Value property contains the last saved data for the control. When you move the focus to another control, the control's data is updated, and the Value property is set to this new value. The Text property setting is then unavailable until the control gets the focus again. If you use the Save Record command on the Records menu to save the data in the control without moving the focus, the Text property and Value property settings will be the same.
So, if you need the value of the Combobox (or a textbox, as a general rule) use the .Value property
Dim msg1, msg2, msg3, msg4, msg5, sign, space, Stock_Availability, Item As String
The first 8 variables in this expression are defined as Variants.  Only the last one is being defined as a String.

ALWAYS give your controls meaningful names before you start using them.  Controls named Command16, Text16, and Text46 are meaningless.  They will cause anyone who has to read your code to curse your progeny to eternity.  Even yourself coming back to it after some time away will be sorry.
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.