Need help to construct simple MS Access DB - read Barcode number & update inventory (1 Viewer)

[larrystaley]

Need help to construct simple MS Access DB - read Barcode number & update inventory

Hello. I have been tasked with constructing a basic VB Access Database that will do the following:
1. User uses a basic barcode reader to scan in barcode number. So for example if scanning for a specific Jade, the number 20532 is scanned into an input field. Also, the quantity for the item has to be entered. If will normally be 1, but could be a multiple number. So would need two fields as Product Number: (20532 is then scanned in) Quantity: Enter the number as say 2, 5, 7, etc. or can just default to 1.
2. Then take this information, specifically the scanned product number (or 20532 in this case) and the quantity (whatever number I entered) and match it to an inventory table. The primary key for the table will be the product number. So therefore will already have 20532 as one to match (along with others) plus a separate field for the current quantity.
3. Finally, will need to print out the scanned bar for the item, basically the tag showing item name, barcode image, barcode product number and name of company. So I am looking at one example as: SUCCULENT CELFIE, barcode image under that name, 22605 as barcode number under the image, and then name of company, under the barcode number.
So far I have a simple input form as:
Barcode Product Number:
(text box to scan in the number)
Quantity:
(text box with the quantity and will just default to 1 if no other number is entered)
Perhaps someone has an example of how this is done. I know I will need some basic code that pulls in the scanned product code number to the inventory table matching the same scanned product code number there and then decrement the inventory count accordingly (with current inventory count as another field).
Thank you for your help in advance.
Larry Staley










35 minutes ago
Reply
|
Quote

Larry Staley
0

Points


Larry Staley Joined Aug 2016 1

Larry Staley's threads
Show activity





0 Points

 

[Uncle Gizmo]

Re: Need help to construct simple MS Access DB - read Barcode number & update invento

I did something similar for Thermo Electron a few years ago. Nothing really difficult to do, it's just knowing the order of how to do it.

From memory I recall I had a unbound text box in which I scanned the barcode. The barcode scanner can be setup to simulate the enter key, in other words the number is received from the barcode scanner, then it's as if the person entering in the data has pressed the enter key. This is normally the default condition of a barcode scanner straight out of the box. If your barcode scanner does not work like this, in other words it's second-hand or nicked from another department, you will need the setup codes that come with the barcode scanner, usually a pamphlet with lots of barcodes that configure the barcode scanner.

Let's continue assuming that your barcode scanner works in the normal manner. (Alternatively just key in the digits manually, then press enter) This would be the best way for testing in my opinion as it mimics exactly what the barcode scanner would do, without having to have one to hand.

Use the text box after update event, capture the barcode entered into the text box, then use it as criteria in a query to return the correct record. You can use the SQL version of the query for a record source for the subform to display the record.

You will probably need to test for a case where the scanned number does not exist in your table. In this case it will return 0 rows. You would have a separate query which you run first. This query would count the number of Records returned and if the return value was one (1) you would carry on as described above. If it was 0 or greater than 1 then you decide what you want to do in that circumstance. You can remove the greater than 1 possibility by applying the relevant index to the relevant field.

With regard to printing the barcodes, then it's just a case of adding the correct font to your computer. I found some suitable fonts free online. I recall it being a little awkward to setup the fonts. Probably worth reading up on it. I think there are several posts within this Forum as this the question has cropped up a few times over the years, so there's quite a bit of stuff within the forum to read up on.

I use this function from the Microsoft website which counts the number of records returns by An SQL statement.

Count the Number of Records in a DAO Recordset

This is the MS function adapted to my Style:-

Public Function fRecordCount(strSQL As String) As Long
'From MS Access Help
'Count the Number of Records in a DAO Recordset
'https://msdn.microsoft.com/en-us/library/office/ff836095.aspx
 
Dim strSubName As String
Dim strModuleName As String

strSubName = "fRecordCount"
'strModuleName = "Form - " & Me.Name
strModuleName = "Module - basModule Name Here"

On Error GoTo Error_Handler
   
 Dim dbRecordCount As DAO.Database
 Dim rsRecordCount As DAO.Recordset
   
     Set dbRecordCount = CurrentDb
     Set rsRecordCount = dbRecordCount.OpenRecordset(strSQL)
   
         If rsRecordCount.EOF Then
           fRecordCount = 0
         Else
           rsRecordCount.MoveLast
           fRecordCount = rsRecordCount.RecordCount
         End If
     
Exit_ErrorHandler:

    rsRecordCount.Close
    dbRecordCount.Close
 
    Set rsRecordCount = Nothing
    Set dbRecordCount = Nothing
    
    Exit Function

Error_Handler:
    Dim strErrFrom As String
    Dim strErrInfo As String
        
        strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
        strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Description:-" & vbCrLf & Err.Description
            
            Select Case Err.Number
                Case 1 'When Required, Replace Place Holder (1) with an Error Number
                    MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
                Case Else
                    MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
            End Select
        Resume Exit_ErrorHandler

 End Function   'fRecordCount

 

[Uncle Gizmo]

Re: Need help to construct simple MS Access DB - read Barcode number & update invento

Here's a search showing a few old posts on Bar Code Scanning to get you started...

 

[larrystaley]

Re: Need help to construct simple MS Access DB - read Barcode number & update invento

Uncle Gizmo:

Thanks for the input. One basic question at the very front end. If I add a barcode item, say 24206911, leave quantity as 1 (in another text field), and hit enter, I am fine and can then do another scan for a new number.

However, if I then scan say 35702103, but then change its quantity to say 5 from 1, and hit enter, the item is apparently being retained. If I then scan say 43004951, then the original value, in the bar scanner code box, actually gets concatenated and reads 3570210343004951 and quantity remains as 5 (I did set default value in quantity to 1).

Is there something I can do here with very simple VBA code so when I change quantity to 1, I can insure the first product ID is reset to blank so I only get the new one, say the 43004951, and also insure the quantity is truly reset to 1 in the quantity box. Thanks again.

L. Staley

 

[larrystaley]

Re: Need help to construct simple MS Access DB - read Barcode number & update invento

Also, Uncle Gizmo,

Where does this code go? Would it be under After Update Event for the main barcode item text box? Also, I said I really need the quantity always defaulted to 1. Therefore, in your code, should the statement you have for resetting the rsRecordCount really be:

Set rsRecordCount = 1 ? (to the point of always wanting a 1 to show for a new scan, even if I then decide to change the quantity to some other number, as associated with the newest scan).

Thanks again.

L. Staley

 

[Uncle Gizmo]

Re: Need help to construct simple MS Access DB - read Barcode number & update invento

Re:- but then change its quantity to say 5 from 1,

I think I'd set the number of items first, then run the code from the after update event of the text-box receiving the scan. Yuo could also use VBA to set the count back to 1..

If you think about the work flow of the operator, for low numbers 2, 3, 5, they would probably quickly scan them 1,2,3,4,5-- Zap! Zap! Zap! Zap! Zap! done.

 

[Uncle Gizmo]

Re: Need help to construct simple MS Access DB - read Barcode number & update invento

Re:- Set rsRecordCount = 1 ?

Your condition was:-
and match it to an inventory table.

if there's no match, then you find this by using the count function to determine the number of records that match the scanned in barcode. If the count returned is 0 then that means you don't have that item in your inventory table you've probably scanned your lunch box or something.