I have a similar problem with the automated updating of a calculating
Age() field. In reading the Age() Function usage found in the
suggested link, I am not sure how to enclose the Age() function or the
code into a query being used as a RecordSource. How is this done?
Alternatively, I have some code that does the age calculation
perfectly but my problem lies in the section of code that updates each
record as it loops through the recordset. Instead of updating each
record with appropriate age, my code takes the age from the FIRST
record and inserts it into ALL records.
Please help as I am not sure where i have gone wrong with this coding.
Thanks in advance,
Robert
~~~code begins here~~~
Sub Form_Load()
Dim dbs As Database
Dim rst As Recordset
Dim strCriteria As String, strDOB As String
Dim DOB As Variant, AGE As Variant
Dim Month1 As Variant, Month2 As Variant
Dim Day1 As Variant, Day2 As Variant
~~~age calculation begins here~~~
Set dbs = CurrentDb
Month1 = Format(DOB, "m")
Month2 = Format(Date, "m")
Day1 = Format(DOB, "d")
Day2 = Format(Date, "d")
If Month1 > Month2 Then
txtAGE = DateDiff("yyyy", txtDOB, Date) - 1
ElseIf Month1 = Month2 Then
If Day1 > Day2 Then
txtAGE = DateDiff("yyyy", txtDOB, Date) - 1
Else
txtAGE = DateDiff("yyyy", txtDOB, Date)
End If
Else
txtAGE = DateDiff("yyyy", txtDOB, Date)
End If
~~~recordset loop begins here~~~
' Set search criteria.
strCriteria = txtAGE
strDOB = txtDOB
AGE = txtAGE
DOB = txtDOB
' Create dynaset-type Recordset object.
Set rst = dbs.OpenRecordset("tblPrimary", dbOpenDynaset)
' Find first occurrence.
rst.MoveFirst
With rst
' Loop until reaching End Of Field
Do While Not rst.EOF
If (strCriteria <> !AGE) Then
' Enable editing.
.Edit
' the following fields are open for
editing
!AGE = AGE
' !DOB = DOB
' Save changes.
.Update
' Check next table record
rst.MoveNext
Else
rst.MoveNext
End If
Loop
End With
rst.Close
Set dbs = Nothing
End Sub
~~~end of code~~~
"Allen Browne" <ab***************@bigpond.net.au> wrote in message news:<wf*******************@news-server.bigpond.net.au>...
Do not store the Age and DaysJoined in your table.
Instead, use a query to calculate these values, and make the query as the
RecordSource for your form. This way the value can never be wrong.
The calculated field for days joined would be:
DaysJoined: DateDiff("d", [DateJoined], Date())
For a discussion on calculating Age() see:
http://allenbrowne.com/func-08.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
"MVA" <wi*********@btconnect.com> wrote in message
news:bj**********@titan.btinternet.com... Hi all
I have a database where in a table (tbl_Members), there are 2 date fields
(DateOfBirth and DateJoined), and also 2 fields which upon entering the
data in the form, it automatically works out the Age (in years) and number of
DaysJoined (in days).
It works fine but it does not automatically update the Age or DaysJoined
columns on opening the form. Can anyone make a suggestion as to how I
could say, open up the Switchboard frm, and all the records in the database gets
updated?
Many thanks in advance.
Will
