Solved

How find first unused number in a table?

Posted on 2004-08-02
19
646 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
ID: 11698549
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
ID: 11698950
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
ID: 11700471
do you mean your table 'could have' 9986 records but the missing numbers are (say) 11235,15446, 13449

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 3

Expert Comment

by:realrael
ID: 11700708
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
ID: 11700928
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
ID: 11702494
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
ID: 11703812
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
ID: 11705181
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
ID: 11705463
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
 
LVL 44

Expert Comment

by:GRayL
ID: 11706044
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
ID: 11706188
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
ID: 11706631
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
ID: 11706638
Sorry, that should read:

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

Expert Comment

by:davidW
ID: 11710990
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
ID: 11711717

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
ID: 11716293
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
ID: 11723685

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
ID: 11725206
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
ID: 11726477

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

773 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