If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Barcode scanner and inventory management, how to scan & search for entry?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,274
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
__________________
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

Last edited by myle; 12-15-08 at 20:49.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
L33t Helpa Munky
 
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.

Quote:
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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:
How do I write a macro that opens up the single form data upon AfterUpdate?

Please help, thank you very much in advance.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Posts: 6
I finally understood what you meant by about search form now Thanks!
Reply With Quote
  #7 (permalink)  
Old
L33t Helpa Munky
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On