Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    106

    Unanswered: Creating a custom primary key

    I want to create a primary key that will concatenate current year and a number series 100..200..300 (each user is assigned a number series). I'm already keeping track of which user is entering information but I'm not sure how to update the number in the series to the next in line from 100 to 101 and so on... Anyone have any ideas?

  2. #2
    Join Date
    Feb 2004
    Posts
    106
    anyone???

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Creating a custom primary key

    Originally posted by Yildirim
    I want to create a primary key that will concatenate current year and a number series 100..200..300 (each user is assigned a number series). I'm already keeping track of which user is entering information but I'm not sure how to update the number in the series to the next in line from 100 to 101 and so on... Anyone have any ideas?
    How are you going to keep track of the LAST number used? Let alone getting the next one in the sequence ...

  4. #4
    Join Date
    Feb 2004
    Posts
    106
    How are you going to keep track of the LAST number used? Let alone getting the next one in the sequence ...
    Good point. I don't know. I was hoping you guys have done something similar in the past.

  5. #5
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Depends on whether or not you want this to be sequential or random.

    Presumably you know the user when they start using your form, you can translate this and do a lookup of the last three digits that begin with their sequence number. For Example, if pd3rmpo uses sequences of the 400 series, when pd3rmpo uses the form, search the last three digits of your key field that has todays date in the first 6 or 8 and find the max.

    I would suggest against putting meaning into your primary key, esp if you are keeping track of this information in a separate field.

    If it's random, the only way I've been able to do this is through arrays, random function, and mod to pick something between 0-9, A-Z, then check my combination against another tracking table of generated keys.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  6. #6
    Join Date
    Feb 2004
    Posts
    106
    Sounds too complicated. It would be nice to put more meaning into a primary key but it's not worth the effort. Thanks for your help.

  7. #7
    Join Date
    Feb 2004
    Location
    AZ
    Posts
    12

    Re: Creating a custom primary key

    Originally posted by Yildirim
    I want to create a primary key that will concatenate current year and a number series 100..200..300 (each user is assigned a number series). I'm already keeping track of which user is entering information but I'm not sure how to update the number in the series to the next in line from 100 to 101 and so on... Anyone have any ideas?

    How something like this..

    Create a new table called ztblPrimaryKeyLookieUppie with following fields:

    UserName (foriegn key)
    PKYear char(4) (this will hold the year)
    PKNextNumber Integer (This will be the next number for this person for that particular year)

    Create a function:

    public function GetPKForSomeTable (byval strEmployee as string, byval intNumberToAdd as integer)as string

    'IntNumberToAdd is the series for that particular person
    'Example code in DAO

    dim strSQL as string
    dim rstCurrent as dao.recordset
    dim dbCurrent as dao.database
    dim strCurrentYear as string

    strCurrentYEar = year(date)

    'Create recordset to get the next number
    strsql = "SELECT PKNextNumber, PKYear, UserName FROM ztblPrimaryKeyLookieUppie WHERE PKYear = '" & strCurrentYear & "' and UserName='" & pstrEmployee & "';"
    set dbcurrent = currentdb
    set rstcurrent = currentdb.openrecordset(strsql)

    'if there's no record for this person for current year, then create one

    if rstcurrent.eof then
    rstcurrent.addnew
    rstcurrent!PKNextNumber=intNumberToAdd
    rstcurrent!PKYear=strCurrentYear
    rstCurrent!UserName=strEmployee
    rstcurrent.update
    GetPKForSomeTable = intNumberToAdd
    else
    GetPKForSomeTable = rstCurrent!PKNextNumber
    'Now increment number
    rstCurrent.edit
    rstcurrent!PKNextNumber=rstcurrent!PKNextNumber+1
    rstCurrent.update

    set rstCurrent =nothing
    set dbCurrent = nothing

    end function

    I think that should do it, just call this function when you want the number. You might have to use the cInt or cStr function when setting the value, not really sure. Also, keep in mind this will break if people "roll over" into the next series. (i.e. BOB is serieis 300 and he adds 100 records so his next number is 400, which runs into MARYS series of 400).

  8. #8
    Join Date
    Feb 2004
    Posts
    106
    Thanks jthammond

    This is a unique type of event that should have less than 100 events per user so the series should not break. I'll try this code and see if it works.

Posting Permissions

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