Solved

How find first unused number in a table?

Posted on 2004-08-02
19
616 Views
Last Modified: 2008-02-26
I have a table with a number field.  This table has to be limited to a range of possibilities, say from 10,000 to 19,999.  Most of the available numbers have been used but there are missing records since a number can get missed from time to time.

How can I identify the first missing number and present it as a Message Box?

--Steve
0
Comment
Question by:SteveL13
  • 6
  • 5
  • 4
  • +2
19 Comments
 
LVL 2

Expert Comment

by:deevallabh
Comment Utility
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
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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;"
0
 
LVL 4

Expert Comment

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

0
 
LVL 3

Expert Comment

by:realrael
Comment Utility
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
0
 
LVL 4

Expert Comment

by:davidW
Comment Utility
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




0
 

Author Comment

by:SteveL13
Comment Utility
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.



0
 

Author Comment

by:SteveL13
Comment Utility
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
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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


0
 

Author Comment

by:SteveL13
Comment Utility
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

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
0
 

Author Comment

by:SteveL13
Comment Utility
I cannot change the structure of the table.  Its actually a linked table to a SQL database that I cannot touch.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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"))
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Sorry, that should read:

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

Expert Comment

by:davidW
Comment Utility
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



0
 
LVL 3

Expert Comment

by:realrael
Comment Utility

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
0
 

Author Comment

by:SteveL13
Comment Utility
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
0
 
LVL 3

Expert Comment

by:realrael
Comment Utility

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
0
 

Author Comment

by:SteveL13
Comment Utility
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

0
 
LVL 3

Accepted Solution

by:
realrael earned 125 total points
Comment Utility

oh, in that case you have to replace the whole string with the correct sql connection parameters. visit this page for details.

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer

- rael
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now