Automatic "Enter" in Access 2000 1

[ffemt1]

At the end of an assembly line, I am currently tracking parts using 2 pieces of bar-coded information, the part number and the serial number of each part. We currently have a Symbol LS 1902F scanner reading the information into an Excel spread sheet. After one barcode is read, we have to press "tab", then read the other barcode, then press enter which returns the curser to the next line. This is very labor intensive, and does not really allow us to check for unique serial numbers.

I have a fairly simple database in Access with the fields "part number", "serial number", "date" and "time". The first 2 will be input using the barcode reader, and the date and time are automatically entered using the default settings "date()" and "time()".

My question is how do you have software read a barcode, automatically advance to the next field?

The part number and serial number both vary in length and format so i do not think I can use a format mask which I think allowes me to use the autotab function.

Here are actual numbers:

part #
KFCEH0401N03
EHK05AKN1
MKFCEH1201D08

Serial # (always the same length)
S1704V04369
SV042006161

Any ideas?

 

[cghoga]

Use the SendKeys function.

 

[ffemt1]

Here is what I have now.

Private Sub Part_Number_AfterUpdate()
SendKeys ("{TAB}"), True
End Sub


Private Sub Serial_Number_Change()
SendKeys ("{TAB}"), True
End Sub

I have added a part numbers table, and on the form, I am using a drop down box for the part number. If an incorrect part number is scanned, you have to manually hit "enter" if you want to accept it, otherwise it just sits there.

I think I am close.

THANKS

 

[ffemt1]

Well, that hasn't worked yet. It works on my computer when I cut and paste numbers in the fields, but it looks like the barcode reader reads numbers individually, so the "Change" command changes on the first character in the serial number, but the last number. Also, it reads the part number, but doesn't select it from the table. I will keep trying.

 

[bistec]

Wouldn't it be better just to program the scanner to send a CR at the end of the barcode?

All modern barcode scanners can be programmed to send all sorts of things and emulating an enter key is just one of them.

With the scanners you should have got a programming manual. If you didn't go to the web site of the manufacturer and download it.

Done heaps works just fine.

Good luck

Howard

 

[willir]

Howard is correct on this.

But in case this does not work, consider this work around...

You may be able to use the events on a form - On Timer event and the Timer property in the property window.

Setting the Timer may be a bit tricky, perhaps not depending on the speed events occur, and how you integrated your scanner to the data entry.

It would work like this...
Every X seconds, the On Timer event fires off. It
- Checks to make sure all required data has been entered
- If all criteria have been met, then it inserts the record and moves on the next add record event.
- If the criteria have not been met, then no activity until the next timer event.
- The operator could intervene

What I would be curious is how you handle duplicate serial numbers. Do you abort the data entry, require user intervention. Or accept the record but flag the record for later review.

Richard

 

[bistec]

Wouldn't a "not in list" event procedure be more useful than the form timer? Correct me if I'm wrong, but the prog is only now hanging when an invalid serial no is entered.

I suppose it depends on what you want to do with an incorrect part no. as to how you would proceed with this event. You could tell it continue on error or stay on the control (as it's doing) to re-enter a correct one.

So.... I ask. What do you (ffemt1) want to do with invalid reads?

Howard

 

[willir]

My comments were based on the statment...

Automatic "Enter" in Access 2000
...
My question is how do you have software read a barcode, automatically advance to the next field?

But I agree that the not in list event would be an excellent way to handle unknown serial numbers.

In general, a combination of event procedures would be needed to get this thing working properly.

 

[ffemt1]

It works great when I "cut and paste" into each field on my computer, but hangs in both fields when I use the barcode reader out on the production line on their computer.

I like the idea of having the barcode reader send the "enter" commands. I will look into that.

As for errors, I want it to get an error message and wait for the operator to check what was scanned. Because of the duplicate serial number problem that started this, I am now going back through the data looking for other problems. (About 140,000 records) Since we currently have to press "tab" and "enter" after the scans, I am finding a lot of extra characters in the data, like:

'\ZIKFCEH0901N10 not ZIKFCEH0901N10
qS1404V04505 not S1404V04505
" ZIKFCEH0901N10" not "ZIKFCEH0901N10"

Here is what I want to do:

Scan a part number and check to see if it is on a list of current numbers, and give me an error if it is not.

Scan a serial number, check to see if it is unique, and has 10 characters, and if not, give me an error.

 

[willir]

ffemt1

Whoops, was out most of the day...

Getting the barcode reader to append the CR+LF would really be idea.

Here is a bit of code that may give you on how you can scan / check your data to avoid some of the mishaps. It uses the BeforeUpdate event (instead of the before insert event which I had thought of before hand).

Assumptions
- WidgetTbl - table for inventory, and includes the serial number
- PartTbl - table to check parts
- Field names, self explanatory, but will need to be changed by you to fit your situation
- DLookUp used to validate data / verify absense of data. It is totally dependent on your table design, and will have to be tweaked accodrdingly.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strPartCode As String, strSerialNum As String, strTest As String
Dim intTest As Integer, strQ As String, strMsg As String, varTest As Variant
Dim booPass As Boolean, intSerialNumLen As Integer

strQ = Chr$(34)
strMsg = ""
booPass = True

intSerialNumLen = 10

strPartCode = Nz(Me.Partcode)
strSerialNum = Nz(Me.SerialNum)

'Strip bad charcters from Part Number
'and make sure part code exists
strTest = strPartCode
TestString strTest
If Len(Nz(strTest)) > 0 Then
    varTest = DLookup("[PartDesc]", "PartTbl", "[PartCode] = " & strQ & strTest & strQ)

    If Len(Nz(varTest)) = 0 Then
        booPass = False
        strMsg = strMsg & "Part Code: " & strPartCode & " not found" & vbCrLf
    Else
        strPartCode = strTest
    End If
End If


'Strip bad charcters from Serial Number
'and test for 10 characters, stored as variable
'If you want to test serial number BEFORE inserting
strTest = strSerialNum
TestString strTest
If Len(Nz(strTest)) = intSerialNumLen Then
    varTest = DLookup("[PartCode]", "WidgetTbl", "[SerialNum] = " & strQ & strTest & strQ)
    If Len(Nz(varTest)) > 0 Then
        booPass = False
        strMsg = strMsg & "Serial Number: " & strSerialNum & " already exists" & vbCrLf
    Else
        strSerialNum = strTest
    End If
Else
    booPass = False
    strMsg = strMsg & "Serial number length incorrect" & vbCrLf
End If


If Not booPass Then
    strMsg = "Update Aborted for Part " & strPartCode & " SN # " & strSerialNum & vbCrLf & strMsg
    intTest = MsgBox(strMsg, vbOKOnly, "Bad Scan")
    Cancel = True
    Me.Partcode.SetFocus
Else
    'in case characters stipped off, update fields
    Me.Partcode = strPartCode
    Me.SerialNum = strSerialNum
End If

End Sub

String testing module as follows...

Private Sub TestString(strTest As String)

'A = ASCI 65, Z = ASCI 90, 0 = ASCI 48, 9 = ASCI 57

Dim strValid As String, strHold As String, intTest As Integer, intX

strValid = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
strHold = ""

For intX = 1 To Len(strTest)

    If (InStr(1, strValid, Mid(strTest, intX, 1))) > 0 Then
        strHold = Left(strHold, intX - 1) & Mid(strTest, intX, 1)
    End If

Next intX

strTest = strHold

End Sub

Comments:
- I had thought of using the ASCI value for the charcters but then re-thought strategy, and used string manipulation instead -- easier to maintain. You just need to add acceptable characters to strValid.
- This is not a fool proof system but should reduce the number of errors
- If the data fails, it puts back to the PartCode field. You may decide to "blank" the fields instead of leaving the offedning data intact
- If offending characters successfully stripped off, new data is displayed and used to update. You may wish to prompt the end user to manually accept the modified data.
- You can set the serial number to use only 10 characters. If so, the strip routine will not work because the first 10 characters including the unacceptable character is accepted. Nonetheless, limiting the field to 10, and using an index, no duplicates would be another approach.

Richard