Link to home
Start Free TrialLog in
Avatar of joe_GDB
joe_GDBFlag for United States of America

asked on

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

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"
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Not wanting to be awkward but what happens if the bar code is unreadable?
Avatar of joe_GDB

ASKER

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.
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
Avatar of joe_GDB

ASKER

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,
" 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 ... ?
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
Avatar of joe_GDB

ASKER

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.
" 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.

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??"


Avatar of joe_GDB

ASKER

"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.
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

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
Avatar of joe_GDB

ASKER

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.
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
Avatar of joe_GDB

ASKER

"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 -
Do you have code in any other event of this text box, like the Before Update or AfterUpdate or Focus/Enter ?

mx
Avatar of joe_GDB

ASKER

The text box is actually unbound with no code in any other event.  Everything happens via an "enter" button on the form.
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
Avatar of joe_GDB

ASKER

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.
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
Avatar of joe_GDB

ASKER

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.
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.
Avatar of joe_GDB

ASKER

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.

ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial