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
Solved

How find first unused number in a table?

Posted on 2004-08-02
19
652 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

840 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