Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    87

    Unanswered: generating special ID for new record

    Ok here is the next step i need to figure out

    I need to have unique ID for each of my record in my form, and it should be created automatically.It should look like this:

    AB060106-1
    AB060106-2
    AB060107-1
    AB060107-2
    AB060107-3
    AB060108-1
    and so on.

    Idea is to have two or three letters at the front that never change, then 6 numbers that represent current date (year,month,day), and then incrementing numbers for that day.Whenever day changes, this last number starts from 1 up to 999 and next day it resets to 1 again.


    Any ideas where should i start from? Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Taking a fundamentalist viewpont if AB is never going to change - then its redundant - bin it, by all means display on reports and forms but don't stor eit internally......

    You need to consider what would happen if your organisation ever came close to breaking the 999 limit in any one day

    the datee value is easy enough use format to extract from the current date

    the actual process is fairly easy
    for perfomance reasons store the last number used (or next number available in a special table (away from the actual product data) - some systems have a configuration table, if so stroe it there, otherwise create a table

    when you want to get a new number the basic process is
    1) accquire an exclusive lock on the special table
    2) read the value
    3) increment it
    4) write the incremented value back
    5) release the lock
    6) use the value

    you could use a max() function on the existing table, but that means you are constantly hitting the main table with an exclusive read. You may get away with it with very few users, but I wouldn't want to place any cash on that. So you need to look up on recordset properties, how to secure a lock, and how to release it

    how you implement the number is up to you
    could be a single value as a primary key
    could be a composite key (OrderDate+orderNo)
    what ever way you do it you have an issue to make sure that you never change the order date after the record is changed
    another technique is to use an autonumber column as a system generated ID, and then derive your data according to what ever rules you need.
    irrespective of what technique you try you are goinjg to have to get down and dirty using some vba functions

    Incidentally what is the reason for the ABYYMMDD prefix, I'm always fascinated why users come up with such requirements - for whose benefit is it? what does th AB represent, do your users really want to type in 8 digits of crud

    some organisations are very leary about letting customers how much/little they do so an order number often gets masked. I have seen techniques to compress the year and month into 2 digits 2000-2026 A-Z, Jan-Dec A-L. I have also seen psuedo random numbers stuck on the front and the second the order was taken on the front just to make an order number look bigger than it truly is.

    One organisation I talked to said they needed the date prefix so they could easily group invoices / orders into a manual filing system ie each month had a unique physical file so they could quickly look at a customers order by month. I did eventually wean them off that approach by pointing out that they could do that online, and there would be no more arguments over who had this / last months file sitting on their desk but it took a while. So do you actually need this requirement or is it a legacy requirement (ie we've always done it that way......)
    HTH
    Last edited by healdem; 01-06-06 at 06:48.

  3. #3
    Join Date
    Oct 2005
    Posts
    87
    Quote Originally Posted by healdem
    Taking a fundamentalist viewpont if AB is never going to change - then its redundant - bin it, by all means display on reports and forms but don't stor eit internally......
    Yes, i think i'll have to create some sort of label or whatever.

    You need to consider what would happen if your organisation ever came close to breaking the 999 limit in any one day
    Such thing won't happen, ever. So its ok. I could have said it to be 999999 or smth.

    the datee value is easy enough use format to extract from the current date

    the actual process is fairly easy
    for perfomance reasons store the last number used (or next number available in a special table (away from the actual product data) - some systems have a configuration table, if so stroe it there, otherwise create a table

    when you want to get a new number the basic process is
    1) accquire an exclusive lock on the special table
    2) read the value
    3) increment it
    4) write the incremented value back
    5) release the lock
    6) use the value

    you could use a max() function on the existing table, but that means you are constantly hitting the main table with an exclusive read. You may get away with it with very few users, but I wouldn't want to place any cash on that. So you need to look up on recordset properties, how to secure a lock, and how to release it

    how you implement the number is up to you
    could be a single value as a primary key
    could be a composite key (OrderDate+orderNo)
    what ever way you do it you have an issue to make sure that you never change the order date after the record is changed
    another technique is to use an autonumber column as a system generated ID, and then derive your data according to what ever rules you need.
    irrespective of what technique you try you are goinjg to have to get down and dirty using some vba functions
    Incidentally what is the reason for the ABYYMMDD prefix, I'm always fascinated why users come up with such requirements - for whose benefit is it? what does th AB represent, do your users really want to type in 8 digits of crud
    The main idea is to NOT type manually those digits, and let access do it.

    Purpose is to have better understanding , when talking with client, which invoice he has.Each invoice must have own number, and it can't be just simple numbers starting from 1.Client shouldn't know how many invoices were made.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry, the main point is for access to generate those digits, disagree the main point is that you are coercing your users to type in those selfsame 9 digits ABYYMMDD- prior to viewing the order (yes Access can easily generate such garbage on data entry, but not on reviewing the orders - Access has been accused of many things over the years - psychic never, psycotic maybe.....

    so is what you really want then a unique number for each customer?

    your original request said you wanted to hardcode the first 8 digits ABYYMMDD, then have a 3 digit reference after it.

    if you want a unique ascending customer specific nuber then thats farily easy
    add a column to your customer table (next available order no, default value 1)

    the process is
    secure an exclusive lock on the customer table
    read the new colun
    increment the value
    write the incremented value back
    release the lock
    use the value

    if you are using a relatively new version of access that supports record level locking, then assuming that you used record level locking you could issue 2 SQL statements, the first increments the value, the second reads the value and subtract a from it - I wouldn't trust that approach, but it may work, and would be easier to implement for someone not too familiar with Access.

    you could use some form of hybridisation of the prefix to say a customer ID. I've often seen the first 4 characters of the compnay name followed by 2 or 3 digits eg YZF001

    in any event you stil need to bone up on recordsets, record locking etc - now way round it, and its a good thing to know in any event.
    Last edited by healdem; 01-06-06 at 07:22.

  5. #5
    Join Date
    Oct 2005
    Posts
    87
    Yeah, maybe i expressed myself wrongly. When i open my form, i want that access would create autoID (YZXXXX-N) and thats it.

    YZ - "label"
    060107 - current date
    N - incrementing number by 1, which resets to 1 if "date" digits change.


    Nothing more

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a simple masking operation, ie you don't want the customer to know how many orders have been placed (ie the organisation is scared or letting customers know how many / few inovices / orders there have been snce the last time they ordered. then Id suggest prefix with a 3 digit code
    eg AB9, where
    A=indicates the year eg year 2005-41 represented by A-Z & 0..9
    B=indicates the month Jan-Dec represented by A-L
    9=indicates a numeric digit
    add a suffix with is possibly the seconds of the date the order was captured
    use an autonumber field. formated the number of invoics you expect ovber the life of the system x a fudge factor (say 100 times the expected).

    so if you are expecting 1000 invoices over the life of the system you get 99999 order numbers

    Using the autonumber means you don't have to learn about record locking, or pulling off some wheeze to get the number, access just does it. the visitbility of the relationships is maintined within access. you just mangle the order number in a function


    Code:
    function MangleOrderNo(OrderNo as long,OrderDate as date) as string
    MangleOrderNo=encodeYear(OrderDate),EncodeMonth(Orderdate) & "3" & format(OrderNo,"00000") & seconds(OrderDate)
    end function
    Code:
    function UnMangleOrderNo(mangledOrderNo as string) as string
    UnMangledOrderNo=mid$(mangledOrderNo,4,5)
    'you could expand this to do some checking
    'ie check the order no exists, its prefix & suffix is consistent (ie you can derive the required values form orderdate)
    end function
    Code:
    function encodeYear(OrderDate as date) as string
    intYear=year(orderdate)
    'assume 2005 is year 0
    intyear=intyear-2005+65 '65 =the ascii value of "A"
    encodeyear=ASC(intyear)
    end function
    Code:
    function encodeMonth(OrderDate) as string
    ...repeat the same basic process as above except you are only using A-L
    end function
    so in every report and screen you call MangleorderNo([OrderNo],[OrderDate]
    when the order number comes in you extract the true intrnal number as Unmnagle([typedorderno]

    This is all air code - not tested, not even anywhere near a windows box with access on today, so its bound to be a bit flaky, but you should get the gist of it...

    you could modifyt the encode function by never adding the leading zeros, or adding a minimum of, say 2 digits, and removing the leading 3 characters and the trailing 2 characters in unmangle

    so an order capturd on the 21 march 2005 @14:23:15 with an autonumber of 12 would be
    AC50001215, or AC50125 if you use format (number,"000")

    so an order capturd on the 10 Jan 2006 @09:45:03 with an autonumber of 13 would be
    BA50001303, or AC501303 if you use format (number,"000")

    The main areas where this approach falls down is if you have a requirment to show contiguous numbers, it you cannot have missing numbers (say if an order is delete, or for some reason satrted but never completed. If that is your requirement then you have togo down the route of getting a number when required.

    Note you also need to never ever allow the date of the order to change - other wise you have a real problem.


    HTH
    Last edited by healdem; 01-06-06 at 08:42.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •