473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Find first unused record number, above control number

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
6 6919
ADezii
8,834 Expert 8TB
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
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
8,834 Expert 8TB
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
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
8,834 Expert 8TB
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
KingWm
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

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

Similar topics

13
by: Chris Cioffi | last post by:
Hello all! I'm trying to subclass int such that once it reaches a certain value, it flips back to the starting count. This is for use as an X12 control number. Subclassing int and getting the...
26
by: Martin R | last post by:
Hi, How to find first not null value in column whitout chacking whole table (if there is a not null value then show me it and stop searching, the table is quite big)? thx, Martin *** Sent...
1
by: | last post by:
How can I obtain the record number or row number for a record while in form view? Thank you in advance for any information. R. L.
14
by: dharmdeep | last post by:
Hi friends, I need a sample code in C which will convert a Hexadecimal number into decimal number. I had written a code for that but it was too long, I need a small code, so request u all to...
4
by: jim | last post by:
I've been searching and trying out SQL statements for hours and I'm still having a problem. I'm querying 3 tables... and I only want the first unique record returned. Currently, the data...
2
by: angelscorp | last post by:
How can we fetch the record with maximun seq number where seq number is a column in the table......the table contains nearly fifty columns so below query is not working. SELECT TOP 1 id, name,...
2
by: kai | last post by:
Hi, I use VB 2008 as front end, SQL Server 2005/2008 as back end, I want control number of cucurrent users log on to the database, I am trying to use a stored procedure, but it did not work. Is...
3
by: pchaitanya | last post by:
I have selected some list of valid dates to a label. now i need to find first day among the given dates from label contrl i got dates from calender control by clicking for entire week.. ...
5
by: paragpdoke | last post by:
Hello All. I am new to SQL ... trying to make a query work untimately in Perl (v5.10.0 built for MSWin32-x86-multi-thread) against MySQL hosted remotely (which currently I don't know the version...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.