By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Auto update database?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.