Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    16

    Arrow Unanswered: generate new unique sequential number, table already contains non-sequential numbers

    Hello,
    I would like to click a button on a form to generate the next sequential available unique ID number. This ID number only gets assigned to certain records, not all. Unfortunatly the existing numbers are not sequential and cannot be reset. The ID number is only 3 digits, they start with 001, 002 then jumps to 110, 111 .. then to 217, then 683 .. all over the place. I would just like to click the button and have it generate "003" .. then "004".. so on and so forth until it got to 110, then skips to next avail of "112" .. etc. Not sure what to do here.
    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Said with other words, you want to fill the gaps.

    1. Create a "tally" table, i.e. a table that contains one column with only sequential number in it and an "unmatched" query. In the c_SQLQuery constant, replace Tbl_Samples and Tbl_Samples.ID by the actual names in your project. Run this code only once:
    Code:
    Sub RunOnce()
    
        Const c_SQLTable As String = "CREATE TABLE Tbl_Tally ( [Number] INTEGER NOT NULL CONSTRAINT pk_Tbl_Tally PRIMARY KEY);"
        Const c_SQLQuery As String = "SELECT Tbl_Tally.Number " & _
                                     "FROM Tbl_Tally LEFT JOIN Tbl_Samples ON Tbl_Tally.Number = Tbl_Samples.ID " & _
                                     "WHERE Tbl_Samples.ID Is Null;"
        
        Dim qdf As DAO.QueryDef
        Dim i As Long
        
        CurrentDb.Execute C_SQLTable, dbFailOnError
        For i = 1 To 999
            CurrentDb.Execute Replace("INSERT INTO Tbl_Tally ( [Number] ) VALUES ( @V );", "@V", i), dbFailOnError
        Next i
        Set qdf = CurrentDb.CreateQueryDef("qry_No_Match")
        qdf.SQL = C_SQLQuery
        Set qdf = Nothing
        
    End Sub
    2. This function will always return the lower "available" number until you reach 999:
    Code:
    Function GetNextAvailableNumber() As Variant
                                   
        GetNextAvailableNumber = DMin("Number", "qry_No_Match")
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    16
    Hey Sinndho,

    Thank you so much for your help, sorry for the delay, just now had time to try this. Functionally it works great .. the only thing is that my field is text so i can include the leading zeros (001, 002, 003, etc) in the ID #. I did convert to number to test it, and it works great, but now the ID #s are 1, 2 , 3 instead of 001, 002, 003... Any thoughts on how to compensate for this?

    Thank you

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    GetNextAvailableNumber = Format(Nz(DMin("Number", "qry_No_Match"),0),"000")
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    store a number as a number
    format it however the heck you fancy.
    BUT DON'T store it as a a string as 00x, but as x

    a refinement on Sinndho's sugegstion
    you've got your tally table

    modify the function so it always returns the next available number

    Code:
    Function GetNextAvailableNumber() As Variant
      GetNextAvailableNumber = DMin("Number", "qry_No_Match")
      if insull(GetNextAvailableNumber) then ' we have run out of 'missing' numbers, so get a new one
        GetNextAvailableNumber = dmax("mycolumn","Tbl_Samples")
        if isnull(GetNextAvailableNumber) then 'we didn't find any value for the specified column
          GetNextAvailableNumber = 1
        end if
      endif
    End Function
    replace my column with whatever your id column is

    as ever its air code, uintested, made up ont he fly and will almost certainly need refinign / debugging
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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