Results 1 to 4 of 4
  1. #1
    DataBaseNoob is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    Duncan, SC
    Posts
    1

    Calcualted Fields: Entering Serial Numbers to Autofill Model types


    Hello,

    I'm attempting to program a main datatable to pull model types from a seperate table or query when a serial number is entered. My goal is to have the user only need to scan the serial number into the datasheet to have the model description autofill and not be forced to select from a combo box as the model list is extensive.

    The seperate "Models" table has three fields: ID, Characters, and Description.

    Note: In the serial number format I am using, the fourth and fifth characters determine the model type (description). I have programmed a field within the main table (named "4th&5th") by using a calculated field (MID[SN],4,2).

    Any help would be appeciated!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    where to start?

    these are bad ideas or just won't work

    all of these will cause you problems down the line - see this link https://www.access-programmers.co.uk...d.php?t=225837
    1. naming fields (or tables/forms etc) with non alpha number characters
    2. starting said names with a number
    3. using reserved words - https://support.microsoft.com/en-us/...ions-of-access

    4. you cannot 'program' a table - you use queries and forms/reports
    5. calculated fields in tables have limited use - limited calculation options and more importantly cannot be indexed which will have detrimental effect on performance
    6. lookup fields in tables also have limited use and will cause more work down the line as well as detrimental to performance - http://access.mvps.org/access/lookupfields.htm
    7. you cannot autopopulate a table beyond setting default values
    8. the rule for a field should be that a field holds a single 'fact'. Your serial number contains at least 2 facts. Better to keep each fact to a separate field and combine in a query as required

    so to answer your question you need to use a form, not enter data directly in a table.

    and the table should store the component parts of the serial number

    you would use something like dlookup to get the model details and display in another control on the form.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not clear on your structure but the first thing that comes to mind is a DLookup() using those 2 characters:

    http://theaccessweb.com/general/gen0018.htm

    Symbols and spaces in object names are not worth the trouble in the long run.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Geez, I feel asleep at the keyboard.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. How to Get Serial Numbers In Sub Form
    By deepakg27 in forum Forms
    Replies: 8
    Last Post: 09-24-2018, 03:51 AM
  2. Autopopulating Serial Numbers
    By nkoenig34 in forum Programming
    Replies: 3
    Last Post: 01-19-2016, 11:07 AM
  3. Help with Serial numbers table
    By RandyP in forum Programming
    Replies: 2
    Last Post: 06-11-2014, 03:39 PM
  4. mutli serial numbers in one
    By wirelineuk in forum Queries
    Replies: 1
    Last Post: 10-11-2012, 01:26 PM
  5. Automatic Serial Numbers
    By Mitch87 in forum Access
    Replies: 9
    Last Post: 02-18-2010, 12:57 PM

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