VBA to update ages in a table

[Clint Marshall]

Using Access 2003, I'm trying to use VBA in a module to update the ages of
all of my students whenever I open a database.
I have created the code to calculate the ages, but can someone share with
me the VBA code needed to open the proper table (tblStudent) and access the
BirthDate field and then put the proper age into the Age field?
I have one case where I want to cycle through all of the students (when I
first open the database) and another where I need to update just one student
(on-update when someone fills in one of the missing birthdates or adds a new
student). So, I also could use help with the code to determine the number
of records and cycle through them one at a time.
Thanks for your help!

-Clint Marshall
Access Weenie Wannabe

 

[Wayne Morgan]

You shouldn't need to do this and I recommend that you don't. If you have
their birthdate you can calculate their age whenever you need it. This
calculation can be done in a query, form, or report. To do so, just call the
function you have to calculate the age and pass the birthdate to the
function. This would be done using a calculated textbox in a form or report
or a calculated field in a query.

Example Control Source of Calculated Textbox:
=MyAgeFunction([BirthdateField])

Example of Calulated Field in a Query:
StudentAge: MyAgeFunction([BirthDateField])


To do the update you mention, you would have the VBA call an Update Query to
update the table.

Example:
CurrentDb.Execute "MyUpdateQuery", dbFailOnError

To update just one student, you would place a parameter on the query to
limit it to the unique id of the current record.

 

[mario burrone]

Using Access 2003, I'm trying to use VBA in a module to update the ages of
all of my students whenever I open a database.
I have created the code to calculate the ages, but can someone share with
me the VBA code needed to open the proper table (tblStudent) and access the
BirthDate field and then put the proper age into the Age field?
I have one case where I want to cycle through all of the students (when I
first open the database) and another where I need to update just one student
(on-update when someone fills in one of the missing birthdates or adds a new
student). So, I also could use help with the code to determine the number
of records and cycle through them one at a time.
Thanks for your help!

-Clint Marshall
Access Weenie Wannabe