Ronald Malk
asked on
Identify the maximum number in a field before a gap in an sequence on before insert event using vba
I tried all day searching forums to find an answer but I couldn't find any.. I hope someone could help me here.
the "BookID" is a field in the table for auto generated ID number for the book This is what I' m using at the moment
Private Sub Form_BeforeInsert(Cancel As Integer)
TxBook = Nz(DMax("BookID", "Tbl_Books"), 0) + 1 'and works fine
end sub
Works fine but what I need is if one book or more is deleted I want it to inset the lowest missing number then if nothing misiing will inset the maximum number
the "BookID" is a field in the table for auto generated ID number for the book This is what I' m using at the moment
Private Sub Form_BeforeInsert(Cancel As Integer)
TxBook = Nz(DMax("BookID", "Tbl_Books"), 0) + 1 'and works fine
end sub
Works fine but what I need is if one book or more is deleted I want it to inset the lowest missing number then if nothing misiing will inset the maximum number
Yes, seems like a very bad idea.
Leave the old Id, but mark it as Deleted or Obsolete (a boolean field).
Then proceed as you do now.
For a current list of books, filter on Obsolete = False
/gustav
Leave the old Id, but mark it as Deleted or Obsolete (a boolean field).
Then proceed as you do now.
For a current list of books, filter on Obsolete = False
/gustav
An auto number for a primary key is a meaningless key and you're trying to apply meaning to it by having the sequence represent something. That's why you're not finding any solutions on this.
There's no reason to do what you're trying to do, and as Ryan pointed out good reasons for not doing it.
As Gustav pointed out, there are other ways to handle situations that you might have. What is it that you're trying to achieve by reusing these old numbers?
Jim
There's no reason to do what you're trying to do, and as Ryan pointed out good reasons for not doing it.
As Gustav pointed out, there are other ways to handle situations that you might have. What is it that you're trying to achieve by reusing these old numbers?
Jim
It should be noted that - if you insist - it can easily be done.
You can create a list of missing numbers:
Find and Generate Missing Values in an Access Table
/gustav
You can create a list of missing numbers:
Private Sub btnSearch_Click()
' Read table/query sequentially to
' record all missing IDs.
' Fill a ListBox with missing values.
' A reference to Microsoft DAO must be
' present.
' Define search table or query.
Const cstrTable As String = "Orders"
Const cstrField As String = "OrderID"
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lst As ListBox
Dim col As Collection
Dim strSQL As String
Dim strList As String
Dim lngLast As Long
Dim lngNext As Long
Dim lngMiss As Long
strSQL = "Select " & cstrField & "" _
& " From " & cstrTable & " _
& " Order By 1;"
Set lst = Me!lstMissing
Set col = New Collection
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount = 0 Then
'The recordset is empty.
'Nothing to do.
Else
lngLast = rst(cstrField).Value
rst.MoveNext
While rst.EOF = False
lngNext = rst(cstrField).Value
For lngMiss = lngLast + 1 To _
lngNext - 1
col.Add (lngMiss)
Next
lngLast = lngNext
rst.MoveNext
Wend
'Generate next value in sequence.
'Discard if collecting only
'missing values.
col.Add (lngLast + 1)
End If
rst.Close
'Populate list box from collection.
For lngMiss = 1 To col.Count
If Len(strList) > 0 Then
strList = strList & ";"
End If
strList = strList & col(lngMiss)
Debug.Print col(lngMiss)
Next
lst.RowSource = strList
Debug.Print strList
Set rst = Nothing
Set dbs = Nothing
Set col = Nothing
Set lst = Nothing
End Sub
The full story is here: Find and Generate Missing Values in an Access Table
/gustav
For a table a(..., f3)
f3
1
2
4
5
This query gives the next lowest missing number:
This a VBA code for a function to return the next value as a variable:
f3
1
2
4
5
This query gives the next lowest missing number:
SELECT Min(x.f3)+1 AS Expr1
FROM a AS x
WHERE (((Exists (Select f3 from a y WHERE y.f3=x.f3+1))=False));
Result 3This a VBA code for a function to return the next value as a variable:
Private Function nextValue()
Dim rs2 As Recordset
Set rs2 = CurrentDb.OpenRecordset("SELECT Min(x.f3)+1 FROM a AS x WHERE (((Exists (Select f3 from a y WHERE y.f3=x.f3+1))=False))")
nextValue = rs2(0)
End Function
'to use, you may set txtBox1 = nextValue()
Private Sub Command40_Click()
Debug.Print nextValue()
End Sub
Result 3
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much it works very good, It's something new for me, First time I do VBA this way, I still need to teach my self more of the internet that where I'm learning from.
Welcome!
so you mean to reuse the so called "deleted id" for your new entry? from my opinion, you should use the new id instead of re-using the "deleted id" to maintain your data integrity.