Storing string variables in table and calling from VBA with correct insertions

[riggsdp]

Is it possible to have a string with variables stored in a table, then assign that field's content to a variable in vba and have it insert the correct information?



For example, if I store the following in a table:

"Notice of Receipt: For Review & Comment - " & frmName.Contract_Number.Column(1) & ", " & frmName.Delivery_Task_Order.Column(1) & ", CDRL " & frmName.CDRL_Number.Column(1)

how do I assign it to a variable in vba so that it pulls the data from the form, inserts it into the string, prints correctly, and looks like the string below on the form?

Notice of Receipt: For Review & Comment - W58RGZ-13-D-0245, T.O. 0007, CDRL A001

I already know that this doesn't work:

msgSubject = DLookup("Subject", "tblEmailCases", "Case=" & emailCase)

and just outputs the exact string from the table.

TIA
dar

[June7]

Can reference form controls in a query. Your syntax is wrong. And query object cannot handle Column(x) reference.

Why do you need VBA?

What do you mean by print - paper copy?

[riggsdp]

Just printing it to another form. We have security settings that won't allow me to send the text to Outlook so I have it output to a form where we copy and paste it into Outlook. I already have this working by putting all the strings into my VBA code but I've been asked to add additional options and was trying to streamline it so I didn't have to add another 100 lines of code.

[June7]

So is your question answered?

Why would you have to add 100 lines of code?

[pbaldy]

Check out the Eval() function. It should do what you want:

msgSubject = Eval(DLookup("Subject", "tblEmailCases", "Case=" & emailCase))

[ssanfu]

Will have to change "frmName.Contract_Number.Column(1)" to "Forms!frmName.Contract_Number.Column(1)"