Auto-populate date field based on two other fields.


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?
 
Ad

Advertisements

S

Steve

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)
 
K

KARL DEWEY

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.
 
B

BlairH

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
 
K

kc-mass

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
 
B

BlairH

DateAdd - I like it. Must have been added to 2000/3 - I've been using 97 up
until recently. Thanks, Steve and Kall.
 
Ad

Advertisements

Ad

Advertisements

D

Dee

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top