Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Identify the maximum number in a field before a gap in an sequence on before insert event using vba

Posted on 2016-10-21
8
Medium Priority
?
78 Views
Last Modified: 2016-10-23
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
0
Comment
Question by:Ronald Malk
8 Comments
 
LVL 55

Expert Comment

by:Ryan Chong
ID: 41853529
>>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

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.
1
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41853534
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
1
 
LVL 58
ID: 41853548
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41853553
It should be noted that - if you insist - it can easily be done.
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

Open in new window

The full story is here:

Find and Generate Missing Values in an Access Table

/gustav
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41854725
For a table a(..., f3)
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));

Open in new window

Result 3
This 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

Open in new window

Result 3
0
 
LVL 31

Accepted Solution

by:
hnasr earned 2000 total points
ID: 41854910
@Ronald Malk
Try this sample database. If still in doubt attach a representative copy of your database.
Check the code in the before insert event.
Private Function nextValue()
    'this code selects the minimum value of a filed in a table
    'where the next value is missing from the table
    'x and y are called aliases to allow using the same table in different nested queries
    'so the x.fld is compared to the y.fld
    Dim rs2 As Recordset
    Set rs2 = CurrentDb.OpenRecordset("SELECT Min(x.BookID)+1 FROM Tbl_Books AS x WHERE (((Exists (Select BookID from Tbl_Books y WHERE y.BookID=x.BookID+1))=False))")
    nextValue = rs2(0)
End Function
Private Sub Form_BeforeInsert(Cancel As Integer)
    BookID = nextValue()
End Sub

Open in new window

TblBooks.accdb
1
 

Author Comment

by:Ronald Malk
ID: 41855746
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.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41855777
Welcome!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question