Access VBA for Barcode Scan

[Denae]

I have a database that tracks check-in and check-out of items. Each item has a barcode assigned to it with a three digit number. I recently purchased a gun scanner to scan the barcodes to check them in/out.

I want to scan the barcode and have the item number auto populate the form, then mark is as in or out then move to the next blank record so the next item can be scanned.

The form called frmBarcode_Check that has three fields on it with a control source of tblCheckedItems;
[ul]
[li]CI_Claim_Tag_ID (short text) - this is the 3 digit barcode number[/li]
[li]CI_TimeStamp (date/time) - date/time the item is checked in our out[/li]
[li]CI_Type (short text) - tracks in or out for the scan[/li]
[/ul]

Right now I have CI_Type defaulting to "IN", but I would like it to look for the last scan for the item and mark it the opposite, but I haven't started trying to figure out that code yet.

I have put the following code in the On Change property of CI_Claim_Tag_ID, if I scan a barcode that is in the system it marks it in and moves to a new record, the problem is if the barcode is not in the system, I can't get it to provide a custom error message and cancel the scan. It gives me a run time error (3201) and asks me to debug the code.

Private Sub CI_Claim_Tag_ID_Change()


Const conErrRequiredData = 3201

If Len(CI_Claim_Tag_ID.Text) = 3 Then

DoCmd.RunCommand acCmdSaveRecord


    If DataErr = conErrRequiredData Then
        Me.Undo
        MsgBox ("Barcode not in system.")
        Response = acDataErrContinue
    Else
        'Display a standard error message
        Response = acDataErrDisplay
    End If


DoCmd.GoToRecord , , acNewRec
    
End If

Any help is greatly appreciated.

 

[Andrzejek]

the problem is if the barcode is not in the system" - well, check it before you try to save the record:

(Not real code, just an idea... You can also use a LookUp function)

If Len(CI_Claim_Tag_ID.Text) = 3 Then

If RecordIsThere(CI_Claim_Tag_ID.Text) Then
    DoCmd.RunCommand acCmdSaveRecord
End If

    If DataErr = conErrRequiredData Then
...

Private Function RecordIsThere(ByRef intID As integer) As Boolean

strSQL = Select * From SomTable Where Tag_ID = " & intID
rst.Open strSQL
RecordIsThere = rst.RecordCount
rst.Close

End Function

Have fun.

---- Andy

There is a great need for a sarcasm font.