Solved

Allow only barcode scanner to input data into an Access text field

Posted on 2010-11-10
24
2,522 Views
Last Modified: 2012-05-10
I am trying to set up a text field in Access to allow only barcode scanner input.  I came accross the following post that seems to be my only solution, however my VBA skills are somewhat limited.  How would I code the solution suggested in the last paragraph.

"The only difference between keyboard and barcode-reader is the typing speed ...
Try measuring the time needed to insert the digits of the barcode and clear the field if typing is too slow"

"Simply remember the time of the last editing of the text in the textbox and compare it with the time of the current update.
You can use the function Now for this
And the calculate the difference with DateDiff function"
0
Comment
Question by:joe_GDB
  • 12
  • 10
  • +1
24 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 34104598
Not wanting to be awkward but what happens if the bar code is unreadable?
0
 

Author Comment

by:joe_GDB
ID: 34104764
The barcode will come from a sticker accompanying a microchip that will be implanted in a dog.  This is a vet procedure in a clinic so each barcode should be readable.  If for some reason a barcode can’t be scanned then I (DBA) will enter that chip id.
0
 
LVL 75
ID: 34104828
Home Again chips?

Not quite following the question.  Typical scanners will dump data into any 'text' control that currently has the Windows focus, eg Notepad, Word, an Access Text or Combo box, etc.

All three of my indoor only cats have the HomeAgain chips and collar tags.  I've had my vet confirm that they can all be read.

mx
0
 

Author Comment

by:joe_GDB
ID: 34105148
Actually Avid is the chip company…but the same idea as Home Again.  I’m talking about initially entering the data into the database NOT a search look up type of situation. I have the ability to manually enter data for a search situation but I want to lock the data entry when we actually put the chip in the animal and link to the database.  I know that I can scan the chip into a text field, that is not the problem.  I don’t want any of the Vets/Techs to input the chip manually into the database, only via a scanner (since this is such an important piece of data).  I want to be able to lock the text field from a manual keyboard entry and only allow a scanned entry, and if for some reason a scanned entry isn’t possible we have set up some business rules that allow for manual entry.

With that said, I’ve tried locking the text field but then that also locks a scanned entry.  I’ve looked at some posts on EE saying that the type of scanners we have “keyboard entry type” won’t let me do this unless I try this “speed typing trick” that I mentioned in my original post and I'm not sure how I would put this code in my form.

Sorry for the long winded post,
0
 
LVL 75
ID: 34105237
" I want to be able to lock the text field from a manual keyboard entry and only allow a scanned entry"
I see.  Not aware of the 'speed typing trick'

So, for the moment ... right now, you can then ... scan the code into a text box and it subsequently gets stored ... ?
0
 
LVL 75
ID: 34105326
You might try this for that specific text box (example)

Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
    KeyCode = 0: Beep
End Sub

Locked = No, Enabled = Yes.

You also need to set the Form's Key Preview property to Yes.

I'm think this will allow a scanned code to come in ...

mx
0
 

Author Comment

by:joe_GDB
ID: 34105384
Yes, that functionality exists.  There have been too many data entry mistakes when someone thinks they don't need the scanner and want to manually enter the data.
0
 
LVL 75
ID: 34105440
" There have been too many data entry mistakes when someone thinks they don't need the scanner and want to manually enter the data."

I totally get that ... and I would be doing the same thing.  If fact, I'm going to run this by my vet, who I am friends with.

Are you in So Cal ?

All of my cats are registered on the HomeAgain website, with pics and medical information.  I get 'lost pet' notifys (in my general area) daily from the site.

0
 
LVL 84
ID: 34105523
No points for me:

Throwing away the KeyCode, as suggested by MX via the KeyDown event, should work. That code will fire whenever any key is pushed. You might also consider showing a MsgBox when this happens, to try and train the users:

Msgbox "Use the scanner, will ya??"


0
 

Author Comment

by:joe_GDB
ID: 34105663
"Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
    KeyCode = 0: Beep
End Sub

Locked = No, Enabled = Yes."

the example you provided on the Key_Down event did not work...it locked out both keyboard and scanned entries.

"Are you in So Cal ?"
No, just north of San Francisco...Marin.
I actually belong to the organization Guide Dogs For The Blind…we breed and train all our dogs as well as have our own vet clinic…we just now are starting to chip all our dogs (a few hundred) …so when we roll this out the initial data entry is going to be a nightmare if I allow ENYONE to manually enter the data.
0
 
LVL 75
ID: 34105716
I was afraid that might happen, probably because the reader is ending the text with a LineFeed and/or Carriage Return or Tab ...?  Although technically I would not consider that a 'KeyPress'.

I have another way ... let me finish lunch first ...

mx

0
 
LVL 75
ID: 34106944
Lets try this mod:

Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 10 Or KeyCode = 13 Or KeyCode = 9 Then Exit Sub
    KeyCode = 0: Beep
End Sub

mx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:joe_GDB
ID: 34107237
Private Sub MicrochipID_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 10 Or KeyCode = 13 Or KeyCode = 9 Then Exit Sub
    KeyCode = 0: Beep
End Sub

Enabled = Yes and Locked = No

With the above code and properties set I'm still unable to manually or scan data in.
0
 
LVL 75
ID: 34107263
Lets try the KeyPress event:


Private Sub Text1_KeyPress(KeyAscii As Integer)
    If KeyAscii = 10 Or KeyAscii = 13 Or KeyAscii = 9 Then Exit Sub
    KeyAscii = 0: Beep
End Sub

"manually "
But you are trying to avoid manual entry, right?

mx
0
 

Author Comment

by:joe_GDB
ID: 34107401
"But you are trying to avoid manual entry, right?"

Yes, i'm trying to aviod manual entry.

"Private Sub MicrochipID_KeyPress(KeyAscii As Integer)
    If KeyAscii = 10 Or KeyAscii = 13 Or KeyAscii = 9 Then Exit Sub
    KeyAscii = 0: Beep
End Sub"

This code has the same result - not able to scan data in -
0
 
LVL 75
ID: 34107426
Do you have code in any other event of this text box, like the Before Update or AfterUpdate or Focus/Enter ?

mx
0
 

Author Comment

by:joe_GDB
ID: 34107508
The text box is actually unbound with no code in any other event.  Everything happens via an "enter" button on the form.
0
 
LVL 75
ID: 34107545
Prior to anything we have changed so far ...
When you do a scan, you then see the barcode in the text box I assume.  What happens immediately after that?  Some code must run to process the scan ..?

mx
0
 

Author Comment

by:joe_GDB
ID: 34107801
It's basically an input box style form with only a text box and an Enter button.  Once the chip ID is in the text box the user clicks the enter button.  The enter button has all the code to take the chip ID and insert it into the database.
0
 
LVL 75
ID: 34107858
I'm not seeing right off why the scan is not working with the Keypress code. I guess somehow it's reacting to the characters in the barcode and since it's resetting the Keycode to zero they are all getting rejected.  But that seems odd, since no Key is being pressed, so I can't see how that is getting triggered.

Back to this:

Private Sub MicrochipID_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 10 Or KeyCode = 13 Or KeyCode = 9 Then Exit Sub  ' Put Breakpoint here ****
    KeyCode = 0: Beep
End Sub

Can you set a Breakpoint on the first line, do a scan, and 1) confirm the code breaks there and 2) hover the mouse over Keycode and see what it reads?  

mx
0
 

Author Comment

by:joe_GDB
ID: 34107929
Private Sub MicrochipID_KeyDown(KeyCode As Integer, Shift As Integer)
51563196A If KeyCode = 10 Or KeyCode = 13 Or KeyCode = 9 Then Exit Sub  ' Put Breakpoint here ****
    KeyCode = 0: Beep
End Sub

I put the breakpoint where you suggested…the barcode scanned in where the breakpoint was inserted (what?).  The numbers before the IF statement isn’t a typo that is where the barcode scans in.
I verified that the cursor was in the text box and that there was no curser in the code, but still the scan enters where the breakpoint is…
Keycode = 49 on mouse over.
0
 
LVL 75
ID: 34107967
49 is a 1  (one).

Seems the scanned is seeing the vba window as a text box. Humm.  Can't even be sure the Keypress event was triggered.  Ok ... this instead ( no break point)

Private Sub MicrochipID_KeyDown(KeyCode As Integer, Shift As Integer)
     MsgBox "The Keycode is " & KeyCode    
    ' If KeyCode = 10 Or KeyCode = 13 Or KeyCode = 9 Then Exit Sub  ' Put Breakpoint here ****
    ' KeyCode = 0: Beep
End Sub

lets see if this really triggers.  Note the other lines are commented out for the moment.
0
 

Author Comment

by:joe_GDB
ID: 34112323
The code seem to trigger, after scanning a barcode the message box read "The Keycode is 49" after clicking ok the MicrochipID text box displays a 1...as you said 49 is a 1.

0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 34115736
Well ... not sure why the Keypress is triggered in this scenario.  Looks like there is a 1 in the 2nd and 6th position of that barcode (51563196A).  Not having a scanner puts me at a disadvantage in testing ideas, which I'm about out of at the moment.  Seems since it's being triggered, the message box would show the first character.  

mx
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

746 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

10 Experts available now in Live!

Get 1:1 Help Now