Table field as control source and manipulating the data (1 Viewer)

[Tourless]

Hi Folks.

Apologies if this is not the right forum.

I'm working on a solution to scan barcodes into a table. I setup a form with a textbox and it's control source is tied to a field in my table. The scanner I'm working with is programmed to add characters to the scanned value so 012345 is presented as `012345` (notice the tic symbols). I want to remove those extra characters but I'm not sure how. So far I've tried adding

Barcode = Mid(Barcode, 2, Len(Barcode) - 2)

to the KeyPress and Enter events but it's not working. I'm thinking either I'm using the wrong event or my syntax is way off. Maybe something like

Barcode = Mid(txtBarcode.Value, 2, Len(Barcode) - 2)

? I'm going to keep digging around but hope someone with a bit more knowledge than I can chime in here an show me the error of my ways. - Thank you in advance.

 

[CJ_London]

try using the barcode afterupdate event

barcode=replace(barcode,"'","")

or perhaps the before update event

barcode.text=replace(barcode.text,"'","")

 

[Tourless]

try using the barcode afterupdate event

barcode=replace(barcode,"'","")

or perhaps the before update event

barcode.text=replace(barcode.text,"'","")

Thanks for replying CJ, but unfortunately neither of those worked.

 

[Gasman]

You have to use the correct character to replace?

Barcode = Replace(Barcode,"`","")

 

[Tourless]

You have to use the correct character to replace?

Barcode = Replace(Barcode,"`","")

Good thought Gasman but I know I'm using the correct symbol, I programmed the scanners.

 

[Gasman]

So show us your code between code tags.
I have never heard of a Replace failing if the characters being searched for are correct.
Perhaps print the ASC() character to see what it actually is, if it is not that obvious.?

 

[Tourless]

So show us your code between code tags.
I have never heard of a Replace failing if the characters being searched for are correct.
Perhaps print the ASC() character to see what it actually is, if it is not that obvious.?

I'm thinking the problem is not with the Replace but with the item I'm trying to replace. Let me try to be more specific...
Starting with a form I scan a barcode into txtBarcode which has a control source of Barcode (a field in tblPackout). The barcode scanner sends the the data (with it's starting and ending tic symbols) and an enter key. The enter has a little code in it to create a new (next) record, and updates a counter on the form.
So far I've been trying the Replace code on on various txtBarcode events (BeforeUpdate, AfterUpdate, Enter) and none of them work. I'm not sure if I'm just not using the right event or if I'm not referencing the tblPackout!Barcode field properly on the replace. Anyway here's my little code...

Option Compare Database
Option Explicit

Private Sub txtBarcode_BeforeUpdate(Cancel As Integer)

    'Barcode = Replace(Barcode, "`", "")
    Barcode = Left([txtBarcode], Len([txtBarcode]) - 1)

End Sub

Private Sub txtBarcode_Enter()
    DoCmd.GoToRecord , , acNewRec
    Forms!frmPackout!txtDailyTotal.ControlSource = "=Count([PackDate] Like Date())"
End Sub

 

[Tourless]

Well apparently I don't need to worry about any of that because I can set the input mask of the textbox to 000000000000... only the numeric characters and yes, 12 of them. Cool.

 

[Gasman]

@CJ_London said that you have to use the .text property in the Before_Update event, not the .Value property. That would be used in the After_Update event.

 

[The_Doc_Man]

Looking at what you showed us first, you were using Accent Grave as opposed to Accent Acute / Apostrophe. The two Accent marks ARE different characters. Is there a chance that your strings are set up for UNICODE rather than ASCII text?

I checked my VBA Language Reference and it says that the Apostrophe is a quoting character but doesn't list the Accent Grave as such. USASCII doesn't actually have a representation for Accent Acute; they just call it "Apostrophe." So my thought is that you might try a two step approach.

Debug your program and put a breakpoint just after you have loaded the string. Let's say it is in variable Barcode. From the immediate window, type the command Debug.Print ASC( Barcode ) - which according to the description of the ASC command, will give you the numeric code for that character. If it really IS an Accent Grave, it will return code number 96. But whatever it returns, remember that number.

Then when you do the REPLACE function do a REPLACE( Barcode, CHR$( whatever-the-code-number-was), "" )

That should get rid of the unwanted character.