Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    56

    Unanswered: Generate sequential numbers bigger than just 1?

    hi,

    i want a field in my database that generates a sequential Unique number, preferably starting from something like 20001 n then increase, how would i make this possible?

    thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    create your own routine
    that looks for the current largest number (use dmax function)
    grabs that number
    writes the data

    in a multi user db, where there is a risk that more than one person could be entering data at the same time, then you need to develop a stratgey to handle such contentions.
    usually you do this by securing a lock on the db, betweent he time you find the highest value, and the time you write the new value back
    OR
    use a brute force approach, grrab the value, write the data, and trap for a duplicate row insert failuyre , then add to the number and try again
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2011
    Posts
    56

    sounds brilliant

    it sounds great, however, im still a retard and would appreaciate if you could explain the process, and elaborate on any coding i may need?

    thanks man

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggest you create a user function
    then find the current highest value using the dmax function
    then I'd add one to that value

    in my calling form I'd probably place the code in the forms before update event
    I would only call that function if the current value (or possibly text of this serialno control) was null using the isnull function

    I'd put some error trapping code in the form (dont' know which event would trigger the problem) which examined the attempt to write the new value and if there was an error cause by duplicate I'd add one ot the value and repeat the process untill the error cleared

    OR
    in place of the error trap approach I'd secure a lock on the table from the moment the dmax was called until such time as the record was written

    Im guessing there almost certainly some code for this in the code bank or findable through google. but like you I can't really be bothered to look for myself.
    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
  •