Auto-Numbering for Checkbook Ledger

[David S.]

WE are designing a checkbook ledger and need a table where the check number
ibcreases by the value of 1 for each new row created. For instance, if I have
check 500 listed, my next entry would automatically be check number 501 and
so forth.

Please include a simple sample of how the code would work and the value of
the column i WILL BE USING (EG. Text box, number, etc.).

Thanks

 

[Allen Browne]

Use the BeforeUpdate event procedure of the *form* to get the maximum value
used so far and add 1.

This example assumes the field is named ChkNum in Table1. It does nothing if
there is already a value in the ChkNum text box (i.e. it looks up the value
only if the box is Null.) DMax() gets the maximum number so far. Nz() turns
that into zero if there are none yet. It then adds 1, and assigns the result
to the text box on your form.

We use Form_BeforeUpdate to do this because that is the last moment before
the record is saved. This reduces the chance of it assigning duplicates if
your database is ever used by multiple users at once.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ChkNum]) Then
Me.[ChkNum] = Nz(DMax("ChkNum", "Table1"), 0) + 1
End If
End Sub