Identify the maximum number in a field before a gap in an sequence on before insert event using vba
Posted on 2016-10-21
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
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