display formatting (new lines, tabs etc) in VBA Access

[pteare]

Hello,

I am using access 07 and have some VBA code which puts some text into a memo field for me. It gets the text from various columns of a table.

Currently it looks like the below:

phil - hire - advanced
jonathan - lessons - beginner
claire - lift pass - local area

etc

Does anyone know of a good resource for info on code for formatting the text? So far I've discovered vbCrLf, that's it! What I'd ideally want is

phil hire advanced
jonathan lessons beginner
claire lift pass local area

In other words how to line up the text into columns and also if there are any features I could use here (e.g. the option to line up to the right hand side of the words or to the left hand side?).

This will be a template email for billing guests so I'm just trying to work out how to make it look good. It needs to stay just as text for the way I'll be emailing it.

Any ideas on where to look to find out what's possible and what the code is? I've found the microsoft help files a whole pile of help again after ages of searching.....

many thanks,

Phil.

[pteare]

hello again!

Sorry that's not come out as I meant! on the what I want list I was looking for it to be in neat columns, that's all.

many thanks!

Phil.

[NeoPa]

What format are you using for the email (HTML, RTF, Plain text)?

[ADezii]

1. It's not that easy a Task, but perhaps it is also too close to my bedtime. The following code will precisely position your 3 Strings into 3 perfectly aligned Columns in a Text Box (txtMemoField) Bound to a MEMO Field. The Keys to this code working effectively are:
   • Use a Fixed Width Font for the Text Box, Courier New works nicely.
   • Use of the Space$() Function to accurately position each Column.
   • Use of the Split() Function to place the Elements of each String into an Array.
2. I'm too tired right now, so I'll simply Post the code and what the subsequent Output will look like in the Text Box.
3. Any questions, please feel free to ask.

'Adjust the Constants to manipulate the Space between Columns
Const intSpacer1 As Integer = 20    'Equalize between Cols 1 and 2
Const intSpacer2 As Integer = 24    'Equalize between Cols 2 and 3
...
Dim strMemo As String
Dim astrString(2) As String         'Declare an Array of Strings
Dim intCounter As Integer
Dim varString As Variant            'Array to hold each of the 3 Strings
...
astrString(0) = "phil hire advanced"
astrString(1) = "jonathan lessons beginner"
astrString(2) = "claire lift pass local area"
...
For intCounter = 0 To 2         'Array of 3 Strings
..varString = Split(astrString(intCounter), " ")     'Split Elements into another Array
..strMemo = strMemo & varString(0) & Space$(intSpacer1 - Len(varString(0))) & varString(1) & _
                      ..Space$(intSpacer1 - Len(varString(1))) & varString(2) & vbCrLf
Next
...
Me![txtMemoField] = strMemo

OUTPUT:
phil.............hire............advanced
jonathan.....lessons.....beginner
claire..........lift..............pass

P.S. - Kindly ignore my Dot Spacers(...), this new format is killing me.

[pteare]

thanks that's great.

If I understand your code right then what it's doing is counting the length of each word / phrase, then adding on the appropriate number of spaces to make the total of phrase-and-trailing-spaces to be always the same number. thanks for the tip on courier as well. That makes perfect sense, thanks very much. I'll get stuck into that.

I'm sending the email out in plain text at the moment, as that works best with my email system as that needs to be all online (as I use different computers in different locations. I had a brief look at outlook but it seems a pain when I need two different computers in two different places looking at the same thing).

Thanks again guys, I should be good on that from now on......

Phil.

[NeoPa]

That's great Phil :)

For basic text I would have suggested formatting the text using spaces. ADezii has even provided some code to illustrate the concept.

This clearly displays best when a non-proportional font is used for the client, but you'll have no control over that I'm afraid :(

[mshmyob]

Since you are using AC2007 you can set your memo to RTF rather than plain text. AC2007 RTF is really HTML so you can just populate your Memo text with HTML code as follows:

Assuming you have a command button and a memo field on your form called txtMemo.

In the OnClick event of the button populate your memo field with 3 columns and 3 rows alligned:

Dim txtMessage As String
Dim txtMemoHTML As String
    txtMessage = "column 1 row 1     column 2 row 1     column 3 row 1"
    txtMessage = txtMessage & "<BR> column 1 row 2     column 2 row 2     column 3 row 2"
    txtMessage = txtMessage & "<BR> column 1 row 3     column 2 row 3     column 3 row 2"
    txtMemoHTML = "<pre style=""font-family: Verdana, Arial, sans serif;""" & ">"
    txtMemoHTML = txtMemoHTML & txtMessage
    txtMemoHTML = txtMemoHTML & "</pre>"
    Me.txtMemo = txtMemoHTML
 

Here I have used the HTML preformatted tag and set the font to a fixed width font (that is the key). You would also use the previous code to determine length of strings.

I have tried to set tables but I have never gotten it to work. Maybe AC2007 RTF doesn't support the table tag (although no error is displayed).

cheers,