Getting an error when using Trim in Form Control

[jlathem]

Getting an error when using Trim in Form Control
I tried to find help in the search but couldn’t find much or I don’t know the proper Syntax criteria to get more than what I have below.

I am rather new to MS Access Forms and need proper Syntax for an issue I am having in a MS Access 2003 Form.

I need to trim leading and trailing spaces from text coming from a control box called "Comment". I don't want someone to be able to just insert blank spaces and call it a comment!

Normally in my web developing I use:
Trim(#Form.Comment#)
[#whatever# Indicates a ColdFusion Variable] in my CFM pages. But I am trying to use Trim in a MS Access 2003 / Table Control.

I found several forum references to using this syntax in the text box's Control Source:
=Trim([Comment])

But I get an #error message in the Comment text box.

Another reference said to use the code above but change the control's "Name" to "CommentTrim". That got rid of the error message but would not allow me to enter anything in the box.

Does anyone have any suggestions?

Thanks!

 

[Guus2005]

Sometimes, a reference to the standard VBA library is needed. Like this: VBA.Trim(var) or VBA.Left(var,1).
Don't ask me why.

HTH

 

[RuralGuy]

If you put a formula in a Control's ControlSource then you can not enter data through the keyboard. Maybe you want some code in the BeforeUpdate event of the control.

 

[vbaInet]

I think the OP is trying to Trim the value of the textbox and still be able to edit it. So maybe putting the Trim() in the After Update event of the textbox or in the Lost Focus event would do it.

Me.Textbox = Trim(Me.Textbox)

 

[jlathem]

I put =VBA.Trim([Comment]) in Control Source and I am getting #Name?

Do you think I should use the =VBA.Trim([Comment]) in the Before Update?

 

[jlathem]

I think the OP is trying to Trim the value of the textbox and still be able to edit it. So maybe putting the Trim() in the After Update event of the textbox or in the Lost Focus event would do it.

Me.Textbox = Trim(Me.Textbox)

Me.Textbox = Trim(Me.Textbox) in the After Update works GREAT!!

Thanks Everyone!

 

[vbaInet]

Glad we could help

 

[jlathem]

You guys are great! Not near the blokes the other forum makes you out to be! I don't care what they say, you guys are ok with me! :-0

 

[vbaInet]

There's always rivalry We're more of a community here (I think)

 

[jlathem]

You guys made me feel at home and not near the dunce I am in real life! LOL

Thanks!

 

[vbaInet]

Hehe!! One day you will be very comfortable in Access and I will be running to you for help! lol

 

[jlathem]

Hehe!! One day you will be very comfortable in Access and I will be running to you for help! lol

My whole life, I dodged using Access except for backend web applications but that all changed this week!

If I can ever help all you need do is ask! I'll be there!

 

[jlathem]

I have another problem I am about to post if you don't mind taking a look at it!

 

[vbaInet]

If I can ever help all you need do is ask! I'll be there!

Happy to know

Have fun coding!