How to get scanned data into my forms?

**TheSmileyCoder** · Apr 23 '10, 09:12 AM

That certainly sounds like an interesting challenge. You have given a very good description of what happens and what you want to happen.

The part im unfamiliar with is how the scanner works as input. If I understand your description correct, access will interpret the scanner as if it was a keyboard entering the numbers?

So that means the whole form should be about catching "keystrokes " which is perfectly possible, but before I go down this path, I just want to make sure about the above question.

Edit: It would also be possible for a user to scan 1 customer and then 5 boxes without scanning a customer?

Edit: When a box is reused will it always go to the same customer? Will it get a new Customer barcode and a new box barcode?

**DanicaDear** · Apr 23 '10, 10:43 AM

If I understand your description correct, access will interpret the scanner as if it was a keyboard entering the numbers? Yes, that is correct. You can program them to accept "Enter" or "Tab" after the each reading. Right now it's set up to "Enter."

It would also be possible for a user to scan 1 customer and then 5 boxes without scanning a customer? Yes, this would be totally possible and we considered this. However, we will ship and receive multiple customer boxes at once. If one customer's box got in with another customer's while we were entering, and we didn't scan the customer number each time, then the wrong box might get assigned to the wrong customer. So this was a safety check. Our processes are negotiable, especially if it makes a big impact on the ease of software development.

When a box is reused will it always go to the same customer? Will it get a new Customer barcode and a new box barcode? No, the boxes will go to multiple customers. Customers reorder gloves every 90 days so we use the box for a customer, get it back about a week later, use it on another customer, get it back a week later, etc. A particular customer does not get the same boxes. It's random.
The customer barcode is their customer account number. That does not change for a customer. We have index cards that slide in and out of the boxes and that allows us to put a new customer into the box for shipment. That is how a new CUST_NUM would be assigned to a BOX_NUM.
The box's barcode also does not change. The BOX_NUM barcode is somewhat permanently stuck to the box. The only thing that changes are the assignment of the boxes to the customers.

I appreciate you being willing to look at this. This is the climax of my DB writing for the 3 programs I am responsible for developing. I've been knowing this was coming for a year and if I can't get this to work there will be major disappointment.
I appreciate you noting my question was well documented. It only took me 5 hours to compose. LOL!!

**TheSmileyCoder** · Apr 23 '10, 10:51 AM

A well thought out and described question can often make the difference between someone like me taking the time to go the extra mile, or just dismissing the question with a "If he doesnt care enough to describe his problem, why should I care enough to help him solve it". This is certainly not a "simple question, simple answer thing". I will look into it.

**TheSmileyCoder** · Apr 23 '10, 11:43 AM

This is what I could come up with. It handles the shipping part of the boxes, but I am confident that you can work out a receiving part based on the code in here.

The form to be used is called frm_Shipping, and I've made comments in my code. If anything is unclear, please dont be afraid to ask.

Attached Files

Shipping.mdb.zip (38.4 KB, 319 views)

**NeoPa** · Apr 26 '10, 01:29 PM

If you had a main input control on your form that the bar-code reader would populate, you have some AfterUpdate code which :

Determined which type of bar-code is being presented. Five characters would imply a customer number . Anything less a box number.
If a customer number, then the current data in the form would be cleared down (and any potential final processing of any possible previous customer executed).
The data would then be moved to a TextBox for the customer number.
If a box number, then the number would be moved to the next available TextBox for the box numbers.
You may want to provide a more manual mechanism for retracing your steps in case of operator error. Some sort of box and/or customer reset.

Clearly the code would be responsible for apportionning the data to the right places, but the form would be very easy to use - at least as far as the data entered via the bar-code reader was concerned.

PS. I haven't looked at Smiley's suggestion, so I cannot tell if there is any overlap, but this is my thinking on the matter, put forward as a potential alternative, not as a better solution or indicating there may be anything wrong with what has already been proposed.

**DanicaDear** · Apr 26 '10, 06:23 PM

Smiley, I like what you've done here. I've been studying your code for a couple hours. I can't read code like a newspaper so it takes a while for it to sink in. But I'm totally on board. I like how you have the Box Numbers in individial records rather than having Box_num_1, 2, 3 like I currently have. I'm going to move in your direction on that. The only thing I initially see is my LastOrder (Max order number for a customer number) not going in. I'm going to try to play around with what I posted above. I don't know where it should go in your code but if I can't get it then I'll let you know. I don't want to ask until I've at least tried it out myself. Thanks very much for your handy solution. I hope I can get this working in my DB.

**DanicaDear** · Apr 26 '10, 06:48 PM

In the following code, I see Case 4 and Case 5. I want "Case 4" to say "Case 3 or 4" I tried copying the Case 4 code and repasting it with the "4" changed to a "3". That didn't work out too good. How can I account for the 3 digit boxes?
(I know I have to change the "4" on line 3 to a "3".)

Code:

If KeyAscii = 13 Then
        'Scan is complete (we hope)
        If Len(Me.tb_ScanID) < 4 Or Len(Me.tb_ScanID) > 5 Then
            'Some sort of error or user error
            MsgBox "Input error, resetting"
            GoTo doClear
        End If
        
        Select Case Len(Me.tb_ScanID)
            Case 4
                'Box
                If Not bCustomerScanned Then
                    MsgBox "A customer ID must be scanned first before scanning boxes"
                    GoTo doClear
                End If
                'Is box registered in database?
                If DCount("*", "tbl_Box", "KEY_Box=" & Me.tb_ScanID) = 0 Then
                    'Box does not exist in DB
                    MsgBox "Box " & Me.tb_ScanID & " not recognized in tool"
                    GoTo doClear
                Else
                    'Box exists.
                    
                    'Assign box to current customer, set shipping date=now, and received date to null and bMissing to false
                    Dim strSQL As String
                    strSQL = "UPDATE tbl_Box SET tbl_Box.ID_Customer = " & Me.tb_KEY_Customer & ", tbl_Box.dt_Shipped = Now(), tbl_Box.dt_Received = Null, tbl_Box.bMissing = False" & _
                            " WHERE (((tbl_Box.KEY_Box)=" & Me.tb_ScanID & "));"
                    DoCmd.SetWarnings (False)
                        DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings (True)
                    Me.subFrm_Boxes.Requery
                    GoTo doClear
                    
                    
                End If
                
            Case 5
                'Customer
                'Lets find customer entered
                Me.Recordset.FindFirst "KEY_Customer=" & Me.tb_ScanID
                If Me.Recordset.NoMatch Then
                    MsgBox "Customer barcode not recognized"
                    'Do whatevter you want to handle this case
                End If
                bCustomerScanned = True
                GoTo doClear
        End Select
    End If

**DanicaDear** · Apr 26 '10, 09:13 PM

Oh my good grief my head is about to explode. LOL. Sometimes you have to just sit back and laugh at yourself.

I have been trying for 8 hours now to get this going...first studying, then doing. I know I must be getting close. My first step is to get this working, then begin changing what I want to. I have all my forms and tables set up similar to the example provided. However, when I try to scan, the customer number will not update on my version. :-( One big question is have is this: At the top of the code, I see this:

Code:

Option Compare Database
Option Explicit
Private bCustomerScanned As Boolean

I can't figure out what bCustomerScanne d is or where it is located. Smiley's properties sheet on the Shipping Form made reference to a module. I can't find a module anywhere. It is possible that this alone is my problem but I'm not betting that I won't have more questions.

**TheSmileyCoder** · Apr 26 '10, 10:45 PM

A module can actually mean 2 things. A module can be attached to a form, and if your looking at the shipping form it has a properties: HasModule=True, which basicly means that the form has code attached (As opposed to nothing or macros). A forms module can only be accessed by the form itself, and thus the code in the form is per default private. Think of a form with code, as a page in a book, with scriblings on the backside :P You can also have standanlone modules, where you put code you use alot, or put code you need to access from within several different forms, or perhaps in querys.

The bCustomerScanne d was something I put into the form, because when you first open the form, it will be on the first customer, so I wanted to ensure that a customer had been scanned before allowing a box scan to happen. There are more elegant ways of doing this though, but I didn't want to get into those at the moment. The variable is used during the Case 5.

Now about the case 3 or 4, the problem is that computers are a bit more literal then we are. Im guessing you have written
Case 3 or 4
That has to be written: Case 3,4
The comparision must evaluate to TRUE. 4 is not an evaluation and 4 is not by itself TRUE.
Its kinde like writing:
X=1 or 2 or 3 or 4
which Access wont like. What access can understand is a series of expressions which each individually can be evaluated to either true or false. Example:
X=1 or X=2 or X=3 or X=4
Now the notation for SELECT CASE is bit different, so there you write:
SELECT CASE 3,4.

Sorry for typos, its really late here, I just wanted to get this off, before going to bed. :)

**NeoPa** · Apr 27 '10, 12:15 PM

Smiley has your Case answer well explained. I would add that for this particular situation you may choose to say write it as Case 5 followed by the rest, which would be Case Else.

If you put the cursor on the word Case and press F1 for Context-sensitive help, you will get a pretty good explanation of all your options within the Select Case Statement. This is a worthwhile option to use for handling multiple different options. Well worth your while learning about.

**DanicaDear** · Apr 27 '10, 01:37 PM

Code:

Im guessing you have written "Case 3 or 4"

How did you know? LOL. Thanks for the explanations. They are easy to understand. I will make this change when I get the form actually working and scanning.

I have been trying really, really hard to get this to work. I think I am very close. But I can't find my error(s). Two things bothering me. (I'm sure there are more but two are obvious.)

1. In the code below, lines 4-12 are what I did to try to make Order_Num show only the max Order_Num for each customer in the form. It isn't working...I assume it's in the wrong place (I have tried many) or in the wrong format. The form will at least load. :-)

2. The DB that Smiley provided for me scanned items exactly like I was wanting it to. When I open my form (frmBOX_SHIPPIN G), the scanner reads the Cust_Num but it doesn't update the form to show the Cust_Num just scanned. It isn't updating correctly. I've spent 6 hours trying to find out why and as usual when I type these questions out on Bytes I get some more ideas. I just noted that Smiley's code was based on fields that were mostly number types. My fields are mostly text types. (The only field of mine in the pasted code below that is a number type is "Missing". All the rest are text.) Those ridiculous little tick marks are getting me again!!!!! Of course, it might be worse than that.... I'm willing to try to do to tick marks but I'm afraid I'll muck it up so badly you won't be able to make heads or tails of it. I'll try that on my own without showing the world my stupidity. If it works, I'll report back. LOL.

Code:

Option Compare Database
Option Explicit
Private bCustomerScanned As Boolean
Private Sub Form_Open(Cancel As Integer)
Dim varLastOrderNumForCust As Variant


'Last/MAX Order Number for Customer
varLastOrderNumForCust = DMax("[ORDER_NUM]", "tblOrders", "[CUST_NUM] = '" & Me![CUST_NUM] & "'")


End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
    'A key has been pressed.
    
    
    'If key pressed is numeric, add it to the ID
        If IsNumeric(Chr(KeyAscii)) Then
            'Concatenate it to the end of the ID, and then convert _
back to long (In theory you could keep it as a string all the time I guess)
            Me.tb_Scan_Cust_Num = Me.tb_Scan_Cust_Num & _
Chr(KeyAscii)
            Exit Sub
        End If
    
    
    
    If KeyAscii = 13 Then
        'Scan is complete (we hope)
        If Len(Me.tb_Scan_Cust_Num) < 4 Or Len(Me.tb_Scan_Cust_Num) > 5 Then
            'Some sort of error or user error
            MsgBox "Input error, resetting"
            GoTo doClear
        End If
        
        Select Case Len(Me.tb_Scan_Cust_Num)
            Case 4
                'Box
                If Not bCustomerScanned Then
                    MsgBox "A customer ID must be scanned first _
before scanning boxes"
                    GoTo doClear
                End If
                'Is box registered in database?
                If DCount("*", "tblBOX", "BOX_NUM=" & Me.tb_Scan_Cust_Num) = 0 Then
                    'Box does not exist in DB
                    MsgBox "Box " & Me.tb_Scan_Cust_Num & " not recognized in tool"
                    GoTo doClear
                Else
                    'Box exists.
                    
                    'Assign box to current customer, set shipping date=now, and _
received date to null and bMissing to false
                    Dim strSQL As String
                    strSQL = "UPDATE tblBOX SET tblBOX.CUST_NUM = " & _
Me.tbCUST_NUM & ", tblBOX.DATE_BOX_SHIP = Date(), _
tblBOX.DATE_BOX_RETURN = Null, tblBOX.Missing = False" & _
                            " WHERE (((tblBOX.BOX_NUM)=" & Me.tb_Scan_Cust_Num & "));"
                    DoCmd.SetWarnings (False)
                        DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings (True)
                    Me.subFrm_Boxes.Requery
                    GoTo doClear
                    
                    
                End If
                
            Case 5
                'Customer
                'Lets find customer entered
                Me.Recordset.FindFirst "CUST_NUM=" & Me.tb_Scan_Cust_Num
                If Me.Recordset.NoMatch Then
                    MsgBox "Customer number not recognized"
                    'Do whatevter you want to handle this case
                End If
                bCustomerScanned = True
                GoTo doClear
        End Select
    End If
    
    


Exit Sub
doClear:
    Me.tb_Scan_Cust_Num = ""
    Me.tb_FocusKeeper.SetFocus
        
End Sub


Private Sub Form_Load()
    Me.tb_FocusKeeper.SetFocus 'Access must have an active _
textbox in order to properly recieve keypress _
(At least I couldnt get it to work without)
    bCustomerScanned = False
End Sub

One final note: I have made some somewhat serious changes to my DB to try to get this to work, including table changes, new forms, queries, the whole bit. Just want ya'll to know this is not just a copy/paste forum for me where I lack thought of my own. I do rely heavily on your expertise but it is not because I'm lazy. My DB and my skills are evolving as we go. Thanks to you all. :-)

Attached Files

GloveProgram042710.zip (111.2 KB, 214 views)

**TheSmileyCoder** · Apr 27 '10, 01:43 PM

Code:

            Case 5 
                'Customer 
                'Lets find customer entered 
                Me.Recordset.FindFirst "CUST_NUM='" & Me.tb_Scan_Cust_Num &"'"
                If Me.Recordset.NoMatch Then 
                    MsgBox "Customer number not recognized" 
                    'Do whatevter you want to handle this case 
                    bCustomerScanned = False
                Else
                  bCustomerScanned = True                   
                End If 

                GoTo doClear

I added the ' where appropriate, and moved the bCustomerScanne d a bit around as well. If CUST_NUM is NOT a numeric type field, then yes, you need the tickmarks around it to make sure access understands it is a string.

**NeoPa** · Apr 27 '10, 02:21 PM

Originally posted by DanicaDear

Those ridiculous little tick marks are getting me again!!!!!

Check out Quotes (') and Double-Quotes (") - Where and When to use them.

**topher23** · Apr 29 '10, 08:11 PM

Danica - I looked at what you had put together and realized that this was very similar to somthing I built for one of my DB's. An issue we had to address was "What if the scanner breaks? How would the operator know what was happening?" The changes I've made address that by using a text box to capture the data rather than using a keypress capture on the form. The form can be operated both using keyed entry and scanned entry with equal functionality.

Attached Files

GloveProgram042710[1].zip (104.5 KB, 220 views)

How to get scanned data into my forms?

How to get scanned data into my forms?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment