Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005

    Exclamation Unanswered: Incrementing the primary key

    I have a 'text' valued primary key as an ID for a field. How do I get it to display the next value in the sequence, every time the form is loaded.

    For instance I have an id number A001, I have added this to the table the next time I open the form up, need it to read A002 as default, so that the sequence integrity is maintained.

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    Well, at what point are you going to increment the alphabetic value? After A999? Will you then go to B001? And what about after Z999?

    I can't recommend doing this. Why do you need such a surrogate key? By definition a surrogate key serves no purpose but to uniquely identify a record, so there is no justification for creating odd rules about it's sequence?

    Can it be done? Sure, a lot of things that should not be done in a database CAN be done. I guess I'd create an auto-incrementing integer field, divide it by 1000 and add the result to the ASCII value of the letter "A", and finish by tacking on the result of your Auto-increment field modulus 1000. There's your key field? Is it worth the trouble?
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Jan 2005
    I need it for invoicing. The numbering system has already been registered. The actual format is A0001, the numbering limitations are sufficient for this project as it will cease at A1560.

    I have no idea how to implement what you have sugggested, is there an easier way

  4. #4
    Join Date
    Nov 2003
    Sussex, England


    Easier way ;(

    Use the OnLoad event of the form:

    Retrieve the last key value (not easy because you cant get the Max value: suggestion, create an Autonumber as a field that is not used for anything else except the priomary key).

    Increment the value of the key.

    Test for the value is 999 (or whatever the maximum is). If that is the case reset the valu to 000. If you don'w want to manipulate the ASCII key value use a SELECT ... CASE routine to increment the letters, e.g.

    CASE "A"
    LetterOut ="B"
    CASE "B"
    LetterOut ="C"

    CASE "Z"
    LetterOut ="A"


    This involves writing a lot of code, and you'll have to work a lot out for yourself, but it is workable.

    Good luck.

  5. #5
    Join Date
    Jul 2004
    Southampton, UK

    An easier way ?


    I agree with Blindman and would avoid going down the route of generating your own code. However, you could have a standard auto-number field as your primary key, then use a query to make the PK look like the code you want in any output.

    So 1,2,3 (numeric) would look like A001,A002,A003 (text).

    The following calc will do the conversion:

    myAcode: "A"+String(4-Len(CStr([myAutoID])),"0") & CStr([myAutoID])

    So you don't actually store the A code, just generate it when needed from the autonumber field !

    Obviously this solution is limited to 999 records which I guess suits you needs.



  6. #6
    Join Date
    Nov 2004
    Bangor, ME USA
    If I understand correctly I may have a file for you to check out that someone else has done. If what you seek is A0000 - A9999 then B0000 - B9999 etc to Z9999 after which would be over 2.6M records. If I am right in this you can decide then how to proceed after Z9999. The file can be found at:'Increment%20Alpha%20 Field.mdb'

    I dont need a key like this but it may be just what you are looking for


Posting Permissions

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