Code to find unused number in a table, and assign as primary key for new record.

[IncidentalProgrammer]

Hi, all!

I've been trying to look for an answer to this on Google, but I'm just not getting results. It's kind of an odd one.

There's a portion of the database I'm building that's dedicated to tracking some specialty insurance policies. As we are almost fully in charge of these particular policies, we're also generating the policy numbers (whereas the rest of the policies we deal with come from the insurance companies with policy numbers already). I worked up a little form that generates a policy number in the correct format, which includes a unique 3-digit number that the policy carries forward as it renews over the years. In order to make that number unique at creation, but able to be repeated for renewals, I had to make a table to store that number when it's created, and from then on, the system stores the full policy number in the same table as the rest of the policies.

To get the unique 3 digit number, I've been using this as the Default Value for the appropriate field on my form:

=Format(DMax("UwID","tblUwNew")+1,"000")

It's finding the highest number on that table, adding 1, and using THAT number, formatted as 3 digits. It is a text type field.

This was all find and dandy, until my boss told me that we'll also be back-loading some existing policies...so I'm going to be putting things in there already with numbers in the hundreds, and skipping around a bit. I just tested it, and of course, it finds that HIGHEST number, and goes from there, skipping possibly hundreds of numbers in the process.

Does anyone know of a better way to do this, that would avoid skipping numbers? So that if I have 001-100 already, and a user enters 203, it won't then start the next one at 204?

[orange]

What is the issue with gaps in the numbers?

[IncidentalProgrammer]

Well, it'd be a while in the future before it became a real problem. With it having to be 3 digits, the problem will occur when we reach 999, and then have to go back and dig for unused numbers. I'm trying to plan ahead now to save myself (or whomever I turn this over to) a headache.

[orange]

I created a small routine that might help. The "ID" field that you are using (looking for gaps) can not be autonumber.
Once an autonumber is deleted, I think it is gone.

Anyway I did test this with a couple of tables, and it seems to work. There is a function and a test routine.

'---------------------------------------------------------------------------------------
' Procedure : MissingRecNos
' Author    : mellon
' Date      : 21/03/2015
' Purpose   : To identify any missing numbers in the "id" field in a table.
'This function finds the highest used number; and the number of records
'in the table. If the numbers are the same, then there are no gaps.
'If the numbers are different, then some gap(s) my exist.
'This routine uses debug.print to write info to the immediate window.
'
' *** This will NOT work if the "ID" field is an autonumber ***
' ***                                                       ***
'**************************************************************
'---------------------------------------------------------------------------------------
' Parameters:
'   tablename---- is the name of the table you are working with
'   recIdField--- is the identifier field in the table
'---------------------------------------------------------------------------------------
Function MissingRecNos(tableName As String, recIdField As String) As String
          Dim sql As String
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim mmax As Long, i As Long


          Dim NoRec As Long
10        On Error GoTo MissingRecNos_Error

20        Set db = DBEngine(0)(0)
30        Set rs = db.OpenRecordset(tableName)
          'get highest used number
40        rs.MoveLast
50        mmax = rs.Fields(recIdField)

          'How many records in the table
60        NoRec = DCount(recIdField, tableName)

70        Debug.Print mmax - NoRec & " unused records exist"

          'what are the unused numbers

80        For i = 1 To mmax
              ' If DCount(recIdField, tableName, recIdField = i) = 0 Then
              '    Debug.Print i
90            sql = "SELECT Count(" & tableName & "." & recIdField & ") AS CountX FROM " & tableName _
                    & " WHERE " & recIdField & " = " & i
100           Set rs = db.OpenRecordset(sql)
110           If rs!Countx = 0 Then
120               MissingRecNos = MissingRecNos & recIdField & "  " & i & "  is available" & vbCrLf
130           End If


140       Next i

150       On Error GoTo 0
160       Exit Function

MissingRecNos_Error:

170       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MissingRecNos of Module Module4"
End Function

Sub testMissing()

    Dim tbl As String
    Dim fld As String
    tbl = "employeeTask"    ' "authors" '<<<your table name
    fld = "MyID"            ' "author_id"   '<<<your field name
    Debug.Print MissingRecNos(tbl, fld)
End Sub

Give it a try. You can move the output to where ever it makes sense to you.

NOTE: For anyone following this thread.

I tried to use a Dcount() but constantly got a type mismatch, after several attempts I swithed to
the sql and open recordset approach.

[IncidentalProgrammer]

Thank you so much!!!

Please pardon my ignorance though. I'm still pretty new to programming (I haven't even had a chance to finish the book yet), and I am doing something wrong. I put it into the class module for the appropriate form, and I've tried calling it from the Open event and Load event. I keep getting a "Compile Error: Argument not optional". This is no doubt something pretty hair-brained on my part, but what do you think I might be doing to make it angry?

[orange]

No, it doesn't go in a class module. It is a standard module.

Put the function and the test routine in a standard module.
In the test routine, look at the comments, and put the table name and identifying field names into the test routine.
Then run the test routine.