ms access form fields into a string vba

Question

I have a form (say) Data, which contains text boxes A,B,C.

I wish to write an email based on the form data. In my email body, I want the following format:

A is : (actual value of A in text box in the form) (a newline) B is : ((actual value of B in text box in the form) ( a newline) C is : ((actual value of C in text box in the form).

I know I can access values by Forms!Data!A_value (assuming I named the box as A_value). I am not able to combine them into a string and add a newline too.

I have tried the following:

Dim body as String

body = "A is : & Forms!Data!A_value &" & "B is : & Forms!Data!B_value &" & "C is : & Forms!Data!C_value &"

It is because I read an & results to a new line somewhere.

However, when i do that, the whole thing is concatenated as written in the code and no values are obtained from the form field. Please suggest options: Thanks in advance

YowE3K · Accepted Answer · 2017-07-25 22:28:12Z

1

You probably want something like:

Dim body as String

body = "A is : " & Forms!Data!A_value & vbNewLine & _
       "B is : " & Forms!Data!B_value & vbNewLine & _
       "C is : " & Forms!Data!C_value

Note: the fact that I wrote that code on 3 lines, using line continuation characters, is nothing to do with the insertion of the new line characters in the output. It could have also been written as

body = "A is : " & Forms!Data!A_value & vbNewLine & "B is : " & Forms!Data!B_value & vbNewLine & "C is : " & Forms!Data!C_value

but I find that harder to read.

answered Jul 25, 2017 at 22:28

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

munnahbaba Over a year ago

Thanks! That worked quite well. Didn't know about vbNewLine. :)

Andy G · Accepted Answer · 2017-07-25 22:29:17Z

1

The ampersand is used to concatenate text "hello " & "there".

If you quote the ampersand it does nothing, just reproduces the ampersand "bits & bobs".

You can use the character vbCrLf (carriage return/linefeed) to add (concatenate) a linebreak, "time for " & vbCrLf & "a break".

answered Jul 25, 2017 at 22:29

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Comments

Gordon Bell · Accepted Answer · 2017-07-25 22:35:33Z

1

Another trick you can use is to create a single string template with placeholders for the values, then use Replace statements to fill them in, like:

body = "A is: {A} & B is: {B} & C is: {C}"
body = Replace(body, "{A}", Forms!Data!A_value)
body = Replace(body, "{B}", Forms!Data!B_value)
body = Replace(body, "{C}", Forms!Data!C_value)

And break out to multiple lines, like:

body = "A is: {A}{CR}B is: {B}{CR}C is: {C}{CR}"
body = Replace(body, "{A}", Forms!Data!A_value)
body = Replace(body, "{B}", Forms!Data!B_value)
body = Replace(body, "{C}", Forms!Data!C_value)
body = Replace(body, "{CR}", vbCrLf)

answered Jul 25, 2017 at 22:35

Gordon Bell

13.7k3 gold badges48 silver badges65 bronze badges

2 Comments

munnahbaba Over a year ago

Seems to be an interesting option to be done if dealing with a lot of fields. Thanks.

YowE3K Over a year ago

Nice - that would be especially useful if there were fields being used multiple times in the output, e.g. people's names - one Replace updating every occurrence.

Collectives™ on Stack Overflow

ms access form fields into a string vba

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related