Problem with appending text to VBA Text box

[Anja]

Hi everyone,

I have a small function that is supposed to take a string and append it
to a text box. Nothing fancy..

So, the function looks like:

Private Sub AppendImportMessage(str As String)
ImportResult.SetFocus
ImportResult.Locked = False
ImportResult.Text = ImportResult.Text & str & vbNewLine
ImportResult.Locked = True
End Sub

Now, I call the function as follows:

AppendImportMessage "(" & str(insertedRecordNum) & ") record(s)
inserted."
AppendImportMessage "(" & str(skippedRecordNum) & ") record(s)
skipped."

the variables are both integers.

I see this as:

( 0) record(s) inserted.( 25) record(s) skipped.Import was successful.

So, no newline and notice the extra space between "(" and the integer
value!!

Can someone explain to me why it is being formatted this way?

Thanks,

Best,
Anja

 

[Ken Snell \(MVP\)]

At times, a trailing "new line" character is unusable unless there is text
on that line as well (even a space). So let's try rewriting your subroutine
to this:

Private Sub AppendImportMessage(str As String)
ImportResult.Locked = False
ImportResult.Value= (ImportResult.Value + vbNewLine) & str
ImportResult.Locked = True
End Sub

The above takes advantage of the fact that a Null value plus anything makes
a Null value, so if the textbox is initially empty, the new line character
is not added. I also have eliminated the use of Text property (which
required you to set focus to the textbox) -- just use Value property
instead.,

 

[Anja]

Thanks!

That works well, except do you have any idea why the a space is put
between "(" and the integer value. I guess this has something to do
with the way str() function works???

At times, a trailing "new line" character is unusable unless there is text
on that line as well (even a space). So let's try rewriting your subroutine
to this:

Private Sub AppendImportMessage(str As String)
ImportResult.Locked = False
ImportResult.Value= (ImportResult.Value + vbNewLine) & str
ImportResult.Locked = True
End Sub

The above takes advantage of the fact that a Null value plus anything makes
a Null value, so if the textbox is initially empty, the new line character
is not added. I also have eliminated the use of Text property (which
required you to set focus to the textbox) -- just use Value property
instead.,

Thanks!

That works well, except do you have any idea why the a space is put
between "(" and the integer value. I guess this has something to do
with the way str() function works???

 

[Granny Spitz via AccessMonster.com]

So, no newline and notice the extra space between "(" and the integer
value!!

Can someone explain to me why it is being formatted this way?

To add to what Ken said, the Str function adds a space preceding numbers to
allow for the +/- sign. You don't need to convert the number though. Access
will implicitly type the value (sans the preceding space) when you
concatenate it with a string when you don't convert it with a function. Use
the CStr function instead if you want to explicitly type the value as a
string without the preceding space.

 

[Anja]

To add to what Ken said, the Str function adds a space preceding numbers to
allow for the +/- sign. You don't need to convert the number though. Access
will implicitly type the value (sans the preceding space) when you
concatenate it with a string when you don't convert it with a function. Use
the CStr function instead if you want to explicitly type the value as a
string without the preceding space.

--

Works great! Thanks!

 

[Granny Spitz via AccessMonster.com]

Works great! Thanks!

You're welcome, Anja. Good luck on your project.