Replace Chr(9) (1 Viewer)

Leo_Polla_Psemata

Registered User.
Local time
Yesterday, 16:45
Joined
Mar 24, 2014
Messages
332
Hi,
In one form, I have a long text field, named text2.
I copy and paste in the field text2 details in bulk, then very easily i update other fields in the same form.
The problem is that the data source contains "tabs", CHR(9)
I need to make one "before update event" which will kill all the CHR(9) from the field

This works just perfect in a query, how can i turn it into a vba "before update" ?
Replace(Text2,Chr(9),"")
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:45
Joined
Sep 21, 2011
Messages
10,863
Haven't you just answered your own question?
However as you are not validating, the afterupdate would also work, wouldn't it?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:45
Joined
Feb 19, 2013
Messages
14,736
If you are copy pasting use the control before update event - but refer to the control text property

replace( Control.text,Chr(9),"")
 

Leo_Polla_Psemata

Registered User.
Local time
Yesterday, 16:45
Joined
Mar 24, 2014
Messages
332
Haven't you just answered your own question?
However as you are not validating, the afterupdate would also work, wouldn't it?
Code:
Private Sub Form_AfterUpdate()

Replace(Control.Text2,Chr(9)," ")

End Sub

Hi, i type this but it is red
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:45
Joined
Sep 21, 2011
Messages
10,863
Code:
Private Sub Form_AfterUpdate()

Replace(Control.Text2,Chr(9)," ")

End Sub

Hi, i type this but it is red
I am not suprised.
You need to assign something to the result of the replace function.
In your case it would be Me.Text2 or perhaps Me.Text2.Text? I have not used the .text property hardly at all.
You also need to use that control and it's text property in the replace function.
CJ_London's post was an example, you need to use the correct control name.
Text2 is not very descriptive either. :(
Start giving your controls decent names to indicate what they are for.
 

strive4peace

AWF VIP
Local time
Yesterday, 18:45
Joined
Apr 3, 2020
Messages
922
hi @Leo_Polla_Psemata ,

> Replace(Text2,Chr(9),"")

maybe with space instead? and Space Linebreak with Linebreak, and Linebreak Space with space
and then replace 2 spaces with one? ... and so on ...

would be in AfterUpdate for control, not BeforeUpdate
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:45
Joined
May 7, 2009
Messages
16,867
you can use the Form's BeforeUpdate (meaning before the record is saved).
Code:
Private Sub FormBeforeUpdate()
Me!Text2 = Replace(Me!Text2 & "",Chr(9)," ")
End Sub
 

strive4peace

AWF VIP
Local time
Yesterday, 18:45
Joined
Apr 3, 2020
Messages
922
why make any changes unless necessary? Better to use control AfterUpdate -- and if control value is null, then exit checking/changing.
Using Form's BeforeUpdate with the above suggestion above means that Null will be replaced with a zero-length string, which isn't a good idea.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:45
Joined
May 7, 2009
Messages
16,867
with all due respect, why make any changes unless necessary?
are you the OP? all my replies are but "suggestions". i am not forcing anything (maybe that is why you are reacting? thinking that Only your suggestion is the Absolute?).
 

strive4peace

AWF VIP
Local time
Yesterday, 18:45
Joined
Apr 3, 2020
Messages
922
I am merely suggesting the CONTROL AfterUpdate instead of a form event since a possible change is only necessary if the control value has changed. We are here to give the best help we can, and that is exactly what I am doing ~
 

Users who are viewing this thread

Top Bottom