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

No Go Jack. Still only logs a change, not what changed. Take a peek?

You have this line of code in your module:

If C.Name <> "Updates" Then GoTo TryNextC

It should read:

If C.Name <> "Updates" Then

You will need to add another End If before the End Select statment as well. Try that as I think it will now work as advertised, but I won't swear to it. It appears to be working for me...

Your table in NOT normalized and I STRONGLY suggest you normalize your structure before you go any further with your database.

hth,
Jack

See? I knew it was something simple I couldn't see. The code you sent had it written with the GoTo after the Then statement. And it had = instead of <>. But it works now. You're the man!

Normalization....I've seen it mentioned before but obv. haven't done it. It doesn't damage the db in any way, right? It just basically makes it run smarter?

Thanx for helping me out with this!!!!!!

If you ever want to gather data you will be fighting a losing battle. And you are right, what you have does not 'damage' the db, but just makes it very difficult to update, query the data, etc. As a simple example, what if you want to add another checkbox to the table? To do it now means modifying the table, modifying the form and modifying any queries and reports that are involved. If your data is normalized and your client needs another check box they open a form, add it and they are good to go...

My philosophy is that if it is worth doing it is worth doing right so I strongly suggest you normalize you structure...

Just my 3 cents worth...

Jack

Okay, one (?) more question on my report. The log says "Admin" regardless of who makes the input. I want to add a step that actually tells the db who is using it when making changes. Should I make a popup form that asks for the name or initials of the current user and add that to the Updates module (and how)?

You want a login form as the first thing that opens when the user starts the db. I would suggest you save the information in a table and then look to that table when you want to see who updated the data. You can use DLookup() in the AuditTrail function to get the name and replace the Admin that is there now. When you close the db delete the name in the table or overwrite it the next time the db is opened....

hth,
Jack

Jack,

I normalised my table now I get this when I make an update:
Run-time error '3251':
Operation is not supported for this type of object.

Code hilites this line:
If C.Value <> C.OldValue Then

HELP!?!?

Hmmm. I do not know why you are getting this error. I would suggest you compile your code and then do a compact and repair. If you get an error when compiling the code that might help you to decipher what is wrong. Try the compile and compact and see if that fixes the problem. The other thing you might try is to create a new, blank db and import everything into the new db and see if that fixes the problem...

Jack

Okay, I complied with all suggestions to no effect. Everything works fine in the db's original state. I'd still like to make the db "right" by putting data into different tables. If the normalization process gives me this error, is there anything else to try besides creating the db from scrath and reentering all my data after I recreate my forms, etc.???

Thanx again.

Since you have normalized your data then there may be other problems that was caused by this. Moving data to new tables and making other changes can certainly create problems as not all the changes you make are 'cascaded' through the db. At this point it might be less time consuming to copy your tables, without their data, into a new, blank db and then create your new forms and once they are done you can enter a few records and see how things are working. If all is well then you can take the time to reenter the data into the new db.

Jack