Unanswered: Store date and time of changes made to 2 tables in one macro?
I have a macro (datemodified) which records the date and time "now() command" into a textbox on a form called 'frmpersonaldetails' and the corresponding field in the forms table called "tblpersonaldetailsvia when each field on the form is changed on its After Update.
It works fine for that single form and table, but I also have a second table(tblsentencenumber) and subform (frmsentencenumber) on the form and I need a macro or piece of code to make both tables show when they were last updated.
The two tables are linked via a relationship "personID".
I dont really want 2 macros with DateModified in each of the two tables. Just if either is changed afterupdate and have it stored in one place only if either table is altered.
In whatever code you have running to update the main form value insert something like this:
Dim con As ADODB.Connection
Dim tmp As Date
Set con = Application.CurrentProject.Connection
tmp = Now()
Me.Text2.Value = tmp
Me.form2.Controls("Text2").Value = tmp
con.Execute "update mod_table set mod_table.date_mod = #" & tmp & "# where mod_table.table_name = 'yourformnamecategory'"
Set con = Nothing
What I would do is create a seperate table named something like 'form_mod_history' with columns form_name, date_mod and update those values.