Solved

How find first unused number in a table?

Posted on 2004-08-02
19
675 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

691 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