Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Posts
    6

    Barcode scanner and inventory management, how to scan & search for entry?

    Hello,
    I am new to MS Access. I would like to know is it possible to do the following?

    I have an inventory list of many items with bar code numbers in Excel file. I imported that into Access. So.. is it possible to do this: As soon as I scan a bar code on an equipment, it will look through the database to see whether it exist or not? If it does not exist it will tell you. (And it will be even better if it will have an option that lets you mark that the item is there).

    Is there any tutorial on this sort of inventory management? I tried to search but I didn't get any result that meets what I want. Please advice, thanks in advance.

    P.S. Once again, I am new to MS Access. I would love to have some guides on what I am supposed to be looking into if I want to learn more about this sort of database management. Though this case is urgent, so I would love possible solutions. Thank you again.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    Just think the barcode scanner is a keyboard

    most barcode scrann can to something after the scan ie press the enter key

    therefore

    on the access form keypress down = 13 then do a
    aa= dcount("*","tablename","productcode=" & barcodefeild )
    if aa>0 its there other wise its not.

    Q: Why did you use Excel then import into Msaccess

    here is some bar make to make 128

    Code:
    Option Compare Database
    Option Explicit
    ' Written by Rodney Marr (RodMarr@mailcity.com) October 7, 2000
    '
    ' Barcode 128-B Generator
    '
    ' Permission granted for public use and royalty-free distribution.
    ' No mention of source or credits is required.
    '
    ' I got a lot of help from the following people's work
    ' Russ Adams' BarCode 1 Web Page   http://www.adams1.com/pub/russadam/info.html
    ' A Free 128-B code generator in Visual Basic by Stefan Karlsson (mrswede@libertysurf.se)
    ' And the Creator of the code 39 Module
    '
    'For Notes on how to use this code look at the code for the barcode39 Generator
    '
    ' us put a text box on the on the report
    ' in this case barcode is the text feild and me been the report
    'Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
    '    Result = SetBarData(Barcode, Me)
    'End Sub
    Public Function SetBarData(Ctrl As Control, Rpt As Report)
        
        On Error GoTo ErrorTrap_SetBarData
        
        'Code 128B has 5 main parts to it. The first part is a start character(211214), followed by DataCharcters. The Data
        'Characters are followed by a check(or Checksum) Character, and that is followed by a stop Character(2331112)
        'The last part of Code 128B is the two quiet sections at the front and back of the barcode. These are 10 dimensions
        'Long(I am thinking that is 11 modules long). Each character is 11 modules long, except the stop character which is
        '13 modules long
        
        Dim CharNumber As Variant, CharData As Variant, CharBarData As Variant, Nratio As Variant, Nbar As Variant
        Dim barcodestr As String, Barcode As String, Barchar As String, Barcolor As Long, Parts As Integer, J As Integer
        Dim tsum As Integer, lop As Integer, s As Integer, checksum As Integer, p As Integer, barwidth As Integer
        Dim boxh As Single, boxw As Single, boxx As Single, boxy As Single, Pix As Single, NextBar As Single
        Const White = 16777215: Const Black = 0
        
        'This is the Raw data that I threw into an arrays
        CharNumber = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16,", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29,", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106")
        CharData = Array("SP", "!", Chr(34), "#", "$", "%", "&", "'", "(", ")", "*", "+", ",", "-", ".", "/", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", ":", ";", "<", "=", ">", "?", "@", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "[", "\", "]", "^", "_", "`", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "I", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "{", "|", "}", "~", "DEL", "FNC 3", "FNC 2", "SHIFT", "CODE C", "FNC 4", "CODE A", "FNC 1", "Start A", "Start B", "Start C", "Stop")
        CharBarData = Array("212222", "222122", "222221", "121223", "121322", "131222", "122213", "122312", "132212", "221213", "221312", "231212", "112232", "122132", "122231", "113222", "123122", "123221", "223211", "221132", "221231", "213212", "223112", "312131", "311222", "321122", "321221", "312212", "322112", "322211", "212123", "212321", "232121", "111323", "131123", "131321", "112313", "132113", "132311", "211313", "231113", "231311", "112133", "112331", "132131", "113123", "113321", "133121", "313121", "211331", "231131", "213113", "213311", "213131", "311123", "311321", "331121", "312113", "312311", "332111", "314111", "221411", "431111", "111224", "111422", "121124", "121421", "141122", "141221", "112214", "112412", "122114", "122411", "142112", "142211", "241211", "221114", "413111", "241112", "134111", "111242", "121142", "121241", "114212", "124112", "124211", "411212", "421112", "421211", "212141", _
                                              "214121", "412121", "111143", "111341", "131141", "114113", "114311", "411113", "411311", "113141", "114131", "311141", "411131", "211412", "211214", "211232", "2331112")
                                              
        barcodestr = "211214" 'Add the Startcode for Start B (characterset B) to the barcode string
        tsum = 104                      'And this is the value for that startcode which will be added with the other character values to find the checksum character
        boxx = Ctrl.Left: boxy = Ctrl.Top: boxw = Ctrl.Width: boxh = Ctrl.Height    'Get control size and location properties.
        
        Barcode = Ctrl                                                          'Set handle on control.
    
        Nratio = Array("0", "15", "30", "45", "60")           'Set up the array for the different bar width ratios
        Parts = ((11 * (Len(Barcode))) + 35) * Nratio(1)  'This is the formula for the width of the barcode
        Pix = (boxw / Parts)                                                  'Here I find out exactly how many Pixels a bar will be
        Nbar = Array((Nratio(0) * Pix), (Nratio(1) * Pix), (Nratio(2) * Pix), (Nratio(3) * Pix), (Nratio(4) * Pix)) 'Set up the array to handle the pixels for each type of bar
        
        'Loop through all bardata to count the sum for all characters and add barcode charcter strings the to the barcode string
        For lop = 1 To Len(Barcode)
            Barchar = Mid(Barcode, lop, 1)
            If Barchar = " " Then Barchar = "SP"
            For s = 0 To UBound(CharData)
                If Barchar = CharData(s) Then
                    barcodestr = barcodestr & CharBarData(s) 'This is where I added the character strings to each other to make one long string of 1's, 2's, 3's, & 4's
                    tsum = tsum + (CLng(CharNumber(s)) * lop) 'Here every barcode character's number value is multiplied by its position in the line and added to tsum
                    'The actual formula for find the the  Checksum  is "(104 + (1 * CharcterNumber) + (2 * CharcterNumber) + ...)/103" You would Use the Remainder as
                    'The Checksum Character. In the case of "BarCode 1" the formula would look
                    'like "(104+(1*34)+(2*65)+(3*82)+(4*35)+(5*79)+(6*68)+(7*69)+(8*0)+(9*17))/103=20 with Remainder of 33" Therefore the checksum would equal 33
                    Exit For
                End If
            Next s
        Next lop
            
        checksum = tsum - (Int(tsum / 103) * 103)                                             'Here I use the the totat sum (tsum) to find the checksum
        barcodestr = barcodestr & CharBarData(checksum) & "2331112" 'Here I add the checksum then the stop character into the barcode string
            
        'lets do some initialization
        Barcolor = Black
        NextBar = boxx + 11     'I added the 20 for the whitespace (or quiet space) at the beginning of the barcode
        
        'Draw the Barcode
        For J = 1 To Len(barcodestr)
            Barchar = Mid(barcodestr, J, 1)   'Reuse variable barchar to store the character to be drawn
            barwidth = CInt(Barchar)              'Change the barcode charcter into an integer so I can use in the array part of the next line
            Rpt.Line (NextBar, boxy)-Step(Nbar(barwidth), boxh), Barcolor, BF  'Draw the line
            NextBar = NextBar + Nbar(barwidth)                                                      'Calculate the next starting point
            If Barcolor = White Then Barcolor = Black Else Barcolor = White      'Swap line colors
        Next J
    
    Exit_SetBarData:
        Exit Function
    
    ErrorTrap_SetBarData:
        MsgBox Error$
        Resume Exit_SetBarData
    
    End Function
    all I did was put the code a module and pass the item I want tobe a barcode it showed on the screen the printed
    Last edited by myle; 12-15-08 at 21:49.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  3. #3
    Join Date
    Dec 2008
    Posts
    6
    Thank you for your help, but I am a bit confused I don't really understand the instruction that you have posted.

    The original file is in Excel format. But then I feel that Access seems to be able to do a better job for inventory management than Excel.

    Also I have done further researching.. it seems what I want to do is something that has to do with "Combo Box."

    As in... every bar code is unique in the existing database that I have. My task is to check the equipment to make sure they are in the same location, else I will need to update the location. So... what I want to do is to have my files in Access, as soon as the scanner reads a number, it will search through the database to make sure there is no duplicates, if there is duplicate, it will let me update the new location. If there is no duplicate, then it will ask me to enter the information for that barcode.

    Thank you again, I appreciate your help.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Keeping it simple, just ignore the barcode scanner. Set up a form that has a field in it that allows you to enter your barcode manually. When the cursor leaves the field (after pressing Enter), make it do your db search and/or update.

    It also has nothing to do with combo boxes; you can set up your barcode scan field as a text box or a combo box, they will both work as well as each other.

    The barcode scanner itself basically enters a string and presses enter. Some of them don't though, you have to be careful in choosing your scanner.

    But then I feel that Access seems to be able to do a better job for inventory management than Excel.
    DEFINITELY!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2008
    Posts
    6
    Hi, thank you for the suggestion. I have read more about Access, but I am still unable to do what I want I have posted my new question in here:
    http://www.dbforums.com/microsoft-ac...terupdate.html

    Please help, thank you very much in advance.

  6. #6
    Join Date
    Dec 2008
    Posts
    6
    I finally understood what you meant by about search form now Thanks!

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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