Solved

Symbol Barcode Scanner into MS Access 2010

Posted on 2013-11-27
27
936 Views
Last Modified: 2013-12-04
I am developing a scanning database in MS Access 2010 and using Symbol/Motorola LS 4278 Bluetooth Barcode scanners.
Some of the barcodes I scan work perfectly but some only repeat the data from the previous line in the table. Almost as though there is something in these barcodes that copies the line above... It sounds crazy and is a little difficult to explain.

When scanning you can see the correct data going across the field, it then disappears and is replaced by the data in the field above.

I have created a brand new table in a blank database and it still occurs. In Excel or Foxpro database it works perfectly.

I have attached an example of the problematic code. Not sure if its res will be good enough though.

Any ideas would be gratefully received.
Barcode.JPG
0
Comment
Question by:iainmacleod
  • 14
  • 13
27 Comments
 
LVL 57
ID: 39680605
<<It sounds crazy and is a little difficult to explain.>>

 No, I understood.  Couple of comments:

1. Keep in mind that scanned data will look as if it came from the keyboard.  That means any keyboard shortcuts you have defined (or Access uses) will be invoked if the data contains that sequence.

2. Watch out for any navigation keystrokes that might be being sent.  For example, a tab character (x09).

To pin this down,  I would in a new database create a single test form with a single control and a command button.  

Create a procedure for the OnClick for the button with nothing more then a STOP in it.   Then open the form and scan the bar code.  Then click the button.

Now you can check the contents of the text control using the debug Window and see what it actually contains.

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39680661
Hi Jim,
Thanks for this.
Have created a form with a text control and can scan the code. In the button i have an event procedure with STOP. I dont understand how to use the debug window though.
Iain
0
 
LVL 57
ID: 39680904
With the text control having the focus, scan the bar code.

Since the form is unbound, it's not going to go to another record.

Now click the button.  The VBA window will come up.   Key:

Ctrl/G

 to bring up the debug window.

Now you can do things like:

?  Me.<mycontrolname>

"?" being short for:

Debug.Print

 and see the value of the text control in the debug window.   You can use things like Asc(), left(), mid(), etc to pick it apart and see what characters your actually getting.

 That should give you a clue as to what's going on.  For example, you see an 09, which is a tab character.

 In Access by default, a tab character will move you to the next field.   If your on the last field in a form, then you might move to the next record.

 It's also possible you had some code interferring with the scanning, which is why I suggested a clean DB.

 You next step in debugging if you find nothing here would be to create a table in this DB, then bind this test form to it and try scanning a bunch of values.

 If the table looks OK after that and your not moving from record to record, then the problem is something your doing in your original DB with macro's or code.

 You can also place STOPs in things like the AfterUpdate event of the form, or OnCurrent once it's bound to a table to see what events are getting executed.  You can also add a second text control to see how it's cycling through controls/records.

 At the moment though, you've reduced it down to the simplest test possible.  You want to build up from here.  At some point, you'll find out what's going on.

Jim.
0
 
LVL 57
ID: 39680932
One other thing to check:

Look at the driver settings/scanner settings and see if it is set to transmit any prefixes or suffixes on the scanned data.  Depending on what it is, that can cause problems.

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39681341
Ok great. I have done that:
When scanning barcode onto Unbound control i get the correct barcode with no unexpected characters. (Data is the same as when i scan to Excel)
If i bind the form to a table and make it continous, the first Barcode I scan is correct, the second one I scan (Although is different data) it shows as exactly the same as the first barcode.

This is a completely clean database..
0
 
LVL 57
ID: 39681577
<<If i bind the form to a table and make it continous, the first Barcode I scan is correct, the second one I scan (Although is different data) it shows as exactly the same as the first barcode.>>

 Did you bind the control as well?   Unbound controls in a continous form view will show the same value on every row.

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39681587
Yes i bound the control
0
 
LVL 57
ID: 39681700
<<(Data is the same as when i scan to Excel)>>

 Your saying two cells are the same in Excel when you scan two different codes?  Then that says the problem is in the gun and/or driver.

But that doesn't add up with your comment in your original post that this works in Foxpro or Excel fine.

I'm confused.

Take your test setup, unbind the form and control.  Add a second text control to the form.

Set focus to the first, scan one bar code, then sent focus to the second (manually) and scan the second bar code.

Do you get the same data or no?

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39681732
Sorry to confuse you. .
In excel and on an unbound form with 2 controls I can scan 2 different barcodes and get 2 different sets of data.as I would expect.

Only when I bind the control on a continuous form or scan directly to a table do I get the first barcode data duplicating...
0
 
LVL 57
ID: 39681771
OK.  So what happens on the test form with two seperate text controls with the forms and controls unbound?

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39681777
Data displays correctly
0
 
LVL 57
ID: 39681808
Take it one step further then; bind the form to a table and the control to a field, but leave it in regular form view.
 
 Scan the code for the first record, then move to the second record and scan the second bar code.   Data should be different.

If so, then:

a. you sure your binding the control to a field when in continous form view?

b. You are the one manually moving between the two records when in continous from view, correct?

c.  If you look at the table directly, you see the same data in both records?

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39681941
Thanks Jim.  I will check that out tomorrow.  Appreciate your time
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:iainmacleod
ID: 39683467
In regular form view with the control bound i scan 2 different barcodes but get the same data in each.....
It is almost as though once the table updates it copies the previous record.
0
 
LVL 57
ID: 39683478
Ctrl/' will copy the previous value.

What is the bar code data for each of the scans?

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39683483
If I scan to Excel then this is the data:
LEPPL13111234567
LEPPL13120000121

Scanning straight to Table i get duplicates of whatever the first one I scan..
0
 

Author Comment

by:iainmacleod
ID: 39683493
I have just used Techoriver Free barcode generator, copied that data and created new barcodes and all works perfectly. It must be something in the format of the original codes..
0
 

Author Comment

by:iainmacleod
ID: 39683623
Found out that barcode is CODE128, do you think that might be the issue?
0
 
LVL 57
ID: 39685512
Quite possibly.  Code 128 can do the full ASCII range, so it can send a control code.

Let's see if that's really what's going on:

1. Set the Keypreview property of your form to Yes.

2. Then in the Keydown event, do:

  If KeyCode > 17 Then KeyCode = 0

  This disables the Ctrl Key.

 Now see if it works right.

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39689487
HI,
with the If statement in the keydown event nothing scans at all?
0
 
LVL 57
ID: 39689808
Sorry, check should be:

 If Shift = 2 then keycode = 0

That will disable the control key.

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39690202
That was it.... Scanning perfectly now with correct data..
0
 
LVL 57
ID: 39695324
Well sorry to say, but I don't think were quite out of the woods yet.  So now we know what's happening, but not why.

I'm not sure I understand why the Code 128 results in a Ctrl character being inserted.  I'm wondering if the gun/driver is somehow doing that.

What is the actual data you have encoded?

Jim.
0
 

Author Comment

by:iainmacleod
ID: 39695702
The barcodes are not created by me so not sure exactly how they are done.
Dont think it is the Gun as other 128 codes scan OK.
With the ability to remove the Ctrl i can at least get me application to do what is needed.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39695776
OK...guess we'll call it done then.  Note that you may not want to do it at the form level, but just on the control itself.

Think I'd stick with the form though.

Jim.
0
 

Author Closing Comment

by:iainmacleod
ID: 39695800
Thanks Jim, really appreciate all of your help
0
 
LVL 57
ID: 39696180
No problem.  I'm just sorry it was such a drawn out process (been REALLY busy as of late - in fact I'm going nuts at the moment - It's been a crazy couple of weeks).

Jim.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article we have discussed the manual scenarios to recover data from Windows 10 through some backup and recovery tools which are offered by it.
What do we know about Legacy Video Conferencing? - Full IT support needed! - Complicated systems at outrageous prices! - Intense training required! Highfive believes we need to embrace a new alternative.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now