trim trailing spaces on data in a text box

[brian.capel]

I have a bound control on an Access form, tied to a CHAR field in SQL
Server. Is there a way to trim off the trailing spaces in the field
before displaying it on my form? Tabbing into the field highlights not
only the "real" data but also the spaces at the end of the field. In
order to append something to the end of the "real" data you must first
delete the spaces. Kind of awkward. Any ideas?

Thanks, Brian

 

[fredg]

I have a bound control on an Access form, tied to a CHAR field in SQL
Server. Is there a way to trim off the trailing spaces in the field
before displaying it on my form? Tabbing into the field highlights not
only the "real" data but also the spaces at the end of the field. In
order to append something to the end of the "real" data you must first
delete the spaces. Kind of awkward. Any ideas?

Thanks, Brian

Look up the Trim() function in VBA Help.

 

[Rick Brandt]

I have a bound control on an Access form, tied to a CHAR field in SQL
Server. Is there a way to trim off the trailing spaces in the field
before displaying it on my form? Tabbing into the field highlights
not only the "real" data but also the spaces at the end of the field.
In order to append something to the end of the "real" data you must
first delete the spaces. Kind of awkward. Any ideas?

Thanks, Brian

You could use...

Me.TextBoxName = Trim(Me.TextBoxName)

....in the GotFocus event, but then you will be dirtying every record you view so
that's not a great fix. More complex, but you could push trimmed data into
unbound copies of the bound TextBoxes then push any changes back in the
AfterUpdate event of the unbound boxes.

 

[brian.capel]

You could use...

Me.TextBoxName = Trim(Me.TextBoxName)

...in the GotFocus event, but then you will be dirtying every record you view so
that's not a great fix. More complex, but you could push trimmed data into
unbound copies of the bound TextBoxes then push any changes back in the
AfterUpdate event of the unbound boxes.

Thanks Rick, I opted to go with your unbound TextBox approach. So... I
am trying to get at a field in the recordset for my subform (SQL
statement is assigned to the recordsource under Form properties) to
populate the unbound textbox, but I just can't seem to get it to work.
I placed the the following code in the On Current event to see what was
going on:

MsgBox Me.Recordset.("PartDesc")

Sometimes it comes up with "Either BOF or EOF is true" error. And at
other times it shows one of the records values. And if I do an update
to the field, the new value doesn't display, just the old value. Am I
using the wrong event, the wrong approach? I realize I could just fire
off a SQL statement to get the field value but it is already in the
recordset for my subform and I'd like to use that, but I don't
understand if's life cycle. Your thoughts?