How find first unused number in a table?

Hi SteveL13,


         Dim dbConn As Database
        Dim Rst As DAO.Recordset
        Set dbConn = CurrentDb
           
        Dim strsql

        strSQL = "SELECT NUM FROM MYTABLE WHERE MYNUM NOT IN (SELECT MYNUM + 1 FROM MYTABLE)  ORDER BY MYNUM "

        Set Rst = dbConn.OpenRecordset(strSQL)
   
        If Not Rst.EOF Then
            MsgBox Rst![MYNUM]
        End If
       
        Rst.Close
        dbConn.Close
        Set dbConn = Nothing
        Set Rst = Nothing

Hope this helps

Change:
strSQL = "SELECT NUM FROM MYTABLE WHERE MYNUM NOT IN (SELECT MYNUM + 1 FROM MYTABLE)  ORDER BY MYNUM "

to:

strSQL = "SELECT MYNUM FROM MYTABLE WHERE MYNUM NOT IN (SELECT MYNUM + 1 FROM MYTABLE)  ORDER BY MYNUM;"

do you mean your table 'could have' 9986 records but the missing numbers are (say) 11235,15446, 13449

i think you're using the number to index your records. if that's your only purpose, you can have ms access auto-generate that number for you, so that you don't have to program a single line of code. in a snap.

- rael

i think we need to know the purpose of the table

if it is just an indexing system read 'realrael'

if the numbers cannot be changed you might try:-

'''''' using '97
dim db as database
set db = currentdb()
dim rs as recordset
set rs = db.openrecordset("Select [NumberField] from [TableName] order by [NumberField]",DB_OPEN_DYNASET)

dim i as long

rs.movefirst
set i = rs("NumberField") ' whatever the first record is
do while not rs.eof
  if rs("NumberField") = i
    rs.movenext
    i = i + 1
  else
    msgbox "record " & i & " is missing"
    exit do
  loop

rs.close

' rough as guts but will work

To all:

Regarding:
"do you mean your table 'could have' 9986 records but the missing numbers are (say) 11235,15446, 13449"
No.. The table could have say 9000 possible records but missing are entries for 1234, 5623, 8976, and 5903 just for example.

Regarding:
"i think you're using the number to index your records. if that's your only purpose, you can have ms access auto-generate that number for you, so that you don't have to program a single line of code. in a snap."
The number is the key index in the table, but, I have no control over how its used.  The user(s) enter the number they wish to use, therefore, numbers can be missed which is why I need to identify the missing ones.  If it was my tabel, I would have auto numbered.

To DavidW:

My code (actually your code) follows...  but I'm getting an error... "Compile Error: Object Required" at the line that begins... "Set i..."

What am I doing wrong?  Am using Access 2003.

Dim db As Database
Set db = CurrentDb()
Dim rs As Recordset
Set rs = db.OpenRecordset("Select [JobN] from [OpenJob] order by [JobN]", DB_OPEN_DYNASET)

Dim i As Long

rs.MoveFirst
Set i = rs("JobN") = 100000 ' whatever the first record is
Do While Not rs.EOF
  if rs("JobN") = i
    rs.MoveNext
    i = i + 1
  Else
    MsgBox "Record " & i & " is Missing.  Please use it."
    Exit Do
  Loop

rs.Close

Set i = rs("JobN") = 100000 ' whatever the first record is - This will not work.

i = rs("JobN") 'will set i to the value of the field JobN

Maybe we can start over because nothing has worked so far.  Here's the situation...

The table I am working with has a key field named JobN.  This is not an auto-number field.  The table is named OpenJob.  Users enter records into this table and "pick" a number for the JobN at their discretion.  For this reason numbers get missed and I need to identify those missing numbers, or at least identify the first missing number.  I also have to be able to limit the entire range of JobN possibilities, for example from 100000 to 199999.  So although many of the possibilities have been used and we have records to show that, there are several numbers in the entire range that have not been used yet.

How can I display the first UNUSED number within the given range, that has not been used yet in a message box?

--Steve

Steve: I think you have to generate a single field - JobN in table JobNums datatype double. Set the value of JobN to your lower limit - 100000. Make the datatype now autonumber. Add rows until you reach the upper limit - 199999. Now do a join

Select Bottom 10 JobNums.JobN from Jobs left join UsedJobs on JobNums.JobN<>UsedJobs.JobN;

The Query could be the record source for a combobox and once selected, the onclick event would update UsedJobs with the cboJobN

I cannot change the structure of the table.  Its actually a linked table to a SQL database that I cannot touch.

I did not say change the structure of OpenJob. I think you have to create a new Access table - JobNums which contains a single autoincrement field ranging in value from 100000 to 199999. Then join JobNums with OpenJob.

Once you get the query working, you can reference the first record by using the DLookUp function in the MsgBox

val = msgbxo("The first available job number is " & DLookUp("JobN","QryName"))

Sorry, that should read:

val = msgbox("The first available job number is " & DLookUp("JobN","QryName"))

i am fairly certain that the logic of the routine i sent you is valid.

as GRayl said

Set i = rs("JobN") = 100000 ' whatever the first record is - This will not work.

i = rs("JobN") 'will set i to the value of the field JobN

( sorry about that )

and maybe
Dim rs As Recordset
needs to be
Dim rs As DAO.Recordset

i only have '97

here's a function. just replace the Data Source with your own.

call by:

    MsgBox CheckFreeId(100000, 199999)

- rael

-----

Private Function CheckFreeId(low As Double, high As Double) As Double
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim IsFound As Boolean
   
    Set cn = New ADODB.Connection
    cn.CursorLocation = adUseClient
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<mdb file>;Persist Security Info=False"
   
    Set rs = New ADODB.Recordset
    rs.Open "select JobN from OpenJob where JobN >= " & low & " and JobN <= " & high, cn
   
    rs.MoveFirst
    CheckFreeId = rs!JobN
    IsFound = False
   
    If low < rs!JobN Then
        IsFound = True
        CheckFreeId = low
    Else
        Do While Not rs.EOF
            rs.MoveNext
            If Not rs.EOF Then
                If rs!JobN <> CheckFreeId + 1 Then
                    IsFound = True
                    CheckFreeId = CheckFreeId + 1
                    Exit Do
                End If
                CheckFreeId = rs!JobN
            End If
        Loop
    End If
   
    Set rs = Nothing
    Set cn = Nothing
   
    If IsFound Then
        'CheckFreeId = CheckFreeId
    Else
        CheckFreeId = -1
    End If
End Function

Rael,

I'm sorry to sound confused... but,

I assume I create a module and paste all your code starting with "Private Function CheckFreeId..." through "End Function" into it.  Then, what do I do with the copy that reads... "MsgBox CheckFreeId(100000, 199999)"?

--Steve

steve, since you have that function pasted in a module already, you can call that function in whatever way that suits you. if you want to run it through a macro, do the following:

1. make the private function public.
2. replace the last if-else block with this:
    If IsFound Then
        MsgBox CheckFreeId
    Else
        MsgBox "No free ID found within range."
    End If
3. create a new macro.
    a. select the RunCode action.
    b. in the Function Name box, type in "CheckFreeId(100000, 199999)".
    c. close and save your macro.
    d. run the macro.

- rael

rael,

I'm sorry to be a pest, I think we're getting close... but I suspect there is something wrong with the following line...

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<mdb file>;Persist Security Info=False"

The linked table is actually a link to a SQL database table.  Should that line read differently?  If so, do you know what to change it to?

--Steve