Logging the Updated Records from a form

[dhstein]

I have a form that I've created that is bound to a table. This will be used
to update records. At the moment, I've put a button there with a caption of
"Update" and this runs the "Save Record" macro. What I'd really like,
however, is to log the transaction in a log table and then do the save. But
it looks like if I want to do anything other than the save , I have to create
my own routine to do the logging and then put in the code to open the table,
update each field from the form and close the table. Am I correct, or is
there a better way - that would save me some time? More importantly, it
seems that if I use the embedded macros, I am restricted to the set of
commands that Access provides. Is there any way to add or insert my own code
in the embedded macro selections so that I could add functionality? Thanks
for any help.

 

[Neil]

I have a form that I've created that is bound to a table. This will be
used
to update records. At the moment, I've put a button there with a caption
of
"Update" and this runs the "Save Record" macro. What I'd really like,
however, is to log the transaction in a log table and then do the save.
But
it looks like if I want to do anything other than the save , I have to
create
my own routine to do the logging and then put in the code to open the
table,
update each field from the form and close the table. Am I correct, or is
there a better way - that would save me some time? More importantly, it
seems that if I use the embedded macros, I am restricted to the set of
commands that Access provides. Is there any way to add or insert my own
code
in the embedded macro selections so that I could add functionality?
Thanks
for any help.

You discuss using macros. I take it you don't want to use VBA code? Assuming
you want to use macros, you first need to create a query that copies the
record from your table to your log table. A simple Append query. Then, in
your macro, you run the query using the Run Query command before (or after)
doing the save.

Now, all that's well and good. But you need a way for the query to only copy
the current record. Your Append query would use * to copy all fields from
your table to the log table. Then add your PK field to the query, and
uncheck the Show check box for that field. In the criteria row of your PK
field, enter the following:

[Forms]![MyForm]![MyPK]

where "MyForm" is the name of the form you're using, and "MyPK" is the name
of the PK field of the bound table. This will tell the Append query to only
copy the record from the table whose PK field value is the same as the one
of the record that's currently displayed in the form.

For me, personally, I think it's better to do it right after the save,
rather than right before the save. That way you get a record of each update
as it happens. If you do it before the save, you're copying the *previous*
save, not the current save (and the last set of modifications are never in
the log file). Thus, IMO, it's best to do your logging after the save,
rather than before. But, whatever works for you.

HTH,

Neil