Updating field before append query

I have a form that shows a single record based on a query criteria.
When I click a button it is set to use an append query to copy that
record to a separate table, then deletes the record from the first
table. Both tables have a 'DateTime' field that shows when it was last
updated using the Now function.

What I am trying to do is get the 'DateTime' field updated after the
append query copies it to the new table. I have tried updating the
DateTime in the first table before coping the record, but the new time
doesn't get saved in the first table before the record gets copied.

Is there a way to do this, either by getting the DateTime to save in the
first table before the record gets copied? Or by using code to write a
new DateTime in the second table after it gets copied? The 2nd table is
not in the original query to select the single record, only in the
append query that happens after I click an "OK" button on the form.

Many thanks to whomever can help me with this! even if they just point
me in the direction of what to look for!

Thanks,
Ray

re: Updating field before append query

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Set up the DateTime column in the second table w/ a default of Now() or
Date(). When you append the data to the second table, do not include
the DateTime column data. When the append happens the DateTime value
will be the date & time you appended the row (the default).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlQfUIechKqOuFEgEQKNiACg2rqphM14J/WEkeHtGa0t8FAVuvwAoOTM
Aka4Uc3ZTFxeXDt5bsyvunlk
=N6OP
-----END PGP SIGNATURE-----


Ray Holtz wrote:[color=blue]
> I have a form that shows a single record based on a query criteria. When
> I click a button it is set to use an append query to copy that record to
> a separate table, then deletes the record from the first table. Both
> tables have a 'DateTime' field that shows when it was last updated using
> the Now function.
>
> What I am trying to do is get the 'DateTime' field updated after the
> append query copies it to the new table. I have tried updating the
> DateTime in the first table before coping the record, but the new time
> doesn't get saved in the first table before the record gets copied.
>
> Is there a way to do this, either by getting the DateTime to save in the
> first table before the record gets copied? Or by using code to write a
> new DateTime in the second table after it gets copied? The 2nd table is
> not in the original query to select the single record, only in the
> append query that happens after I click an "OK" button on the form.
>
> Many thanks to whomever can help me with this! even if they just point
> me in the direction of what to look for![/color]

re: Updating field before append query

Thank you MGFoster, that will work great for what I need.

MGFoster wrote:[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Set up the DateTime column in the second table w/ a default of Now() or
> Date(). When you append the data to the second table, do not include
> the DateTime column data. When the append happens the DateTime value
> will be the date & time you appended the row (the default).
>[/color]