Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011

    Unanswered: Autonumbered Primary Keys

    How can I make a primary key in Access to follow a certain pattern? For example: I have a suppliers table and the primary key column is set to autonumber, which is just incrementing from 001 and up. I would like to have it set to something like SUP002, SUP002, SUP003, and so on.

    I'm just learning Access and would really like to do something a little more complicated than that, but it would be a good start if I knew where to begin modifying this setting.

    Is it difficult to do that?


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    yes it can be done. but ask yourself does it need to be done?
    if these numbers are all suppliers, and all prefixed by SUP, then I'd suggest you run with an autonumber solution, and if you must prefix with SUP fake it.
    each time you display the autonumber add 'SUP' to it using a fucntion such as 'AddPrefix' below, to remove the prefix use a function such as stripprefix below. also consider if you actually need 'SUP' by definition the vlaue in that column must be a SUPplier. a vlaue in the customer column must be a CUStomer. its perfectly possible that a CUStomer may also be a SUPplier and you wouldn't want to have two items identifying the same organisation. you could have one table with organisations in and a couple of boolean flags such as IsCustomer and IsSupplier, or an intersection table qualifying what type of interaction you have with this organisation
    table: organisation
    ID PK
    ...stuff relevant to the organisation (address, switchboard, name)
    table: InteractionTypes
    Type  PK
    table: OrganisationsInteractions
    OrganisationID }
    IteractionType } PK
    However this does mean that you are using an Autonumber value and giving it a meaning outside the system. thats not a problem unless someone applies a different meaning to that value (a typical different meaning is where there is a demand to have contiguous numbers (eg Invoice numbers, GRN's, Delivernotes and so on)). if someone else relies on a different meaning then you need to make certain that an autonumber is OK. Autonumber bvlaues are about making a unique value, you don't know if the autonumber is contiguous, in sequence or whatever. its down to the db implementation to allocate autonumbers and its quite possible that values may be missing, out of time sequence and so on

    if you want to write you own funtion to create a sequential numbering system you need to take the following steps

    lock the table
    find the next number
    write your new number (write a row to the db)
    release the lock on the table
    it needs to be carefully designed assuming its a multi user DB, so other users are locked out of the db for the minimum amount of time

    you can dispense with the locks if you go for an ugly forced write solution. put the write inside a code block that attemtps a writer, if it fails for a 'duplicate' record key increment your counter and attempt to write again

    function AddPrefix(Prefix as string, Number as Long, NoChars as integer) as string
      'adds a specified prefix to a number and returns the combined string
      'eg AddPrefix ("XYZ", 72, 8) returns XYZ00072
      'Number is the numeric value to be appended
      'Prefix is the character symbol(s) to be prefixed
      'NoChars is the number of character the finished string should be
      Dim NoDigits = NoChars-len(Prefix) 'should give us the number of digits in the number
      if NoDigits <=0 then NoDigits = 6 ' if the value is negative it means the resultant field will be too small
      'for my purposes I want a minimum number size of 6 digits
      AddPrefix = Prefix & format(Number, buildmask(NoDigits))
    end function
    function BuildMask(NoDigits as integer) as string
      'builds a format mask with zeros
      'eg if NoDigits is 6 the returned format mask will be '000000'
      'because I don't trust a supplied value
      if NoDigits < 6 then NoDigits = 6
      while NoDigits>0
        BuildMask = BuildMask & "0"
        NoDigits = NoDigits -1
    end function
    function StripPrefix(Value as string) as long
    'to cater for any value run through the supplied Value and remove leadign characters untill the remaining is a number. note this function will fail if there are trailing non numeric values
    'eg ABC000045 will return 45, 1089089 will return 1089089, FGZ09809A will return 0
      StripPrefix = 0 ' set up our default return value
      while len(Value) >0 and isnumeric(Value) = false
        Value = mid(value,2) 'strip off the leading character
      if len(value)>=1 then StripPrefix = Value
    end function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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