How to update table with calculated form value?

[Debwin]

I'm very new to Access but I've created a form named NewMembers which is
linked to a table called members. The object of the form is for the user to
input new membership details and besides the usual fields of name, address
etc. I have a field for Date of Birth. I also have a field called Schooldate
which contains the date of the school starting year i.e. 31/08/2006 and
another 2 unbound text boxes called Age(which shows the age of the member at
start of school year) and Session (which shows a value of 1 or 2 dependant on
whether the member was under or over 11 at the start of school date.)

I have written/obtained code to calculate the values for the age and session
fields but I need these values to be stored in the table also.

The code I have used to calculate values is below, but I am really stuck with
how to get these values into the table and would appreciate any suggestions.

Public Function GetAgeStr(varDOB As Variant, varDate As Variant) As String
' Purpose Returns a string of the age between dates in the following
format:
' Yy-Mm where Y is years and M is the months between dates
Dim dteDOB As Date, dteDate As Date
Dim lngMonths As Long, lngYears As Long
Dim value
value = GetAgeStr
If IsDate(varDOB) And IsDate(varDate) Then
dteDOB = CDate(varDOB)
dteDate = CDate(varDate)
lngMonths = DateDiff("M", dteDOB, dteDate) Mod 12
lngYears = DateDiff("M", dteDOB, dteDate) \ 12
If DatePart("m", dteDate) = DatePart("m", dteDOB) And DatePart("d", dteDate)
< DatePart("d", dteDOB) Then
lngYears = lngYears - 1
lngMonths = 11
End If
GetAgeStr = lngYears & "." & lngMonths
Else
GetAgeStr = ""
End If

End Function


Public Function GetSession() As Integer

Dim currentAge As Variant
Dim schoolAge As Double

currentAge = CDbl(txtAge)
GetSession = currentAge
schoolAge = (11#)

If currentAge < schoolAge Then
GetSession = 1
Else
If currentAge > schoolAge Then
GetSession = 2
End If
End If

 

[Guest]

You don't store calculated vaules.
You calculate them when you need them.

 

[Debwin]

So is there any way I can get round this?

You don't store calculated vaules.
You calculate them when you need them.

I'm very new to Access but I've created a form named NewMembers which is
linked to a table called members. The object of the form is for the user to

[quoted text clipped - 52 lines]

End If
End If

 

[Rick Brandt]

So is there any way I can get round this?

Stop trying? Here is what I usually recommend...

Remove the field for the calculation result from your table (you don't need
it).

Create a SELECT query that includes all the fields from your table PLUS an
additional field that does the calculation. Now just use that query every
place you would otherwise use the table. The value will be there for you
with no extra work and you can be smug in the fact that you are doing things
the correct way instead of the incorrect way.

 

[Guest]

Nothing to get around. Rick Brandt's reply is what you need. Using this
approach, you end up with reusable code that you can call whenever you need
the calculation. For example, our activity table contains values for each
month of the year. Most of the reports and queries for exporting to Excel
have to include Year to Date and Total Year calculations. I have a function
named CalcYearToDate() that performs the calculation based on the month you
pass to it. I use this function in all the queries that do the exporting or
that are recordsets for the reports. For YTD, I pass it the current month,
and for year total, I pass it 12.

CalcYearToDate(GetClosingMonth(), [jan], [feb], [mar], [apr], [may], [jun],
[jul], [aug], [sep], [oct], [nov], [dec])

And, you said you want to know the age of the person when the session
started. The way to do that is use your age calculation function and pass
the birth date and the session start date rather than the current date.

So is there any way I can get round this?

You don't store calculated vaules.
You calculate them when you need them.

I'm very new to Access but I've created a form named NewMembers which is
linked to a table called members. The object of the form is for the user to

[quoted text clipped - 52 lines]

End If
End If

 

[Debwin via AccessMonster.com]

Thanks, it works great now

Stop trying? Here is what I usually recommend...

Remove the field for the calculation result from your table (you don't need
it).

Create a SELECT query that includes all the fields from your table PLUS an
additional field that does the calculation. Now just use that query every
place you would otherwise use the table. The value will be there for you
with no extra work and you can be smug in the fact that you are doing things
the correct way instead of the incorrect way.

 

[Debwin via AccessMonster.com]

Thanks both for your invaluable help

Nothing to get around. Rick Brandt's reply is what you need. Using this
approach, you end up with reusable code that you can call whenever you need
the calculation. For example, our activity table contains values for each
month of the year. Most of the reports and queries for exporting to Excel
have to include Year to Date and Total Year calculations. I have a function
named CalcYearToDate() that performs the calculation based on the month you
pass to it. I use this function in all the queries that do the exporting or
that are recordsets for the reports. For YTD, I pass it the current month,
and for year total, I pass it 12.

CalcYearToDate(GetClosingMonth(), [jan], [feb], [mar], [apr], [may], [jun],
[jul], [aug], [sep], [oct], [nov], [dec])

And, you said you want to know the age of the person when the session
started. The way to do that is use your age calculation function and pass
the birth date and the session start date rather than the current date.

So is there any way I can get round this?

[quoted text clipped - 6 lines]