Forcing a New Line in a Text Field in MS Access using VB

Heya!

I feel I'm beating an old horse to death with this question but I
can't get around the problem.

I want to update a text field on a form with a string from a VB
module. I need enter/linefeeds in the string.

I've tried:
Chr(10) & Chr(13)
Chr(13) & Chr(10
vbnewline
vbcrlf
none of which seem to work.
I've set the properties for the text box to [Enter Key Behavior] =
'New Line In Field' and [Can Grow] = 'yes'.

At the moment, my code looks like this:
celsql = celsql _
& "Program: " & encname _
& vbCrLf _
& vbCrLf _
& "Start Time: " & sttim _
& vbCrLf _
& "Participants: " & numpart _
& vbCrLf _
& "Confirmed by Curator: " & cbc _
& vbCrLf _
& "Client: " & clntnm _
& vbCrLf _
& "Contact: " & cntct _
& vbCrLf _
& vbCrLf
Forms(frm)(bts) = celsql

If I toss this string into a [MsgBox], it comes out fine. It's just
something about the text field...

I'm working in MSAccess 2007, but need it backwards compatible to
2003.

I'd really appreciate any advice.

Cheers,
KG

Re: Forcing a New Line in a Text Field in MS Access using VB

I've seen this type of problem before, but I typically just use a
separate text box for each data field. Is there a particular reason
why you're trying to fit this all into a single text box?

Re: Forcing a New Line in a Text Field in MS Access using VB

Thanks for asking! It's a long story :)

I've used unbound Access forms backed by a fair bit of VB code to
generate interactive calendars. Each textbox is the cell for a given
date. The program updates information into the cell for one or more
[event]s occurring on that date. Each [event] is comprised of the text
string above. Users print off the calendars to post around the
workplace. Since there can be more than one [event] per text box
(day), I can't easily use multiple text boxes (although it would be
possible).

I know I'm pushing the edge of what Access can cope with and am likely
better off creating interactive calendars from scratch using VB. If
there's no easy patch or solution, I'll look for some other way to do
it. I was thinking maybe I had the wrong [References] selected?

Thanks for the help!

KG :)

Re: Forcing a New Line in a Text Field in MS Access using VB

On Nov 19, 11:52 am, Bubo.virginia...@gmail.com wrote:
> Heya!
>
> I feel I'm beating an old horse to death with this question but I
> can't get around the problem.
>
> I want to update a text field on a form with a string from a VB
> module. I need enter/linefeeds in the string.
>
> I've tried:
> Chr(10) & Chr(13)
> Chr(13) & Chr(10
> vbnewline
> vbcrlf
> none of which seem to work.
> I've set the properties for the text box to [Enter Key Behavior] =
> 'New Line In Field' and [Can Grow] = 'yes'.
>
> At the moment, my code looks like this:
> celsql = celsql _
> & "Program: " & encname _
> & vbCrLf _
> & vbCrLf _
> & "Start Time: " & sttim _
> & vbCrLf _
> & "Participants: " & numpart _
> & vbCrLf _
> & "Confirmed by Curator: " & cbc _
> & vbCrLf _
> & "Client: " & clntnm _
> & vbCrLf _
> & "Contact: " & cntct _
> & vbCrLf _
> & vbCrLf
> Forms(frm)(bts) = celsql
>
> If I toss this string into a [MsgBox], it comes out fine. It's just
> something about the text field...
>
> I'm working in MSAccess 2007, but need it backwards compatible to
> 2003.
>
> I'd really appreciate any advice.
>
> Cheers,
> KG

This works for me in Access 2007 (that is the line breaks are shown):

Private Sub Command38_Click()
Me.Notes.Value = "Education includes a BA in psychology from Colorado
State University in 1970." _
& vbNewLine _
& "She also completed 'The Art of the Cold Call.'" _
& vbNewLine _
& "Nancy is a member of Toastmasters International."
End Sub

The textbox shows:

Education includes a BA in psychology from Colorado State University
in 1970.
She also completed 'The Art of the Cold Call.'
Nancy is a member of Toastmasters International.

Re: Forcing a New Line in a Text Field in MS Access using VB

Bubo.virginianus@gmail.com wrote:

> Heya!
>
> I feel I'm beating an old horse to death with this question but I
> can't get around the problem.
>
> I want to update a text field on a form with a string from a VB
> module. I need enter/linefeeds in the string.
>
> I've tried:
> Chr(10) & Chr(13)
> Chr(13) & Chr(10
> vbnewline
> vbcrlf
> none of which seem to work.
> I've set the properties for the text box to [Enter Key Behavior] =
> 'New Line In Field' and [Can Grow] = 'yes'.
>
> At the moment, my code looks like this:
> celsql = celsql _
> & "Program: " & encname _
> & vbCrLf _
> & vbCrLf _
> & "Start Time: " & sttim _
> & vbCrLf _
> & "Participants: " & numpart _
> & vbCrLf _
> & "Confirmed by Curator: " & cbc _
> & vbCrLf _
> & "Client: " & clntnm _
> & vbCrLf _
> & "Contact: " & cntct _
> & vbCrLf _
> & vbCrLf
> Forms(frm)(bts) = celsql
>
> If I toss this string into a [MsgBox], it comes out fine. It's just
> something about the text field...
>
> I'm working in MSAccess 2007, but need it backwards compatible to
> 2003.
>
> I'd really appreciate any advice.
>
> Cheers,
> KG

I created a form in A97 (not 2007, I know) with a TextBox and Command
button. The code below works just fine. I didn't bother with setting
grow props and I kept the keystrokes as default (IOW, not the enter
key). If it doesn't work, it might be an A2007 thing.

Now...I did not make this a bound field to a table. I'd use a Memo
field if that were the case. Are you using a Text field with a small
number of characters as its length?


Private Sub Command2_Click()
MsgBox Me.Text0
End Sub

Private Sub Form_Current()
s = "Program: " & "Is" _
& vbCrLf _
& vbCrLf _
& "Start Time: " & Now() _
& vbCrLf _
& "Participants: " & 1 _
& vbCrLf _
& "Confirmed by Curator: " & "Yes" _
& vbCrLf _
& "Client: " & "Customer" _
& vbCrLf _
& "Contact: " & "Client" _
& vbCrLf _
& vbCrLf
Forms("Form1")("Text0") = s
End Sub

Re: Forcing a New Line in a Text Field in MS Access using VB

I use Access 2000, but unless it's been changed since, the "Can Grow"
property only works when the form is printed (or previewed), not when it's on
the screen as a form. Check the help file for your version.

John



Bubo.virginianus@gmail.com wrote:
>Heya!
>
>I feel I'm beating an old horse to death with this question but I
>can't get around the problem.
>
>I want to update a text field on a form with a string from a VB
>module. I need enter/linefeeds in the string.
>
>I've tried:
> Chr(10) & Chr(13)
> Chr(13) & Chr(10
> vbnewline
> vbcrlf
>none of which seem to work.
>I've set the properties for the text box to [Enter Key Behavior] =
>'New Line In Field' and [Can Grow] = 'yes'.
>
>At the moment, my code looks like this:
> celsql = celsql _
> & "Program: " & encname _
> & vbCrLf _
> & vbCrLf _
> & "Start Time: " & sttim _
> & vbCrLf _
> & "Participants: " & numpart _
> & vbCrLf _
> & "Confirmed by Curator: " & cbc _
> & vbCrLf _
> & "Client: " & clntnm _
> & vbCrLf _
> & "Contact: " & cntct _
> & vbCrLf _
> & vbCrLf
> Forms(frm)(bts) = celsql
>
>If I toss this string into a [MsgBox], it comes out fine. It's just
>something about the text field...
>
>I'm working in MSAccess 2007, but need it backwards compatible to
>2003.
>
>I'd really appreciate any advice.
>
>Cheers,
>KG

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200711/1