How to generate sequential dates automatically.
Q: Richard Bryant writes: “I have made a diary in Access. I enter a month at a time from notes on my calendar. I have to enter each date of the month each time I make an entry. In Excel the dates can be put in sequentially automatically. Is there a way to set up a form so that stepping forward to a new record automatically creates the next date?”
A: If you wrote in your diary every day, then a simple default value of Date() in the date field would work, but for batch data entry something more is needed, such as the event procedure listed below. It will run the first time anything is typed into the new record, so put another control before the date control in the tab order.
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo ErrorHandler
Dim dteDiaryNext As Date
dteDiaryNext = DMax(“[DiaryDate]”, “tblDiary”) + 1
Me![txtDiaryDate].Value = dteDiaryNext
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
