Calculating default date on forum from other values entered

[WH2O]

In trying to make a data-entry form as idiot-proof and user-friendly as possible, I want the computer to calculate the end date for a lease, based on the user typing in the start date and the length in years.

I've figured out how to make the information display as text:

[start date]+[length]*365

But I want this to be displayed as the default value for the field, giving the user the option to change it if necessary.

Can anyone help? Your time is much appreciated.

 

[Darrylles]

Hi,

In the control source property of the text box use this:

=[start date]+[length]*365

As long as [Start date] and [length] is a field in the form record source.

Regards,

Darrylle
"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[WH2O]

Thanks, that sort-of works. However, I still need two things to happen.

1. The user needs the option to change it when appropriate.

2. I need either the default or the user corrected data stored in the "End Date" field.

By putting a formula in the control source, it seems to prevent both of these.

Any ideas on how I can have the default pop up via formula for the user to accept or change and then store that data in the field?

Thanks!

 

[sathandle]

Steps...
1. In AfterUpdate of startdate and the length fields...

[tt]
Dim strResp as string

'Insert code to test to make sure a value is entered for startdate & length... If not IsNull(startdate) or ...

strResp = MsgBox("Do you want " & ([startdate] + [length] * 365) & "as the End Date?", vbYesNo)
If strResp = vbYes Then
docmd.runmacro "mcrSetDate" [/tt] 'See creating the macro below...
[tt] Else
enddate.setfocus 'Allow user to enter own date
End If [/tt]

2. Create a macro
Action: SetValue
Arguments:
Item = specific reference to Forms!FormName![end date]
Expression = (Forms!FormName![startdate] + Forms!FormName![length] * 365)

3. Establish controlsource for end date as the field where you want to store the information.

GOOD LUCK! This process took me a good couple of weeks to decipher...

SATHandle
definition of 'less behind': "not fully caught up, digging out slowly, one-week delay to "The IT hit the fan."

 

[MichaelRed]

however this CAN (will?) cause a problem. Date + 365 is NOT a year (approx 25% of the time). Use DateAdd("YYYY", 1, NYrs, Date()). Better yet, look up dateadd in help and understand some of hte more basic functions available.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[WH2O]

Thank you for your time! These are great suggestions and I will give them a try.

WW