I'm hoping someone can help me with this one or perhaps steer me to a better solution.
I have a DB that handles inventory for an ice cream shop (and nope, it's not homework...they really pay me with ice cream )
Anyhoo, it's a bar code scanner situation where employees scan ice cream containers in/out of a large freezer. It's mainly high school kids doing the scanning, so I've made it as hands-free as possible. They've been using the db for a year and it's working well with one exception. The owner has asked me to track mis-scans. This happens if there is some human-hardware error, OR if the product code is not in the db yet (this is the case 90% of the time).
I am testing the attached code with the scanner and it seems to be ok. I fire off a popup form for the user to input their name and any notes about the mis-scan. I've put this in the On Error. It works ok, but I'm not certain it's a very sound way of handling the error.
My question is...is this a good way to handle this situation?
This code fires at each scan.
Any thoughts are much appreciated.
Private Sub keyProductID_AfterUpdate()
On Error GoTo BadScan
Dim cnnCurrent As New ADODB.Connection
Dim rstCurrent As New ADODB.Recordset
Dim strSQL As String
Set cnnCurrent = CurrentProject.Connection
strSQL = "SELECT Currentlvl FROM tblLocationDetail WHERE ((keyProductID = '" & Me![keyProductID] & "') And (keyLocationID = " & Me![keyLocationID] & "));"
rstCurrent.Open strSQL, cnnCurrent, adOpenKeyset, adLockOptimistic
rstCurrent!Currentlvl = rstCurrent!Currentlvl + Me!Quantity
DoCmd.GoToRecord , , acNewRec
Set cnnCurrent = Nothing
Set rstCurrent = Nothing
'Set public variable to store whatever the scanner picked up
'Variable is later stored in tblBadScan
pubstrBadScanProductID = Me!keyProductID
'Go to popup form to enter bad scan info
Me![keyProductID] = ""
It's a predictable error. I would perform a DCount prior to execution and run a switch asking if this is a new item to be entered. You can then gracefully enter the new item and continue execution. In the process of entering the new item, it would be trivial to store when/who/what was entered in a seperate table.
I like Teddy's approach, howevere it does presume that the person doing the scan is authorised / approved to create the record. Furthermore will they have all the information available to doa full data capture. If the goods are delivered at a loading dock will that employyee have all the information (eg supplier, product description, pricing etc.) You may have to extend the process to allow your office back end to identify what products have been entered on a 'bad scan' so that the information is validated before it is released. The risk is that product could then be shipped and invoiced with null or incorrect data. If it is not spotted quickly then it can get very very expensive.
H'm payment in Ice Creams, when I was younger I'd of killed for that job, but then being a greedy pig I'd probably died at that job soon after
Based on your (and Teddy's) recommendations, I think I'm on the right track. Basically, I have some custom security set up for a managers to log in and everyone scans under that name. Security isn't a big concern for them, but accuracy is. So what I've done is captured the bad scans in a temp table, so the manager can reconcile it at the end of a day. Whether it's a new product or some other error.
The basic problem was that their inventory was always off by a few containers (and I didn't take them...I swear ) . So they need to know if they're truly getting bad scans, or if the kids at night simply aren't scanning at all.
I agree with Teddy that it's trivial to store that data, provided they're really doing the scanning. So we'll find out and go from there.
Thanks again guys.
BTW Teddy, the shop's on Marshall & Cleveland in St. Paul...you may want to check it out if you're in the neighborhood...it's fantastic ice cream.