check field and do a replace

[james_halliwell]

Hi all,

i have a subform which i scan bar codes into via a barcode reader, occasionally that last character could be a space, what i need to do if possible is to replace the space with a "1" before it tabs onto the next field

is there a replace funtion that you do this,

any advise on how to achive this would be a big help as access automatically removes any blanks spaces after text

 

[pbaldy]

Did you try the Replace() function?

 

[james_halliwell]

Did you try the Replace() function?

Hi and thanks for taking the time to read and help with my post,

i tried the below code in the beforeupdate

Private Sub Barcode_BeforeUpdate(Cancel As Integer)

Dim LResult As String
LResult = Replace([Barcode], " ", "1")

End Sub

but nothing happns, its tabs to the next field and does not replace the space as i think the space is automattically deleted when i tab

my vba is very basic so struggling with it a bit

 

[sparks80]

Hi,

Your code is storing the altered barcode in a variable, and does not amend the actual field.

Try:

Me!Barcode = Replace(Me!Barcode, " ", "1")

 

[pbaldy]

Also, you may need to use the after update event rather than the before update event.

 

[james_halliwell]

Hi and thanks for your response's
both nearly got me there but i learned about the replace funtion which will come in handy but i dont think the replace will work

been looking at this for a while and this was my last hope, it looks like the space at the end of the text just disapears before the after update for before update works?

tried 1234567_ (The _ is the space to show you)
even when i clicked on control H there was no space at the end?

123 567 worked it went 1231567 but the space is always at the end?

cheers anyway guys if you can think of another way that would be amazing but looks like access just cannot do it

 

[pbaldy]

I don't have a scanner handy to test with. Brief testing confirms that the space is gone in the update events. It's still there in the change event, and it could normally be replaced in the keypress event, but I'm not sure if those will work with a scanner doing the input. You can test. This works in the KeyPress event with regular typing into a textbox:

  If KeyAscii = 32 Then
    KeyAscii = 49
  End If

 

[james_halliwell]

Jesus Christ

It worked? thank you so much i thought that was it, I wont even ask how you knew that thats impressive stuff????

cheers pal for your help in sorting this and teching me somthing new

also thank you to Sparks80 for teaching me the "replace" function this will come in handly in future projects

 

[pbaldy]

Ah, glad it worked. Even a blind squirrel finds an acorn now and then.