Results 1 to 7 of 7
  1. #1
    mfarooq is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    3

    Creation of Auto Number based on criteria of two fields

    Hi, Have a Good Day to friends,



    Kindly help me to create auto number based on criteria of two fields.
    For example my auto number should be changed when city name is changed.

    Thank you and Best Regards,
    Farooq

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,857
    Autonumber is just that, an Autonumber.
    If you want some sort of index yourself, then you create your own, perhaps by concatenating the value of the two controls?

    You would still use the Autonumber field to link records, not your concatenated fields.

  3. #3
    mfarooq is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    3
    Actually I have data, vendor id (3 char), Category id (2 char), Sub category id (3 char) and serial number (5 char).
    My auto number is JPRIPFWL-00001, JPRIPFWL-00002, JPRIPFWL-00003. But when Category id change the auto number should be start from 1.
    For example for Category IT and Sub Category ITF the auto number should be JPRITITF-00001.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,857
    You need to code for that yourself.
    However I would not use that as the PK/FK, I would use an AutoNumber field. Your is NOT an Autonumber field. I cannot stress this enough.

    You could use DMAX() function to find the last 'number' for your business key and create your own key, but that is for your business, not for the DB.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have to agree with Welshgasman. You do not want an Autonumber. What I think you want/mean is AutoIncrement.

    Here is info about the Autonumber Type field:

    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.


    Autonumbers--What they are NOT and What They Are
    Microsoft Access Tables: Primary Key Tips and Techniques




    You could use the DMAX() function to get the max Serial Number, then add 1. It would look something like
    Code:
    Me.SerialNumber = Nz(DMax("SerialNumber", "YourTableName", "VendorID = '" & Me.txtVendorID &"' And CategoryID = '" & Me.txtCategoryID & "' And SubCategoryID = '" & Me.txtSubCategoryID & "'"), 0) + 1
    I would place the code in the form BeforeUpdate event with error handling code to ensure that the 3 text fields have values.

    Or you could write a UDF to get the max serial number where the VendorID = something and the CategoryID = something and the SubCategoryID - something, then add 1. Same as above, but you can add checks to ensure that the 3 text fields have values.

    So you would have 4 fields: VendorID, CategoryID, SubCategoryID and SerialNumber , In a query you would have a calculated column to see the value:
    Code:
    ReferenceNum: VendorID & CategoryID & SubCategoryID & SerialNumber

  6. #6
    mfarooq is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    3
    Thank you to all friends, specially dear ssanfu
    your detailed solution and time.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help....

    Good luck with your project.

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

Similar Threads

  1. Replies: 10
    Last Post: 04-01-2018, 07:45 AM
  2. Replies: 6
    Last Post: 09-17-2017, 07:20 PM
  3. auto number fields
    By gwboolean in forum Access
    Replies: 8
    Last Post: 09-17-2016, 02:12 PM
  4. auto-number for multi criteria or
    By jaytejani in forum Access
    Replies: 3
    Last Post: 09-30-2014, 10:22 AM
  5. auto fill name based on file number
    By mark_w in forum Forms
    Replies: 13
    Last Post: 08-03-2012, 04:59 AM

Tags for this Thread

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