Find first unused record number, above control number

[nickvans]

Hi all,

I have a table called tblRecords that has "DashNum" [type: number] as its primary key. The lowest value of this table is 116 and the highest value is 269, though there are some missing values. It is expected that records will be removed in the future, leaving more missing values. I would like to have a text box have as its default value the first unused entry in this table.

I found a way to do this, but its not very efficient and requires an otherwise useless secondary table. I created a table called "DashNumBas is" with two columns: "DashNum" and "Used". "DashNum" has values starting at 116 and going to 9999, and used is a yes/no field. In order to find the first unused entry I have to run an update query which sets DashNumBasis!Us ed to "yes" for any record found in tblRecords. Then I have a select query find all DashNum from tblRecords where "Used" is "no", then finally I have to use a VBA expression DMIN("DashNum", "qryUnused" ) to find the first unused value.

While this works it means the user has to run two queries, plus a third query to reset all DashNum values to "no" after its done so the next update will grab values that are removed from the table.

I know I can have VBA run this code, but it seems like a complicated answer to a simple problem. Please help!

[ADezii]

Hi all,

I have a table called tblRecords that has "DashNum" [type: number] as its primary key. The lowest value of this table is 116 and the highest value is 269, though there are some missing values. It is expected that records will be removed in the future, leaving more missing values. I would like to have a text box have as its default value the first unused entry in this table.

I found a way to do this, but its not very efficient and requires an otherwise useless secondary table. I created a table called "DashNumBas is" with two columns: "DashNum" and "Used". "DashNum" has values starting at 116 and going to 9999, and used is a yes/no field. In order to find the first unused entry I have to run an update query which sets DashNumBasis!Us ed to "yes" for any record found in tblRecords. Then I have a select query find all DashNum from tblRecords where "Used" is "no", then finally I have to use a VBA expression DMIN("DashNum", "qryUnused" ) to find the first unused value.

While this works it means the user has to run two queries, plus a third query to reset all DashNum values to "no" after its done so the next update will grab values that are removed from the table.

I know I can have VBA run this code, but it seems like a complicated answer to a simple problem. Please help!

I have a table called tblRecords that has "DashNum" [type: number] as its primary key. The lowest value of this table is 116 and the highest value is 269, though there are some missing values

Hello nickvans, maybe I am misinterpreting what you are saying, but if DashNum is the Primary Key with values ranging from 116 thru 169, how can there be missing values?

[nickvans]

Hello nickvans, maybe I am misinterpreting what you are saying, but if DashNum is the Primary Key with values ranging from 116 thru 169, how can there be missing values?

Hi ADezii, Thanks for your response.

I have DashNum set as the primary key to prevent multiple entries using the same DashNum. The way the system is set up, we quote dash numbers to other departments for future work. Sometimes we get a notification that we won't actually use that dash number, and go back and delete it from our database. For instance, we might quote that we'll use dash number 270 for one customer, then the next day quote that we'll use 271 for another customer. The next day the customer who wanted 270 comes back saying they'll use a previously existing module, and no future work will be done. Dash number 270 should then be available for us to assign to another customer.

Hope this helps. Thanks.

[ADezii]

Hi ADezii, Thanks for your response.

I have DashNum set as the primary key to prevent multiple entries using the same DashNum. The way the system is set up, we quote dash numbers to other departments for future work. Sometimes we get a notification that we won't actually use that dash number, and go back and delete it from our database. For instance, we might quote that we'll use dash number 270 for one customer, then the next day quote that we'll use 271 for another customer. The next day the customer who wanted 270 comes back saying they'll use a previously existing module, and no future work will be done. Dash number 270 should then be available for us to assign to another customer.

Hope this helps. Thanks.

This code should work nicely for you. It assumes the Table containing the [DashNum] Field is named tblTest, and the Field on your Form to hold the 1st unused sequential Dash Number is txtDashNum. Make your necessary substitutions, and let me klnow how you make out:

Dim MySQL As String, MyDB As DAO.Database, rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
 
MySQL = "Select [DashNum] From tblTest Order By DashNum;"
 
Set MyDB = CurrentDb()
Set rst1 = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
Set rst2 = rst1.Clone
 
rst1.MoveFirst: rst2.Move 1     'move to the 2nd Record in rst2 
 
Do While Not rst2.EOF
  'If the difference between 2 consecutive Dash Numbers is not 1, since
  'they are ordered, this would indicate a gap in sequence
  If rst2![DashNum] <> rst1![DashNum] + 1 Then
    Me![txtDashNum] = rst1![DashNum] + 1
      rst2.Close
      rst1.Close
      Set rst2 = Nothing
      Set rst1 = Nothing
        Exit Sub
  End If
  rst1.MoveNext
  rst2.MoveNext
Loop
 
rst2.Close
rst1.Close
Set rst2 = Nothing
Set rst1 = Nothing
 

[nickvans]

This code should work nicely for you. It assumes the Table containing the [DashNum] Field is named tblTest, and the Field on your Form to hold the 1st unused sequential Dash Number is txtDashNum. Make your necessary substitutions, and let me klnow how you make out:

Thanks ADezii, your code works fantastically. It actually found a couple records which weren't in my database and should have been. Thanks a million!

[ADezii]

Thanks ADezii, your code works fantastically. It actually found a couple records which weren't in my database and should have been. Thanks a million!

I'm happy it all worked out for you.

[KingWm]

Thanks for the information. As an Access Hack, I found this very useful. However, there are two scenarios not covered I thought I would mention for others: 1) the table is empty and so the first available number is 1, and 2) all the numbers are in sequence and so there are none skipped. I am using this routine for a table that will be empty upon first use. And, since we are always filling in skipped numbers, it will be typical that there are none skipped. The only time we will find a skipped number is when a record was deleted.

First, if there are no records in the recordset, then you need a test at the beginning. I put this at the beginning of mine:

If rst1.EOF Then
    [DashNum] = 1
    GoTo LastLine ' I have a tag at the bottom before the close and destroy statements
 
Else
    rst1.MoveLast
    rst1.MoveFirst
    rst2.MoveLast
    rst2.MoveFirst
End If
 
If Not rst1.EOF And rst1![JID] <> 1 Then
    [DashNum] = 1
    GoTo LastLine
End If

Then, in the event that there are no skipped numbers, you need a line at the end, after the loop, to set the variable, otherwise you will get "0", or no result:

[DashNum] = rst1![JID] + 1
 

Thanks for the idea. I hope someone else finds this useful. Even though it has been 9 years since this was posted, it is still relavent.