By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Find first unused record number, above control number

P: 62
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 "DashNumBasis" 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!Used 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!
Oct 11 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,638
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 "DashNumBasis" 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!Used 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?
Oct 11 '07 #2

P: 62
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.
Oct 11 '07 #3

ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String, MyDB As DAO.Database, rst1 As DAO.Recordset
  2. Dim rst2 As DAO.Recordset
  3.  
  4. MySQL = "Select [DashNum] From tblTest Order By DashNum;"
  5.  
  6. Set MyDB = CurrentDb()
  7. Set rst1 = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
  8. Set rst2 = rst1.Clone
  9.  
  10. rst1.MoveFirst: rst2.Move 1     'move to the 2nd Record in rst2 
  11.  
  12. Do While Not rst2.EOF
  13.   'If the difference between 2 consecutive Dash Numbers is not 1, since
  14.   'they are ordered, this would indicate a gap in sequence
  15.   If rst2![DashNum] <> rst1![DashNum] + 1 Then
  16.     Me![txtDashNum] = rst1![DashNum] + 1
  17.       rst2.Close
  18.       rst1.Close
  19.       Set rst2 = Nothing
  20.       Set rst1 = Nothing
  21.         Exit Sub
  22.   End If
  23.   rst1.MoveNext
  24.   rst2.MoveNext
  25. Loop
  26.  
  27. rst2.Close
  28. rst1.Close
  29. Set rst2 = Nothing
  30. Set rst1 = Nothing
  31.  
Oct 11 '07 #4

P: 62
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!
Oct 15 '07 #5

ADezii
Expert 5K+
P: 8,638
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.
Oct 15 '07 #6

P: 1
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:


Expand|Select|Wrap|Line Numbers
  1. If rst1.EOF Then
  2.     [DashNum] = 1
  3.     GoTo LastLine ' I have a tag at the bottom before the close and destroy statements
  4.  
  5. Else
  6.     rst1.MoveLast
  7.     rst1.MoveFirst
  8.     rst2.MoveLast
  9.     rst2.MoveFirst
  10. End If
  11.  
  12. If Not rst1.EOF And rst1![JID] <> 1 Then
  13.     [DashNum] = 1
  14.     GoTo LastLine
  15. 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:

Expand|Select|Wrap|Line Numbers
  1. [DashNum] = rst1![JID] + 1
  2.  
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.
Nov 22 '16 #7

Post your reply

Sign in to post your reply or Sign up for a free account.