I have a continuous form where the users wanted a daily sequential count to display on each record in the form.
i.e. if there were 10 entries on October 1st (the record entry date), entries would be labelled 1 through 10, on Oct 2nd the first entry would display 1, then 2, etc....
The code below worked great until a new requirement came in to be able to backdate the entry date. I'm stumped on what to do to update the daily count display when later on an entry date gets back-dated.
Any ideas would be appreciated!
(Note: The ID field is a primary key auto-number field)
'Daily Count Code
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim dEnterDate As Date
Dim dPreviousDate As Date
Dim lPreviousID As Long
Dim lPreviousDailyCount As Long
Dim LValue As Long
dEnterDate = Me.ENTRY_DATE
lPreviousID = DMax("ID", "Main")
lPreviousDailyCount = DLookup("[daily_count]", "Main", "ID = " & lPreviousID)
dPreviousDate = DLookup("[Entry_Date]", "Main", "ID = " & lPreviousID)
If DateDiff("d", dPreviousDate, dEnterDate) > 0 Then
Me.[Daily_Count].Value = 1
Me.[Daily_Count].Value = lPreviousDailyCount + 1