Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Location
    Minneapolis
    Posts
    21

    Unanswered: Get next available number

    I have a table that runs from 04-0001 th 04-470. Will eventually be more but everytime I add a record I need to grab the next available number. Field is indexed no duplicates. Is there a simple way to make the form grab the next available number? Records are entered thru a form.

  2. #2
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133

    Re: Get next available number

    Originally posted by Chopper4116
    I have a table that runs from 04-0001 th 04-470. Will eventually be more but everytime I add a record I need to grab the next available number. Field is indexed no duplicates. Is there a simple way to make the form grab the next available number? Records are entered thru a form.
    You can make a function getting the next number for you. It can look something like this :

    '------------------------------------------------------------
    Public Function MakeNewNr() As String
    '------------------------------------------------------------

    'get highest existing nr
    MakeNewNr = DMax("ID", "MyTable")

    'create integer and raise 1
    MakeNewNr = Mid(MakeNewNr, 4) + 1

    'create new nr
    MakeNewNr = "04-" & Format(MakeNewNr, "0000")

    End Function


    If you store this function in a module, you can set the default value of the field on your form to :
    =MakeNewNr()
    the field will then automatically give you the next nr for each new record.

  3. #3
    Join Date
    Dec 2003
    Location
    Minneapolis
    Posts
    21
    Thanx for the help. Not good with code so I'm going to have someone help me get it entered... will let you know how it goes...

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ....also

    if you are in a multi-user situation, two or more users might by chance do a DMAX() call at the same time ... each gets the same number ...each creates the same "unique ID" ...booooom!

    sorry i don't have a decent fix for this.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by izyrider
    ....also

    if you are in a multi-user situation, two or more users might by chance do a DMAX() call at the same time ... each gets the same number ...each creates the same "unique ID" ...booooom!

    sorry i don't have a decent fix for this.

    izy
    Generally you'll have to go with the autonumber way or handle the unique numbers yourself (which is what I do ...) Basically: Create a table (call it what you will) with a column for each unique number to be generated and make a function/method to Get the next number and increment ... This table should have 1 and only 1 row in it.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the "intellectually despised" autonumber handles all this crap internally

    ...there are (pace M Owen) ways to do a "manual" "auto"-number (and you must on some non-A backends), but if you are sitting in A, consider a minor table redesign to incorporate the (free) autonumber facility.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Get next available number

    Hey guys I have the same problem. I am working on a database created by someone else who is no longer available, naturally. What he created was excellent but I can't figure it out. Here goes, we use Job numbers i.e. 40125, and that is also part of the P.O. number when we order. But what happens is the thing that generates the P.O.number adds 2-3 digits on the end like...40125-22 or 39978-265. The trick it, when the Job number changes the P.O.number generator knows the last number issued for that particular Job number. See what I mean? Now how can I duplicate that process without being a rocket scientist. Keep in mind I am not a programmer per se. Each Job number has it's own series of incremented numbers for it. Autonumber for me only added the -22 suffix in successive order.. meaning if 40125 has -22 for the P.O. number, if I create a P.O. for 39978 it will give it -23. Not Good. HELP!!!
    Bud

  8. #8
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Bud,

    you could try the following function :

    '------------------------------------------------------------
    Public Function MakeJobNr(intOrder As Long) As String
    '------------------------------------------------------------

    'get highest existing nr
    MakeJobNr = DMax("Clng(Mid(JobNr, InStr(JobNr, '-') + 1))", "MyTable", "Left(JobNr,5)='" & intOrder & "'")

    'raise 1
    MakeJobNr = MakeJobNr + 1

    'create new nr
    MakeJobNr = intOrder & "-" & MakeJobNr

    End Function


    As this function requires the OrderNr as argument, you should use this function only when the ordernr is known.

    Try it out and see what you can do with it.

Posting Permissions

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