Need To track and date each time a record has been changed. 1

[playmate]

Multiple users will use the database I have created. I need to track each time a record has been changed and the date it was changed. Currently I am updating the table by using a form that is based on a query. Please tell me how to create a history for each record. <br>
<br>
Also, is there any way to notify the users (via message box) when a record has been added or changed while the users are using other applications.<br>

 

[DougP]

yes to tell when arecord is changed can be done by having the users press a Re-save button<br>
this button will save the record and also add teh sytenm date to a new field you must add to the table.<br>
<br>
To notify others<br>
Do the others have e-mail?<br>
If so &quot;docmd.sendobject&quot; will work there.<br>

 

[Apollo6]

Playmate<br>
I have a similar situation...<br>
<br>
DougP<br>
Where is the Re-save button, is it something that needs to be added, if so, how/where? Would this method keep an individual record for each time the original record was modified? Thanks for any help you can give.

 

[DougP]

Yes Apollo6<br>
Do you know how to create a button for your form?<br>
<br>
In design view turn on the &quot;Toolbar&quot;<br>
Make sure the Wizard button is deprssed it has a &quot;Majic wand&quot; with fairy dust dropping from it (at the top or left).<br>
click &quot;Command button&quot;<br>
draw it on your form.<br>
it will launch the wizard.<br>
In &quot;categories&quot; list click &quot;Record Operations&quot; in the &quot;Actions&quot; list pick &quot;Save Record&quot;<br>
finish prompts.<br>
Now right click on your new button and click properties<br>
click the &quot;Event&quot; tab in the properties box.<br>
You will see and &quot;Event Procedure&quot; in the &quot;On click&quot; event.<br>
Click it and look at the far right side of the box you will see a &quot;3 dots&quot; button.<br>
click the dots button.<br>
this is the code window (VBA).<br>
you can do whatever you want here.<br>
you will see your &quot;Save&quot; code in the middle.<br>
<br>
to send e-mail look at &quot;docmd.sendobject&quot;<br>
Type it in actually then double click on sendobject to highlight it and press F1 it will bring up help on that item.<br>
<br>
Second Question Answer<br>
Yes it will Keep &quot;Re-save&quot; the original record<br>
Now to add the date and time you have to have a new field<br>
Call it &quot;RecordSaveTime&quot;<br>
<br>
here is an example button<br>
-----------------------------------------<br>
Private Sub Command6_Click()<br>
On Error GoTo Err_Command6_Click<br>
Dim MessageText As String<br>
Me![RecordSaveTime] = Format(Now, &quot;hh:nn:ss&quot<br>
Me![RecordSaveDate] = Format(Now, &quot;mm/dd/yy&quot<br>
<br>
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70<br>
<br>
MessageText = MessageText & &quot; UPS RED Order &quot; & Chr$(10) '&lt; -line feed<br>
MessageText = MessageText & &quot;Process NOW...&quot;<br>
<br>
DoCmd.SendObject acSendNoObject, , actext, &quot;<A HREF="mailtoPoston@blah.com">DPoston@blah.com</A>&quot;, , , &quot;HOT Order&quot;, MessageText, False<br>
<br>
Exit_Command6_Click:<br>
Exit Sub<br>
<br>
Err_Command6_Click:<br>
MsgBox Err.Description<br>
Resume Exit_Command6_Click<br>
<br>
End Sub<br>
<br>
-----------------------------------------------------------<br>
NOTE:<br>
just paste the following 7 lines in your sub commnad button code.<br>
Dim MessageText As String<br>
Me![RecordSaveTime] = Format(Now, &quot;hh:nn:ss&quot<br>
Me![RecordSaveDate] = Format(Now, &quot;mm/dd/yy&quot<br>
<br>
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70<br>
<br>
MessageText = MessageText & &quot; UPS RED Order &quot; & Chr$(10) '&lt; -line feed<br>
MessageText = MessageText & &quot;Process NOW...&quot;<br>
<br>
DoCmd.SendObject acSendNoObject, , actext, &quot;<A HREF="mailtoPoston@blah.com">DPoston@blah.com</A>&quot;, , , &quot;HOT Order&quot;, MessageText, False<br>
-----------<br>
<br>

 

[DougP]

One note in above I put code to save both date and time<br>
you need 2 fields for this.<br>
<br>
The Code is correct but my wording above it says add 1 field.

 

[elizabeth]

FYI, I sometimes find it useful to save the userID and date when a record is added and the last time it was updated, so I end up with 4 fields added to the record. If I had to save every change, I'd create a separate table. A shortcut for adding the date created (new records only) is to make now() (I put time and date into the same field) the table's default for the field. That way it gets entered without any code. And to save a little coding on the updated field you can just put the Me!TimeandDate=Now() into the BeforeUpdate event.