By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,750 Members | 1,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,750 IT Pros & Developers. It's quick & easy.

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

P: 8

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


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,

Sep 24 '08 #1
Share this Question
Share on Google+
6 Replies

P: 8
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!

Sep 24 '08 #2

Expert Mod 15k+
P: 31,754
What format are you using for the email (HTML, RTF, Plain text)?
Sep 25 '08 #3

Expert 5K+
P: 8,692
  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.
Expand|Select|Wrap|Line Numbers
  1. 'Adjust the Constants to manipulate the Space between Columns
  2. Const intSpacer1 As Integer = 20    'Equalize between Cols 1 and 2
  3. Const intSpacer2 As Integer = 24    'Equalize between Cols 2 and 3
  4. ...
  5. Dim strMemo As String
  6. Dim astrString(2) As String         'Declare an Array of Strings
  7. Dim intCounter As Integer
  8. Dim varString As Variant            'Array to hold each of the 3 Strings
  9. ...
  10. astrString(0) = "phil hire advanced"
  11. astrString(1) = "jonathan lessons beginner"
  12. astrString(2) = "claire lift pass local area"
  13. ...
  14. For intCounter = 0 To 2         'Array of 3 Strings
  15. ..varString = Split(astrString(intCounter), " ")     'Split Elements into another Array
  16. ..strMemo = strMemo & varString(0) & Space$(intSpacer1 - Len(varString(0))) & varString(1) & _
  17.                       ..Space$(intSpacer1 - Len(varString(1))) & varString(2) & vbCrLf
  18. Next
  19. ...
  20. Me![txtMemoField] = strMemo

P.S. - Kindly ignore my Dot Spacers(...), this new format is killing me.
Sep 25 '08 #4

P: 8
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......

Sep 25 '08 #5

Expert Mod 15k+
P: 31,754
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 :(
Sep 25 '08 #6

Expert 100+
P: 904
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:

Expand|Select|Wrap|Line Numbers
  1. Dim txtMessage As String
  2. Dim txtMemoHTML As String
  3.     txtMessage = "column 1 row 1     column 2 row 1     column 3 row 1"
  4.     txtMessage = txtMessage & "<BR> column 1 row 2     column 2 row 2     column 3 row 2"
  5.     txtMessage = txtMessage & "<BR> column 1 row 3     column 2 row 3     column 3 row 2"
  6.     txtMemoHTML = "<pre style=""font-family: Verdana, Arial, sans serif;""" & ">"
  7.     txtMemoHTML = txtMemoHTML & txtMessage
  8.     txtMemoHTML = txtMemoHTML & "</pre>"
  9.     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).

Sep 25 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.