Can Access find the lowest ID not used? 4

[GoinDeep]

To get Order Numbers for new orders I have two options on my New Orders Form. One is to type in the Pre-Printed number from the Form the customer fills out, or the other option is a Command Button that simply does Dmax to find the highest number in the Orders Table + 1. The problem is that users will sometimes enter the pre-printed Form Number in wrong and make it a very high number. Now I have a gap in the Order Number sequence that I could do without. Is there a way to find the "Lowest Un-used Number" in the table using code? Thank you in advance for any help or suggestions.

 

[BillPower]

Put this in the on click event of a form:

Dim fld As Field, intPos As Integer, intCounter As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Min(YourTableName.OrderID) AS " & _
"MinOfOrderID FROM YourTableName;", dbOpenSnapshot)
rs.MoveFirst
intCounter = rs!MinOfOrderID
Set rs = CurrentDb.OpenRecordset("SELECT YourTableName.OrderID, * FROM YourTableName " & _
"ORDER BY YourTableName.OrderID;", dbOpenSnapshot)
rs.MoveFirst
Do While Not rs.EOF
If rs!OrderID <> intCounter Then
MsgBox &quot;Lowest Un-used Number is: &quot; & intCounter
Exit Do
End If
intCounter = intCounter + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Replace YourTableName with your own table name.

Replace OrderID with your own field name.

You will need &quot;Microsoft DAO Object Library&quot; installed.

To do this, while in a module select from the menu Tools> References - scroll down, select the highest version, 3.51 or 3.6

When you click on the button you will get a message box giving you the &quot;Lowest Un-used Number&quot;

Good Luck

 

[BillPower]

It's my night for making mistakes, the first line should read:

Put this in the on click event of a button on a form:

 

[PWise]

this is my ordernum field from orders table
1
2
3
4
5
8
9
10
12
13
14
15
17
18
the following query will return the number 5
SELECT TOP 1 orders.ordernum
FROM orders
WHERE (((orders.ordernum) Not In (SELECT [orders].[ordernum] FROM orders INNER JOIN orders AS nextord ON [orders].[ordernum]+1=nextord.ordernum));
nextordernum = dlookup(&quot;ordernum&quot;,&quot;queryname&quot+1

 

[PWise]

better yet this query will return 6 from the above table
SELECT TOP 1 orders.ordernum +1 AS nextordernum
FROM orders
WHERE (((orders.ordernum) Not In (SELECT [orders].[ordernum] FROM orders INNER JOIN orders AS nextord ON [orders].[ordernum]+1=nextord.ordernum));
nextordernum = dlookup(&quot;onextordernum&quot;,&quot;queryname&quot

 

[BillPower]

Hi pwise,

Top marks for your examples, but I had a good reason for choosing the recordset way of doing this.

If i.e. the user has a table with let's say 5000 records and the 1st gap is at record 4000 the above query will have to run 4000 times to find it and if there's no gap, 5000 times. Before the advent of DAO I did exactly the same as you. Believe me, there's a massive difference in performance between the 2 methods.

I've put my message box in the wrong place in my posting. Adjustment below:

Dim fld As Field, intPos As Integer, intCounter As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(&quot;SELECT Min(YourTableName.OrderID) AS &quot; & _
&quot;MinOfOrderID FROM YourTableName;&quot;, dbOpenSnapshot)
rs.MoveFirst
intCounter = rs!MinOfOrderID
Set rs = CurrentDb.OpenRecordset(&quot;SELECT YourTableName.OrderID, * FROM YourTableName &quot; & _
&quot;ORDER BY YourTableName.OrderID;&quot;, dbOpenSnapshot)
rs.MoveFirst
Do While Not rs.EOF
If rs!OrderID <> intCounter Then
Exit Do
Else
End If
intCounter = intCounter + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
MsgBox &quot;Lowest Un-used Number is: &quot; & intCounter

Anyway, have a great weekend

Bill

 

[BillPower]

One final adjustment, for larger tables declare the Counter as Double.

 

[Craig0201]

But for even faster performance.....

Question whether it actually matters?? It most probably doesn't!

Craig

 

[DanJR]

here's a SELECT Statement that might be of interest to you:

Create a table called 'Table1' and a Field called 'ID'

Create some ID values (with missing ones) and run this query
[tt]
SELECT Min([A].[ID]+1) AS FirstID
FROM Table1 AS A
WHERE (((Exists (SELECT B.ID FROM Table1 as B WHERE A.ID=B.ID-1))=False))
[/tt]

It should return the minimum ID not used

Cheers,
Dan

 

[BillPower]

Hi again,

Finding the 1st unused no. in a table with 3000 records in Access 97 took 17 seconds and the same in Access 2000 took 32 seconds using a query. DAO took 0-1 second to find it in both versions. The 1st and only missing no. is 2500.

Which brings up an interesting point, has Access 2000 slowed down. Why should exactly the same table, query and code perform so much slower in 2000. Does anybody have an answer or is it my PC. O/S is Windows ME, Pentium III, 600MHz with 256 RAM.

I've put the DB's on a page at

http://www.wpower.fsbusiness.co.uk/

LowestUnusedNoAcc97.zip and LowestUnusedNoAcc2000.zip The DB opens in a form with 2 Buttons on it, IDE Query and ADO.

I'd be grateful if someone could check them out and get back to me.

Thanks, Bill

 

[DanJR]

Hi Bill....very interesting...

I Have:
-Athlon XP 1600 (1.33MHz)
-256 MB Ram
-Win2000
-Office XP
-lots of software installed

Your DAO method took 0 seconds
The 'pwise' query took 13 seconds
My query (above) took 10 seconds

However, here's a query without using the 'Not In' or EXIST clauses:
[tt]
SELECT Min(A.OrderID+1) AS MinOrderID
FROM YourTableName AS A LEFT JOIN YourTableName AS B ON A.OrderID = B.OrderID-1
WHERE b.OrderID is Null;
[/tt]

It also took 0 seconds....

Cheers,
Dan

 

[GoinDeep]

Question Bill?, I have a field that I want to just automaticaaly add this number to after clicking the cmdButton instead of the MsgBox. Is there a way to that?

Here is what is working for me, and very quick I might add. This has been a great help:

Private Sub cmdCreateOrderNo_Click()
On Error GoTo Err_cmdCreateOrderNo_Click
Dim fld As Field, intPos As Integer, lngCounter As Long
Dim rs As Recordset, datElapsed As Date, strElapsed As String
datElapsed = Now
Set rs = CurrentDb.OpenRecordset(&quot;SELECT Min(tblOrders.OrderNumber) AS &quot; & _
&quot;MinOfOrderID FROM tblOrders;&quot;, dbOpenSnapshot)
rs.MoveFirst
lngCounter = rs!MinOfOrderID
Set rs = CurrentDb.OpenRecordset(&quot;SELECT tblOrders.OrderNumber, * FROM tblOrders &quot; & _
&quot;ORDER BY tblOrders.OrderNumber;&quot;, dbOpenSnapshot)
rs.MoveFirst
Do While Not rs.EOF
If rs!OrderNumber <> lngCounter Then
Exit Do
Else
End If
lngCounter = lngCounter + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
strElapsed = DateDiff(&quot;s&quot;, datElapsed, Now)
MsgBox &quot;Lowest Un-used Number is: &quot; & lngCounter & vbCrLf & _
&quot;This took &quot; & strElapsed & &quot; seconds to find.&quot;

Exit_cmdCreateOrderNo_Click:
Exit Sub
Err_cmdCreateOrderNo_Click:
MsgBox Err.Description
Resume Exit_cmdCreateOrderNo_Click
End Sub

 

[BillPower]

Use one of these instead of the message box.

Me!ControlName = lngCounter 'control is on a main form
Me!SubFormName.Form!ControlName = lngCounter 'control is on a sub form

I would use DanJR's brilliant (Thanks!) example, which I've converted to a RecordSet:

Dim rs As Recordset, lngCounter As Long
Set rs = CurrentDb.OpenRecordset(&quot;SELECT Min(A.OrderID+1) AS MinOrderID &quot; & _
&quot;FROM YourTableName AS A LEFT JOIN YourTableName &quot; & _
&quot;AS B ON A.OrderID = B.OrderID-1 &quot; & _
&quot;WHERE b.OrderID is Null;&quot;, dbOpenSnapshot)
rs.MoveFirst
lngCounter = rs!MinOrderID
MsgBox lngCounter
rs.Close
Set rs = Nothing

 

[GoinDeep]

Okay, that works great. But of course that has now created another question. I have the rest of the controls on my form disabled until there is an OrderNumber in the OrderNumber field, so then after the OrderNumber_AfterUpdate event it will enable the rest of the controls as long as the OrderNumber field is not Null. When I manually enter an OrderNumber it works fine. It enables the controls and tabs to the next field. However, when using the command button to automatically enter the next lowest un-used OrderNumber it wont enable my fields????? It's like the field doesn't recognize there is data in the field unless I enter it manually. Do you know what the problem could be?

 

[DanJR]

If you haven't already done so, encapulate the code to enable/disable your controls into its own procedure e.g.
[tt]
Private sub EnableControls(YesNo as Boolean)
'code to enable/disable controls

Me!MyControl1.Enabled = YesNo
Me!MyControl2.Enabled = YesNo
etc...

end sub
[/tt]
Therefore, on the OrderNumber_AfterUpdate event, write the code:
[tt]
Call EnableControls(not isnull(me!OrderNumber))
[/tt]
Also, add the above line to the end of the code that billpower has provided.

Cheers,
Dan

 

[GoinDeep]

I believe I understand. I am not that experienced, but I would usume that is a more efficient way than I have it set now. Basically I have the OnCurrent Event that goes like:
OnCurrent
If IsNull(Me.OrderNumber) Then
Me.ctl1.Enabled = False
Me.ctl1.Enabled = False
Else
Me.ctl1.Enabled = True
Me.ctl1.Enabled = True
End If

Then I have it basically doing the same thing in the AfterUpdate Event.

If I understand yours (I think I do) then that sure would have saved me alot of work, I appreciate everyones help on this...

 

[GoinDeep]

Well, I thought I understood. My controls just stay enabled all the time now. I must be missing something...

 

[DanJR]

Need to check that the procedure is running. In form design, go into the event properties of the Form and OrderNumber textbox and check that [Event Procedure] is shown for the appropriate events ie, the Form's On Current event and the OrderNumber's AfterUpdate event.

Keeping the enabling/disabling controls routine in its own procedure saves you writing the code more than once, and if you make changes to the code, you only do it in one place.

The code pasted below works, but you'll need to change the name of the controls etc to your own control names.

[tt]
Private Sub EnableControls(YesNo As Boolean)
'code to enable/disable controls

Me.myControl1.Enabled = YesNo
Me.myControl2.Enabled = YesNo


End Sub

Private Sub Form_Current()
Call EnableControls(Not IsNull(Me!OrderNumber))

End Sub

Private Sub OrderNumber_AfterUpdate()
Call EnableControls(Not IsNull(Me!OrderNumber))
End Sub

Private Sub cmdCreateOrderNo_Click()
Dim rs As dao.Recordset, lngCounter As Long
Set rs = CurrentDb.OpenRecordset(&quot;SELECT Min(A.OrderID+1) AS MinOrderID &quot; & _
&quot;FROM YourTableName AS A LEFT JOIN YourTableName &quot; & _
&quot;AS B ON A.OrderID = B.OrderID-1 &quot; & _
&quot;WHERE b.OrderID is Null;&quot;, dbOpenSnapshot)
rs.MoveFirst
lngCounter = rs!MinOrderID
MsgBox lngCounter
rs.Close
Set rs = Nothing

Call EnableControls(Not IsNull(Me!OrderNumber))

End Sub
[/tt]

I hope this helps.

 

[GoinDeep]

Wonderful...I forgot OnCurrent. Thanks alot, it is doing just what I hoped for now.