(barcode)scanning customer/Storenr and set the fields of the next productbarcodes (1 Viewer)

[killerflappy]

Hi. I’m a beginner in Access VBA.

I started a learning project for scanning and matching barcodes. We receive shipments (physical) and orderfiles from a couple of customers. In these orderfiles we get the barcode, storenumber, shippingdate en quantity. We put these files combined in the tblOrders. We sort products of these shipments by route and store. The stores of different customers are sorted in one route. We scan the barcodes to match with the orderfiles and make documentation for drivers. The barcodes identify the customer, store, product and quantity.

After sorting the products by route and store, sometimes a product is placed at the wrong store.
At the moment I use a complex Excel (no VBA) file and before I scan the barcodes of the products I first scan the barcode witch has the Custstr in it. This automatically detected because it start with letters. I puts the customer and the storenumber in separate columns until the new Custstr is scanned. This is done with some If/and formula’s that checks for the first letters of the customers name.

In access I try to copy this proces, The scanproces is very simple and flexible Datasheet View. In the attached access-file the user is scanning the barcodes in the FrmMutations.

With my current frmMutations I can't notice when a product is sorted at a wrong store. In the attached access-file I want to be able to scan the barcode of a Custstr (see tblStore) and then automatically populate the Customer en Storenr of the FrmMutations for the next productbarcodes, until I scan the next Custstr.

For example: The barcodes of the product all begin with a number. The Custstr begins with letters. So I begin scanning a Custstr (Example IR1001) then a barcode of a product and automatically it sets the Customer (IR) an Storenr (1001) in the fields next to the barcode. After all product of this store are scanned, I scan the next Custstr (IR1002). And so on.

On the frmMutations I already have some coding done for barcode at the AfterUpdate event. Maybe I can detect letters in this even and than store the customer/storenr and put them in the fields when I scan a productbarcodes until I scan a new Custst? With Do While and letters recognizing?

Is this possible?

 

[pbaldy]

There is no code in the after update event of the barcode, or anywhere else. You can certainly parse out the customer and store. If the pattern is consistent, you could use the Left() and Right() functions. If not, you can loop through the scan character-by-character, using the Len() and Asc() functions.

 

[killerflappy]

Hi pdaldy. Thanks for the information. I simplified the access files. Therfore I left out the after update event of the barcode. I have some customers with two letters and some with 3 letters at the beginning. Do you have a bid more datails so I can figure out how to use this?

 

[pbaldy]

Something to play with:

Public Function ParseString(strTest As String)
  Dim x As Integer

    For x = 1 To Len(strTest)
        If IsNumeric(MID(strTest, x, 1)) Then
            Debug.Print "Customer: " & Left(strTest, x - 1)
            Debug.Print "Store: " & MID(strTest, x)
            Exit Function
        End If
    Next x
End Function

results:

?ParseString("abc1234")
Customer: abc
Store: 1234
?ParseString("ab1234")
Customer: ab
Store: 1234

 

[killerflappy]

Got it! thanks

 

[pbaldy]

Happy to help!