How to: Combobox text inserted as part of a texbox standard message.

[jdusn1978]

I have a textbox on a form with a standard message that has a few blanks in it. I want to be able to fill in the blanks based on text from combobox selections.

ComboboxItem select options are: TrainingBook 1, TrainingBook 2, TrainingBook 3.

ComboboxLocations select options are: Northern Region, Southern Region, Eastern Region, Western Region.

The standard text looks like: Thank you for ordering _________ for the ________ of operations. Your order was received on_______ and should be shipped no later than ________. Thank you once again for your order!

I’ve started sort of like this --

Textbox source =“Thank you for ordering” & [comboboxItem] & “for the” & [ComboboxLocations] & “of operations. Your order was received on” & [datebox] & “and should ship by”, =adddate([datebox]+2), & “Thank you once again for your order!”

Thank you for your help!

[jforbes]

What you are attempting to do should work, but typing all that into the ControlSource is not the easiest thing to troubleshoot. I would recommend creating a function in your form to knit a text string together and return it, then you can use this function as the ControlSource and then you can debug what is going on.

For example, you could create a function like the following and set the ControlSource to =generateConfirmation():

Public Function generateConfirmation() As String
 
    Dim sComboboxItem As String
    Dim sComboboxLocations As String
    Dim ddatebox As Date
    Dim dShip As Date
    Dim sReturn As String
 
 
    sComboboxItem = Nz(Me.ComboboxItem.Value, "")
    sComboboxLocations = Nz(Me.ComboboxLocations .Value, "")
    ddatebox = Nz(Me.datebox .Value, ddatebox )
    dShip = dateadd("d", 2, ddatebox)
 
    sReturn = sReturn & "Thank you for ordering "
    sReturn = sReturn & sComboboxItem  & " for the "
    sReturn = sReturn & sComboboxLocations & " of Operations.  "
    sReturn = sReturn & "Your order was received on "
    sReturn = sReturn & ddatebox & " and should ship by "
    sReturn = sReturn & dShip & ".  Thank you once again for your order!"
 
    generateConfirmation = sReturn 
End Function

[jdusn1978]

jforbes,

Thank you for the answer and I've looked into implementing your instructions; however, I am not at a level to full comprehend how to do this. Would you mind, if you have the time, pointing me in the right direction or walking me through how to create this function.

Than you in advance!

[jforbes]

• Open your Form in Run Mode.
• Open the VBA Editor code by hitting the Alt+F11 keys.
• In the Editor's upper left shoudl be a list of your Forms, double-click on the Form you are working on to open the Form's Code.
• Go to the bottom of the code and Paste in the Code from Post #2
• You may have to tweak the code a little bit to get it to work. First thing to do is to click Debug|Compile to see what errors Access can find with the Code. It most likely will find errors with the names of your TextBoxes and ComboBoxes as I only can guess what their names are.
• Second, once it compiles, you'll want to test the function. The easiest way I know how to do this is by using the Immediate Window. To open it, if it isn't open already, hit Ctrl+G on the keyboard.
• In the Immediate Window, type the following and hit enter: ?Form_[yourFormName].generateConfirmation() (You'll need to type in your actual Form Name for [yourFormName])
• Your code should execute and if there are errors, the debugger will highlight them. The Question mark is shorthand for Debug.Print, so a question mark followed by a function, will run the function and print the results to the Immediate Window.
• This is some good documentation on how to Debug in VBA and if you don't feel confident with debugging VBA, reading this should actually be your first step before anything else.
• Once the code is working, put the following into the ControlSource of your TextBox: =generateConfirmation()
• Enjoy your new TextBox