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
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
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.
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.
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.
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
I would place the code in the form BeforeUpdate event with error handling code to ensure that the 3 text fields have values.Code:Me.SerialNumber = Nz(DMax("SerialNumber", "YourTableName", "VendorID = '" & Me.txtVendorID &"' And CategoryID = '" & Me.txtCategoryID & "' And SubCategoryID = '" & Me.txtSubCategoryID & "'"), 0) + 1
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
Thank you to all friends, specially dear ssanfu
your detailed solution and time.
Happy to help....
Good luck with your project.