Allowing a field to have a default which requires a calculation

[GraemeG]

Hello,

I am currently developing a database which allows us to collect information about properties.
I have the tables, queries, forms etc all setup to carry out what I need to.
However to make things easier I was wondering if it is possible to have a field which has a sum as a default value.

Example. (This a form which collects the data)
I have elements like Kitchens.
Field 1 - Kitchen Type
Field 2 - Kitchen Install year
Field 3 - Kitchen Renew year
Firstly the surveyor selects the type.
Then the surveyor would put in its install year i.e 2000
Then based on a life cycle of 15 years the surveyor would enter the renewal year. I.e. 2015
It would be easier for the renewal year to default value according to the install year.
I.e. =Sum([Kitchen Install Year] + 15)
But allowing the surveyor to change this value if need be.
I cant seem to get anything to work.


Any help would be much appreciated.
Thanks

 

[John Big Booty]

In the After Update event of Kitchen Install year, you could put some code like;

Me.[Kitchen Renew year] = [URL="http://www.techonthenet.com/access/functions/date/dateadd.php"]DateAdd[/URL]("yyyy", 15, Me.[Kitchen Install year])

That code assumes that you are storing dates, if not;

Me.[Kitchen Renew year] = Me.[Kitchen Install year] + 15

As a side not avoid using spaces and other special characters in your DB object and control names, limit yourself to alpha and numeric characters and the underscore (_).

 

[GraemeG]

Thanks for your response.
I am new to Access and associated code.

I am not entirely sure what you mean. I see the After Update in properties and tried to insert code but with no response.

I would try to put a screen print on to show you but it wont let me yet.
Try this:
//img257.imageshack.us/i/accessrenewaldates.jpg/

The install year and renew yeat are just text boxes set in the table so they surveyor could enter anything.
I just ran a form wizard from the table to setup my forms.
So in the control box on the form it relates to the specific field.

not sure if iam on th right track.

 

[John Big Booty]

For starters format your Install and renew year fields as number at the very least.

Now with the code; whilst your form is in design view, click in the the After Update event row for Kitchen Install year, select Event Procedure from the list. Now click on the button with the three full stops on it (ellipsis). A new code window will open up which will look something like;

[COLOR="Blue"]Private Sub[/COLOR] FinDate_AfterUpdate()

[COLOR="Blue"]End Sub[/COLOR]

Past the code i gave you between those two lines, save the form and return it to form view and see what happens when you add a date to Kitchen Install year and move to the next field.

 

[John Big Booty]

... also if you wish to share an image of anything else, simply put it in a zip file and attach it to your post, by clicking the paper clip icon at the top of the posting window

 

[GraemeG]

Brilliant! Thank you very much it works. I have akward brackets in my field names also. Might be too much work to redo all though.

At the moment it caluclates it once you click into the renew year cell, is there any way for it to auto once you have entered the install year?

 

[John Big Booty]

Brilliant! Thank you very much it works. I have akward brackets in my field names also. Might be too much work to redo all though.

At the moment it caluclates it once you click into the renew year cell, is there any way for it to auto once you have entered the install year?

I'm sure there is; but could you be a little clearer on the steps that the user will take and the response you wish the form to have

 

[GraemeG]

USER:
Firstly opens up the type of survey form
Then selects an address (record) by a search button (which is another question)
Then they proceed to enter the information.
Some tab through or others just click the field they want to fill in.

So:
Kitchen Type is a combo box where they select an option.
Then they tab or click the install year box and enter the year
Then they tab or click the renew year box and enter the year (now auto)

So what I am trying to say is if the use enters the install year the renew year auto fills rather than waiting for the user to click or tab into the renew year befor ethat date appears.

Another query I am now having with the auto sum is I have a validation rule which does not allow the renew year to be in the past (Not <2011)
but this no longer works unless you actually type a year in that is less than 2011.
Any ideas?

 

[John Big Booty]

I don't think we can do anything about the first request as the value in Kitchen Install year will not be committed until focus moves to another control.

The second part can be overcome by using some code in Kitchen Install year's Before Update event, that might look something like;

If Me.[Kitchen Install year] < [URL="http://www.techonthenet.com/access/functions/date/datepart.php"]DatePart[/URL]("yyyy", [URL="http://www.techonthenet.com/access/functions/date/date.php"]Date()[/URL]) Then
     MsgBox "Kitchen Install Date date must not be prior to the current year"
     Me.[Kitchen Install year].SelStart = 0
     Me.[Kitchen Install year].SelLength = Len([Kitchen Install year])
     Cancel = True
End If

 

[GraemeG]

Thanks.

Just a note on the code above, this was meant for Kitchen Renew Year, which cannot be in the past. Do I just change the Install Year to Renew Year? Also I am not sure about the "< DatePart("yyyy", Date())" section as the format is not date.

Sorry to keep pestering but is there any way of getting the Renew Date to default to the current year if the date caluclated is less than 2011?

Thanks

 

[Rabbie]

Sorry to keep pestering but is there any way of getting the Renew Date to default to the current year if the date caluclated is less than 2011?

Thanks

yTry looking up the IIF function in Access Help.

This should do the job for you

IIF(Renewdate < Year(date()),Year(date()),Renewdate)

 

[GraemeG]

yTry looking up the IIF function in Access Help.

This should do the job for you

IIF(Renewdate < Year(date()),Year(date()),Renewdate)

Thanks for the reply.
Not sure I quite understand this. The fields they are filling in are not Date formated, just text. So they can type in i.e. 1999. Iam going to change them to number in the near future.

Also do I put this expression as the validation rule?

 

[Rabbie]

Thanks for the reply.
Not sure I quite understand this. The fields they are filling in are not Date formated, just text. So they can type in i.e. 1999. I am going to change them to number in the near future.

Also do I put this expression as the validation rule?

The Year function returns an integer result. so this code will work provided there is a sensible entry in the Renewdate field. I would put it in the BeforeUpdate event for the field

 

[GraemeG]

Sorry to be a pain, but I still dont know how to work that code.

My fields are:
Kitchen Install Year (which surveyor enters i.e. 2000 and has the afterupdate calc)
Kitchen Renew Year (which now auto fills according to the calc)

 

[GraemeG]

yTry looking up the IIF function in Access Help.

This should do the job for you

IIF(Renewdate < Year(date()),Year(date()),Renewdate)

I am placing this code in the field I am validating under the BeforeUpdate field.

I am getting a compile error (Expected: =)

Any ideas?