Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Access VBA for Barcode Scan

Access VBA for Barcode Scan

Access VBA for Barcode Scan

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;
  • CI_Claim_Tag_ID (short text) - this is the 3 digit barcode number
  • CI_TimeStamp (date/time) - date/time the item is checked in our out
  • CI_Type (short text) - tracks in or out for the scan
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.

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
        MsgBox ("Barcode not in system.")
        Response = acDataErrContinue
        'Display a standard error message
        Response = acDataErrDisplay
    End If

DoCmd.GoToRecord , , acNewRec
End If 

Any help is greatly appreciated.

RE: Access VBA for Barcode Scan

"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

End Function 

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close