Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313

    Unanswered: Opinions on error code

    Hi all,

    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.

    Chris

    Code:
    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.Update
    	rstCurrent!Currentlvl = rstCurrent!Currentlvl + Me!Quantity
    	rstCurrent.Update
    	
    	DoCmd.GoToRecord , , acNewRec
    	Me![keyProductID].SetFocus
    	Set cnnCurrent = Nothing
    	Set rstCurrent = Nothing
    Exit Sub
    BadScan:
    	DoCmd.SetWarnings False
    		'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
    	DoCmd.OpenForm ("frmBadScan")
    	Me![keyProductID] = ""
    	Me![keyProductID].SetFocus
    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Thanks a lot Teddy,

    Makes sense and is a much better idea. I'll give it a shot.

    Chris

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  5. #5
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Thank you Healdem,

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •