Custom numbering

Results 1 to 2 of 2

Thread: Custom numbering

  1. #1
    Join Date
    Dec 1969

    Default Custom numbering

    Hi,, I hope someone can help :)<BR><BR>I&#039;m attempting to create the following rather than using the autonumber system:<BR><BR>Table with a number of fields the main two being CustomerID and CustName.<BR><BR>When entering a new record in a form I&#039;ve made the CustomerID disabled and want to generate a unique ID (as this is the primary key) by taking the Name (on exit) and using left(field,3) to create the first section. I then want to query the table to count how many existing IDs have the same first three letters. Then place the ID in the field with the first 3 letters from the name and the next consecutive number from the query ie. JAM00015.<BR><BR>Is this possible?

  2. #2
    Join Date
    Dec 1969

    Default RE: Custom numbering

    First off, I would still use an AutoNumber/Identity field for your Primary Key (and use it as the Foreign Key). I&#039;d only use this autogenerated field for display purposes (and maybe folder names if you have client-specific folders).<BR><BR>But, it would be a matter of doing a SELECT TOP 1 from the table where the LEFT 3 letters match the entered name; ordered by DESC on the field.<BR><BR>Then, if do NOT get any records, then you know to use the #1. If you DID get records, then right the # from the characters to the RIGHT of the 3 letter prefix. Take this number, convert it to a DOUBLE and add 1.<BR><BR>Now, the number you get from the previous instructions. Convert that to a 0-padded number (easily done by: strZeroPadded = Right("00000" & dblNextNumber).<BR><BR>At that point you have your newly generated ID.<BR><BR>Last point to this .. there is a remote possibility (on a high-traffic site) that you will end up with 2 IDs that are the same due to concurrent users. But, if you have a UNIQUE constraint on the field, you should be OK.<BR><BR>You could actually do a check at the end of the ID logic that checks for the existance of this ID. If it doesn&#039;t exist, it uses it. If it DOES exist, then you figure the next one.

Posting Permissions

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