Table Macro to log edit date/time

[Danny J. Lesandrini]

I'm having trouble getting Table Macros to work. Might be
some general thing, but I'll give 2 examples:

1) Whenever any field is edited, I want the [EditRecDate] field to update to Now()
(of course, exclude updates to [EditRecDate] field in this trigger)
2) Whenever any field is edited, I want an [EditRecDate] in a DIFFERENT table to update to Now()
(this table summarizes all the "latest update dates" for a set of tables in the database)

I'm seeing this error in the Application Error Log:
EditRecord failed because the alias 'usysAgency' represents a record which is read only.

Going to investigate this next but I'm sure the database is not "read only". Maybe the problem is that I'm trying to do
this in the AfterUpdate macro when I should do it in the BeforeChange macro.

Ideas?
--
Danny Lesandrini
www.lesandrini.com/datafast/

[Danny J. Lesandrini]

Well, a little playing around worked.

First, you can't update a record in the After Update macro, but only on the Before Change macro event.

Second, I can update a row in another table, but first I had to do a "Lookup Record" action. This makes sense but the
table I'm using has only 1 row. It's a table I have in every database that contains info like Version, etc., and I only
allow 1 row in this table.

As an aside, I thought that the Data Macros allowed you to export the macro as XML or plain text, to share with others.
I couldn't figure out how to do that now. Does that feature still exist?

--
Danny Lesandrini
www.lesandrini.com/datafast/



"Danny J. Lesandrini" wrote in message news:Mwsxr.58461$iq1....@en-nntp-12.dc1.easynews.com...

[Danny J. Lesandrini]

Ok, maybe I shouldn't' answer my own posted questions but I found the answer
to my aside question:

You can copy the macro by pressing Ctl+A and Ctl+C while viewing it. You get the XML shown below, which can then be
pasted into the Macro window for a different table, etc.


<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro
Event="AfterUpdate"><Statements><LookUpRecord><Data><Reference>usysVersionServer</Reference><WhereCondition>[RowID]=1</WhereCondition></Data><Statements><EditRecord><Data/><Statements><Action
Name="SetField"><Argument Name="Field">usysVersionServer.usysAgency</Argument><Argument
Name="Value">Now()</Argument></Action></Statements></EditRecord></Statements></LookUpRecord></Statements></DataMacro></DataMacros>









--
Danny Lesandrini
www.lesandrini.com/datafast/



"Danny J. Lesandrini" wrote in message news:NLsxr.402350$Xo4.1...@en-nntp-13.dc1.easynews.com...

[christi...@yahoo.com]

Wouldn't it have been easier if MS had modeled these 'data macros'
after ESENT (Jet Blue) and just made vba accessible callbacks?

I sometimes think they want to make these features impossible for real
development but still give them something to hype for their new
release.


http://msdn.microsoft.com/en-us/library/windows/desktop/gg269245%28v=exchg.10%29.aspx

[Albert D. Kallal]

"Danny J. Lesandrini" wrote in message

news:NLsxr.402350$Xo4.1...@en-nntp-13.dc1.easynews.com...

>Well, a little playing around worked.

>First, you can't update a record in the After Update macro, but only on the
>Before Change macro event.

Correct. A really great way to think about this is compare this to a bound
form.

For a bound form we have before update and after update.

It would not make a whole lot of sense to update the record AGAIN after you
just safely written the record to disk. So one would not want to put code in
a forms after update event "right after" we just written the record to the
table. This would in fact cause the record in the form to be written again
to the table. So the same thinking of how we used VBA code in form works
rather well here in regards to the table events.

You don't want to choose a event that results in the record becoming dirty
again right after we just saved the record. No need to write the record to
disk again and again here.

In fact in a table event, if you do modify the same record in the after
update event it will cause both the before change and after update to fire
again (you can get around the read only limitation by using a lookup
record - kind of again like opening a recordset in VBA).

So just like in a Access form, you would not want to place code in the AFTER
update event that will cause the record to be all written to disk again.

So the before update event in a form is a great place to modify values since
then you don't cause the record to become any more "dirty" then it already
is.

And the before change event in a table is again the same idea and you can
modify values since you don't cause the record to become anymore dirty then
it already is. And more important that the before change event already has a
record "in context" so no look up record is required.

>Second, I can update a row in another table, but first I had to do a
>"Lookup Record" action.

Sure, I mean, think of this as like a open recordset. I mean even in VBA
with recordsets you would quite much have to "pull up" the correct record
and then modify it. So again, you quite much have to open up the table, and
grab a record that you modify.

>This makes sense but the table I'm using has only 1 row. It's a table I
>have in every database that contains info like Version, etc., and I only
>allow 1 row in this table.

Sure, but the same goes for VBA and a recordset. You simply open it and then
modify the record.

Note that in place of the table name, you could specify sql in the macro
like this:

Lookup a Record IN: select * from NextInvoiceNum where id = 1

So you can type in sql in the lookup record command. And in the above, you
don't need the id = 1 since as you note there only one record in the table.

And in VBA, we would still have to do something like this:

set rst = currentdb.OpenRecordSet("select * from NextInvoiceNum where id =
1")

So, again, think of the lookup record as "similar" to that of a open
recordset

--
Albert D. Kallal
Edmonton, Alberta Canada
PleaseNoS...@msn.com