Results 1 to 7 of 7
  1. #1
    dale_j1992 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    8

    Autofill a field based on the first 7 digits of a barcode


    hello, i am currently in the process of making a database where the end goal is to able to scan a barcode and store part measurement data against the barcode. i currently have a form where the first field is generated by scanning a barcode, the barcode consists of 12 digits the first 7 being the part number, i need to find a way of filtering depending on the first 7 digit only that then autofill another form with the required part information, i have around 15 different parts that it could be selected from. any help with this would be greatly appreciated .

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Perhaps the Left() function is what you are looking for.
    https://msdn.microsoft.com/en-us/lib.../gg251556.aspx

  3. #3
    dale_j1992 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    8
    That link is perfect for selecting the part of the barcode i require, thank you. I am struggling slightly getting the field that relies on this number to autofill the data, what I basically want is if the barcode is 111111 (Camshaft1) or 222222(crankshaft1) then the following text box will auto fill automatically with camshaft1 if 111111 is inputted or vice-versa, i have only been using access for a few months so im still learning, any help provided will be much appreciated regards

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    What does the barcode represent? I am having a hard time understanding the barcode's relationship between the camshaft and the crankshaft. Would not the tolerances be different for each part? Do the first 7 digits represent a group that share the same tolerance?

    To display the part number group, you could include the Left() function in a query as an alias and then JOIN on the alias. So, work through most of your problem in queries.
    Something Like
    Code:
    MyAliasName: Left([TableName].[PartNumberName], 7)
    Just remember that what the Left function returns will be Text. So if you want to do some math on it, you will need to convert it to the proper data type.

    Once you have the alias, you can display that information to the User in a Form. If you need to do additional queries on the 7 digits, you will need an additional, well, query. So, maybe you need another query object that JOINS your first query on the MyAliasName. Your second query could include its own Alias that employs the Left() function. Use the alias' to JOIN the two.

  5. #5
    dale_j1992 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    8
    the barcode will be attached too the part and it will be that single crank or cranks personal identification number, the barcode is 12digits the first 7 digits is what i need too filter from because the last 5 digits are for the parts unique number for example the 56th camshaft woukd be 111111100056, the 1111111 of this number is what i need access too read and autofill some other fields in the table such as a text field saying camshaft. i hope this is more clear now. many thanks for your help.

  6. #6
    dale_j1992 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    8
    atm i have a database with a form, the first field is a number field. i have linked a usb barcode scanner so they scan the parts barcode first, its this field i need too use as mentioned above.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I think it will be easiest if you isolate different aspects of the big picture. Deal with the different aspects on their own front. For instance, entering measurement data for a specific serial number should be done while looking at the complete serial number and not the first 7 digits of the serial number. Of course, it is unwieldy for a user to sift through thousands of serial numbers, numbers that fall into many categories. It is not realistic to expect the User to understand 123456789 is for a Camshaft and not for a Crank.

    So, entering the Measurements for a given part is its own thing. Another thing would be entering the serial number for a given part.

    I would suggest dealing with the Inventory as a separate Business Process from dealing with the Tolerance.

    When a user is presented with a Crank that is not already in the Inventory, how do you deal with that? One part of the answer is to have the User scan a barcode. Now that we have the barcode, is there a way to Automate the process of category? What I am getting at here is, during data entry the User can verify and tell the database that this serial number is associated with a specific category.

    I suggest you start by having a table that is dedicated to part categories. In this table you would have a column that is a Primary Key. Each record will have a unique value for its PK (I recommend the AutoNumber type). For each record, you will have additional attributes. I suggest at least one other column, like a PartDescription or PartCategory. In PartCatagory, you can include the text that describes the category, Crank, Camshaft, Short Block, Solid Lifter, etc..

    Store the value of the Primary Key from tblPartCategories in the table that stores the Inventory. In your tblInventory would be the barcode value and also the Key value from the associated record in tblPartCategories. PK's from other tables are stored in columns that are defined as Foreign Keys.
    https://www.youtube.com/watch?v=-fQ-bRllhXc

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How can a barcode scanner autofill fields
    By Business in forum Programming
    Replies: 5
    Last Post: 05-26-2016, 11:01 AM
  2. Autofill field based on search
    By rd26 in forum Access
    Replies: 2
    Last Post: 03-05-2013, 09:20 PM
  3. Autofill based on Autonumber Field?
    By W.Chan in forum Access
    Replies: 2
    Last Post: 12-06-2012, 10:51 PM
  4. PLEASE help: Autofill based on one field
    By Suzanne in forum Forms
    Replies: 5
    Last Post: 07-07-2011, 11:09 AM
  5. Autofill of a field based on another
    By MyWebdots in forum Forms
    Replies: 7
    Last Post: 07-12-2010, 05:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums