update fields in table using vba

[DanLezoche@gmail.com]

i have a client who's database requires users to enter the beginning
date and ending date for contracts. i would like to create a vba
function that checks the end date w/ the now() function (from what i've
gathered) and updates the status field with "Expired" if the end date
has passed. can i have this function run when the form opens, or a
report is run, so it loops through the entire table and adjusts these
fields as necessary? how should i code it?

any help with this would be greatly appreciated.

thanks,

dan

[Larry Linson]

Re: update fields in table using vba

<DanLezoche@gma il.com> wrote
[color=blue]
>i have a client who's database requires users to enter the beginning
> date and ending date for contracts. i would like to create a vba
> function that checks the end date w/ the now() function (from what i've
> gathered) and updates the status field with "Expired" if the end date
> has passed. can i have this function run when the form opens, or a
> report is run, so it loops through the entire table and adjusts these
> fields as necessary? how should i code it?[/color]

You can, easily, create an update query to do what you describe. You'll have
to run it either manually or programmaticall y -- it could be done in code in
the form from which you open the form or report, or, perhaps, in the Form or
Report's Open event.

Because the Now function includes both Date and Time, you might want to use
the Date function that uses midnight of the date for the time.

Larry Linson
Microsoft Access MVP

[DanLezoche@gmail.com]

Re: update fields in table using vba

Thanks Larry, that is brilliant!

Dan