Auto-populate date field based on two other fields.

  • Thread starter Thread starter Dee
  • Start date Start date
D

Dee

Enter "review date" and "review frequecy" to auto-populate "next review date"

For example if the user enters a review frequency of annual, and a review
date of 2/16/2010 then in the "next review date" field should populate
2/16/2011

Note that other options the user can select for the review frequency field
would be Quarterly, N/A, on contract renewal, etc. The user can also add a
frequecy that is not in the list.

Could someone please let me know what the vba code for this would be or if
it can done?
 
You need to put some VBA code in the AfterUpdate event of the
ReviewFrequency field. The code would use the Select Case construct and the
Case statements would use the DateAdd function. Look at Select Case and
DateAdd in the Help file.

Steve
(e-mail address removed)
 
Better to use a list combo than manually entering frequency information.
The combo to consist of this data --
12 Annually
3 Quarterly
1 Monthly
0 N/A

Next review date: DateAdd("m", [review frequecy], [review date])

The user can not add a frequecy that is not in the list.
 
You could use onexit code such as:

Select Case Forms![Form Name]![review frequency]

Case "annual"
Forms![Form Name]!next review date] = dateserial(Year([review date])+1,
month([review date]),day([review date])

Case "monthly"
Forms![Form Name]!next review date] = dateserial(Year([review
date]-day([review date]+32), month([review date]-day([review
date]+32),day([review date])

End Case


this is an example of what you can use with one caveat - my function for
monthly isn't robust, it will fail if the review date is the 31st and the
next month has less than 31 days.

Blair
 
Hi Dee

I suggest you use a simple text box and have the user enter the number of
days
till renewal. That makes the possibilities endless as there is no list.

Users can put in 730 days for biannual, 30 for monthly, 14 for biweekly, 7
for weekly and the math runs straight from the value. Much simpler coding,
vastly more options. As Steve said it would go in the after update but would
be a one liner using the dateadd function.

Regards

Kevin
 
DateAdd - I like it. Must have been added to 2000/3 - I've been using 97 up
until recently. Thanks, Steve and Kall.
 
Thank you all for your replies - It is just the information I was looking for!

KARL DEWEY said:
Better to use a list combo than manually entering frequency information.
The combo to consist of this data --
12 Annually
3 Quarterly
1 Monthly
0 N/A

Next review date: DateAdd("m", [review frequecy], [review date])

The user can not add a frequecy that is not in the list.

--
Build a little, test a little.


Dee said:
Enter "review date" and "review frequecy" to auto-populate "next review date"

For example if the user enters a review frequency of annual, and a review
date of 2/16/2010 then in the "next review date" field should populate
2/16/2011

Note that other options the user can select for the review frequency field
would be Quarterly, N/A, on contract renewal, etc. The user can also add a
frequecy that is not in the list.

Could someone please let me know what the vba code for this would be or if
it can done?
Click to expand...
Click to expand...
 
Back
Top