Auto update database?

[MVA]

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

[Allen Browne]

Re: Auto update database?

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" <william.mva@bt connect.com> wrote in message
news:bj78o6$sv8 $1@titan.btinte rnet.com...[color=blue]
> 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[/color]
data[color=blue]
> 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[/color]
could[color=blue]
> say, open up the Switchboard frm, and all the records in the database gets
> updated?
>
> Many thanks in advance.
>
> Will
>
>[/color]

[Robert]

Re: Auto update database?

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.OpenRecords et("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" <abrowne1_SpamT rap@bigpond.net .au> wrote in message news:<wfG5b.826 35$bo1.19883@ne ws-server.bigpond. net.au>...[color=blue]
> 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" <william.mva@bt connect.com> wrote in message
> news:bj78o6$sv8 $1@titan.btinte rnet.com...[color=green]
> > 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[/color]
> data[color=green]
> > 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[/color]
> could[color=green]
> > say, open up the Switchboard frm, and all the records in the database gets
> > updated?
> >
> > Many thanks in advance.
> >
> > Will
> >
> >[/color][/color]