I'd like to add a control to my form that would autmatically update when any changes are made to a record (text or yes/no boxes) and then build a query to find all updated records within last 30 days. Help???

Thanx in advance. And thanx for all previous help!!!

Add a Date/Time field to your table and call it Updated or whatever you like. In your form put todays date in that field if the record is changed. Now you can use a query to search for records that have an Updated date that is within the last 30 days. Note that there will only be one date so if you want to keep a history of update dates you will need a related table and then append the lastest update date to that table...

hth,
Jack

Thanx Jack, I guess I hadn't looked for the right verbiage when I was searching before I posted this. I have the "updated" box on my form and the query built. Now, can I get a report of WHAT was changed on the form???

Ah ha! What you want is an 'audit trail' and this article will get you started....

Good luck!

Jack

I just found that KB article and tried it in the sampleDB and it does what I want. But I don't want that data on my form AND I need a report of all changes made. I know I can make the field hidden, but now the report of changes???

Thanx Jack.

The updated data does not appear on the form. You can use the Updated memo field from the table as a control in a report and by making the control big enough you will see all the data that is stored there. If you do not like that idea then create a table of Changes and append the changes, along with RecordID to that table. That way you will have a single record for each change made to each record.

hth,
Jack

I really should do some of my own work before I pester you guys. I have the report built from the query. Can I assign 2 or more commands to BeforeUpdate?

Thanx,
Jim

Jim -

I am not sure what you want to do, but you generally speaking you are not limited to how much code you can put into an event... What is it that you want to add to the Before Update event?

hth,
Jack

I already have an "Updated" Macro (automatic date) running on BeforeUpdate. Now I want the AuditTrail (see below code) to run also. When I add AuditTrail() to the LastUpdated macro, it returns error #2424. Do I need to only list those types of controls that are actually on the form?

Option Explicit

Function AuditTrail()
On Error GoTo Err_Handler

Dim MyForm As Form, C As Control, xName As String
Set MyForm = Screen.ActiveForm

'Set date and current user if form has been updated.
MyForm!Updates = MyForm!Updates & "Changes made on " & Date & " by " & CurrentUser() & ";"

'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"New Record """
End If

'Check each data entry control for change and record
'old value of Control.
For Each C In MyForm.Controls

'Only check data entry type controls.
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
' Skip Updates field.
If C.Name <> "Updates" Then

' If control was previously Null, record "previous
' value was blank."
If IsNull(C.OldValue) Or C.OldValue = "" Then
MyForm!Updates = MyForm!Updates & Chr(13) & _
Chr(10) & C.Name & "--previous value was blank"

' If control had previous value, record previous value.
ElseIf C.Value <> C.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
C.Name & "==previous value was " & C.OldValue
End If
End If
End Select
Next C

TryNextC:
Exit Function

Err_Handler:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
End If
Resume TryNextC
End Function

Okay, I got rid of the error by removing references to controls I don't have on the form, but it won't log what the changes were. Maybe I didn't list acCheckBox correctly?

Save the AuditTrail() funtion in a Standard Module. Convert your macro to code using the tool provided in Access. In the forms Before Update event click in it and then click on the down arrow to the right of the field. Select [Event Procedure] then click the button to the right with 3 dots to open the code editor. The cursor will be flashing between two lines of code. This is where you will put AuditTrail() line of code, without the = in front of it. Just: AuditTrail

I do not know what your macro does, but it sounds like maybe it does not belong in the Before Update event. If you can tell me what it does I will try to advise you further.

I will be leaving at 8:30 Pacific Time and will return sometime after noon....

hth,
Jack

Thanx for your time Jack,

The Macro enters the date (visible to user) that the record was updated. The instructions for employing this stated to select it in Before Update. I added RunCode=AuditTrail() to this macro and it now runs fine. But I'm not getting the report to tell me what was changed, only that a change was made on that date. I have the control listed as acCheckBox. Is that correct or not?

Jim

Hmmm. Not sure what is going on. You should have a control named Updated (as I recall) and the changes made to the record are stored there. Do you have that control in your table? If you make a change doe is show up there? The first entry in the Updated field is a carriage return so it may look blank. Resize the field in the table and see if your changes are record there....

Jack

I've resized the field on the report with no luck. I've deleted a few lines of code in the AuditTrail so it only has one blank line but it just logs that a change was made, not what control on the page (CheckBox) was changed.

Here's where I think part of the problem lies:

Select Case C.ControlType
Case acTextBox, acCheckBox
' Skip Updates field.
If C.Name <> "Updates" Then

' If control had previous value, record previous value.
ElseIf C.Value <> C.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) & _
C.Name & "==previous value was " & C.OldValue

When I run my query/report, all it says is:

Changes made on 11/4/2004 by Admin;Changes made on 11/4/2004;Updates==previous value was

And repeated for every time that I make a change to that record. It never tells me which control, other than Updates, was changed. It must be something in the code that I'm not getting modified right.

Your code should look exactly like the code in the article I suggested. You can change the Select Case code, as you have done, but the rest of the code should be copy and paste. My only suggestion is to put a stop in the code and check the values in the variables as you step through the code. This should show you were the code is breaking down.

hth,
Jack

The rest of the code was cut-n-pasted. All I did was take out one Char rtn and modify the Select code as you noticed. I found another site that had other info on fixing an error, but I can't access it thru my work computer. It's not really a big deal to have the exact changes listed in my updated report, but it would be handy if I can get it figured out. I tried entering a date and got the same result, only logs a change, not what changed. I've thought about dumping some of my tables, etc., to an empty db and uploading it to have someone help. Is this plausible???

Thanx for helping me out on this.

Attached is an audit trail demo in which I have modifed the code to eliminate the first carriage return. Try this demo then copy and paste the code into a module in your database. The only thing you need to change is the Select Case for the types of controls you want checked by the code.

If your db still does not work with this code then post you db (compact, zip and 500k max. size) and I will take a look at it.

hth,
Jack

Okay, I'll give that a shot and see what happens.

Good luck!

Jack