By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,276 Members | 1,951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,276 IT Pros & Developers. It's quick & easy.

How To Get Access to Autofill Date Field From Past Date

P: 5
I enter my expense records AFTER a month is gone. What I want to be able to do is once I enter a date the next record's date auto fills next day's date. For eg. I entered first date as Dec 1, 2016, the next record date auto fills Dec 2, 2016 and so on.

Please give as much detail as possible, I have working knowledge of Access but I'm no expert.
Thanking you in advance for help!
Jan 5 '17 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,430
Assuming you have a field called "ExpenseDate" in a table called "TblExpenses" then this code gets the maximum date

Expand|Select|Wrap|Line Numbers
  1. LastDate = DMax("ExpenseDate", "TblExpenses")
We now wish to add 1 day to this date so use
Expand|Select|Wrap|Line Numbers
  1. NextDate = DateAdd("d", 1, LastDate)
(The 1 means add 1 and "d" means day - so add 1 day)

Putting the whole thing together, when you start to add the new record the Before Insert Sub fires so on the form's Before Insert you need the following
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub Form_BeforeInsert(Cancel As Integer)
  6.     ExpensDate.DefaultValue = DateAdd("d", 1, DMax("ExpenseDate", "TblExpenses"))
  8. End Sub
Jan 5 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.