Really stuck on how to retrieve previous record (1 Viewer)

connie · Aug 7, 2009

Hi...I really need help!

I have an audit trail running that keeps a change log in a single table using the code:

Code:

Private Sub Form_AfterUpdate()
Dim db As Database
 
    Set db = CurrentDb
    db.Execute "INSERT INTO [tblDatabaseChanges] " _
    & " SELECT * FROM [tblEquipmentDatabase] WHERE " _
    & " [tblEquipmentDatabase].[RecordID]=" & Me![RecordID] & ";"
    Set db = Nothing
 
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
Dim blnCheckDiff As Boolean
Dim ctl As Control
blnCheckDiff = False
For Each ctl In Me.Controls
    If ctl.Tag = "Check" And ctl.Value <> ctl.OldValue Then
        blnCheckDiff = True
        Me!DateofChange = Date
    End If
Next
If blnCheckDiff Then
    [txtTime] = Now()
    [txtuser] = [txtCurrentUser]
Else
    Cancel = True
End If
End Sub

Everything runs fine, but now the boss wants a report that displays the information like this:

Record # Date of Change Reason for Change Current Values Old Values
1 (some date) (some reason) QTY: (new qty here) QTY: (old qty here)
2 (some date) (some reason) PRICe: (new price here) PRICE: (old price here

Each specific item has it's own record #, which repeats in the audit table when multiple changes are made to the same item. Since the audit log table stores all new values in one line at a time, the previous value is actually the previous row. Wouldn't I have to be able to somehow pull the 2nd to last version of each record # (ie, the 2nd to last row for that record # in the table) in order to put the new and previous values side-by-side like that? And how is that even done?

Sorry if this description was long, but I'm confused and frustrated and really need help. Thank you!!

boblarson · Aug 7, 2009

Actually the controls on a form have what is known as .OldValue which can be accessed in the BeforeUpdate event of the form. Your audit trail code should run in the form's BEFORE UPDATE event (just after any validation which could cancel the update) and therefore you could use the

Me.YourControlName.OldValue

RuralGuy · Aug 7, 2009

You may find this link useful.

connie · Aug 7, 2009

Re: Bob's answer, Thanks!, but how would I use that code to generate a field in the report? In design view of the report can I set the actual Control Source to =Me.ctlItemQty.OldValue ? Or make the report in VBA somehow?

Re: Rural Guy's answer, thank you, I checked that out but actually that result is exactly how the current audit log table I have looks...my Record# repeats as the "CarNum" in your example does for different entries. My question would be how to take that query result and list the different variables (new vs. previous) side by side instead of vertically...

boblarson · Aug 7, 2009

connie said:
Re: Bob's answer, Thanks!, but how would I use that code to generate a field in the report? In design view of the report can I set the actual Control Source to =Me.ctlItemQty.OldValue ? Or make the report in VBA somehow?

No, I meant you move your audit trail into the Before Update event and not the After Update event and you would have a field in the table that captures the .OldValue at that time. Then you just include that field in any reports.

But I guess I'm confused as to what is being done, and when, and what it looks like. Perhaps a few screenshots might help.

RuralGuy · Aug 7, 2009

connie said:
Re: Rural Guy's answer, thank you, I checked that out but actually that result is exactly how the current audit log table I have looks...my Record# repeats as the "CarNum" in your example does for different entries. My question would be how to take that query result and list the different variables (new vs. previous) side by side instead of vertically...

...and yet Paul was able to come up with the previous odometer reading in his query.

connie · Aug 10, 2009

RuralGuy said:
...and yet Paul was able to come up with the previous odometer reading in his query.

I am able to come up with the previous values, what I'm having trouble with is getting them into the report the way they want it. Maybe I'm not explaining this right.

table1.jpg is my tblDatabaseChanges.

table2.jpg is how the report is supposed to be formatted with that information.

There may be an easy answer to this and I've just been staring at it for too long

Thank you Bob I will try to move my AfterUpdate events into my BeforeUpdate in the meantime...although I have other code in there so I hope it works...

connie · Aug 10, 2009

Well, I'm trying to set this up in the AfterUpdate event as follows:

Code:

Private Sub Form_AfterUpdate()
        [tblDatabaseChanges].[Item_Qty_Old] = Me.ctlItemQty.OldValue
        [tblDatabaseChanges].[Unit_Cost_Old] = Me.ctlUnitCost.OldValue
        [tblDatabaseChanges].[Ext_Cost_Old] = Me.ctlExtCost.OldValue
        [tblDatabaseChanges].[Manufacturer_Old] = Me.ctlManufacturer.OldValue
        [tblDatabaseChanges].[Model_Old] = Me.ctlModel.OldValue
 
End Sub

Meaning I'm trying to tell it to put the old version of each of those fields into tblDatabaseChanges, and I've set up a new field for the old values like Item_Qty_Old, etc.

When I debug>Compile all looks good but when I try to update the form I get "Run-time error '2465': Microsoft Access can't find the field 'l' referred to in your expression." Under debug it then highlights the following line:

[tblDatabaseChanges].[Item_Qty_Old] = Me.ctlItemQty.OldValue

I'm not very fluent in VBA so I may be writing this wrong?

(PS all of your help has been and is GREATLY appreciated!!)

connie · Aug 10, 2009

Anyone out there?

boblarson · Aug 10, 2009

you can't tell it to update a table like that, you would need to use a recordset or execute a SQL Statement like how you originally had it in the AfterUpdate code you posted in the first post.

Sort of like:

Code:

Dim strSQL As String

strSQL = "Insert INTO tblDatabaseChanges (Item_Qty_Old, Unit_Cost_Old, Ext_Cost_Old, Manufacturer_Old, Model_Old) VALUES (" & Me.ctlItemQty.OldValue & ", " & Me.ctlUnitCost.OldValue & ", " & Me.ctlExtCost.OldValue & ", " & Me.ctlModel.OldValue & ")" 

CurrentDB.Execute strSQL, dbFailOnError

Scooterbug · Aug 10, 2009

Well, the syntax for what you are tying to do is wrong from what I can see. Is there a form open with bound fields for the data you are trying to save? I'm guessing not, so the easiest way would be to open a recordset for the table and to add a new record that way.

Code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
 
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblDatabaseChanges")
    
With rs
      .AddNew
      .Fields("Item_Qty_Old") = Me.ctlItemQty.OldValue
      .Fields("Unit_Cost_Old") = Me.ctlUnitCost.OldValue
      .Fields("Ext_Cost_Old") =  Me.ctlExtCost.OldValue
      .Fields("Manufacturer_Old") = Me.ctlManufacturer.OldValue
      .Fields("Model_Old") = Me.ctlModel.OldValue
      .Update
      .Close
End With
 
  Set db = Nothing
  Set rs = Nothing

That code will put in your old values into your change table.

connie · Aug 10, 2009

Scooterbug said:
Well, the syntax for what you are tying to do is wrong from what I can see. Is there a form open with bound fields for the data you are trying to save? I'm guessing not, so the easiest way would be to open a recordset for the table and to add a new record that way.

The form is bound to a different table, the tblEquipmentDatabase, where the regular data is kept. tblDatabaseChanges, which I want to write to, is just for the changed values.

Will your code still work even though the form is bound elsewhere? I'm going to try it right now. Thanks!!!

connie · Aug 10, 2009

Actually I'll try both...hopefully one of them works...you guys are the best!!!

Scooterbug · Aug 10, 2009

Both bob's method and mine do not require a form bound to your change table to be open. Data is put directly into the table without the need for a form.

connie · Aug 10, 2009

Yikes. Okay, when I try Bob's, I get the following:

Run-time error '3346': Number of query values and destination fields are not the same.

and the debugger highlights this line:
CurrentDb.Execute strSQL, dbFailOnError

When I tried Scooter's, it works!! - BUT - it stops recording all of the new values into that table and just records the old values, ignoring my BeforeUpdate events and only filling in the fields which we just added.

I'm confused...

Scooterbug · Aug 10, 2009

connie said:
Yikes. Okay, when I try Bob's, I get the following:

Run-time error '3346': Number of query values and destination fields are not the same.

and the debugger highlights this line:
CurrentDb.Execute strSQL, dbFailOnError

There is no value being entered for the manufacturer...try this:

Code:

Dim strSQL As String

strSQL = "Insert INTO tblDatabaseChanges (Item_Qty_Old, Unit_Cost_Old, Ext_Cost_Old, Manufacturer_Old, Model_Old) VALUES (" & Me.ctlItemQty.OldValue & ", " & Me.ctlUnitCost.OldValue & ", " & Me.ctlExtCost.OldValue & ", " [COLOR=red]& me.ctlManufacturer.oldValue & ","[/COLOR] & Me.ctlModel.OldValue & ")"

CurrentDB.Execute strSQL, dbFailOnError

When I tried Scooter's, it works!! - BUT - it stops recording all of the new values into that table and just records the old values, ignoring my BeforeUpdate events and only filling in the fields which we just added.

I'm confused...

I guess I'm a bit confused too. It was my understanding that you were attempting to record the old values into a separate table than the changed value. Hence the code works as it should.

What code do you have firing off on the BeforeUpdate event?

boblarson · Aug 10, 2009

connie said:
Yikes. Okay, when I try Bob's, I get the following:

Run-time error '3346': Number of query values and destination fields are not the same.

and the debugger highlights this line:
CurrentDb.Execute strSQL, dbFailOnError

I'd rather go with ScooterBug's version myself than try to fix the SQL on this one.

When I tried Scooter's, it works!! - BUT - it stops recording all of the new values into that table and just records the old values, ignoring my BeforeUpdate events and only filling in the fields which we just added.

Of course because you have to add the new stuff too. He gave you what you need for the old stuff but you also need to add the red parts:

Code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
 
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblDatabaseChanges")
    
With rs
      .AddNew
      .Fields("Item_Qty_Old") = Me.ctlItemQty.OldValue
[COLOR="red"]      .Fields("Item_Qty") = Me.ctlItemQty[/COLOR]
      .Fields("Unit_Cost_Old") = Me.ctlUnitCost.OldValue
[COLOR="red"]      .Fields("Unit_Cost") = Me.ctlUnitCost[/COLOR]
      .Fields("Ext_Cost_Old") =  Me.ctlExtCost.OldValue
[COLOR="red"]      .Fields("Ext_Cost") = Me.ctlExtCost[/COLOR]
      .Fields("Manufacturer_Old") = Me.ctlManufacturer.OldValue
[COLOR="red"]      .Fields("Manufacturer") = Me.ctlManufacturer[/COLOR]
      .Fields("Model_Old") = Me.ctlModel.OldValue
[COLOR="Red"]      .Fields("Model") = Me.ctlModel[/COLOR]
      .Update
      .Close
End With
 
  Set db = Nothing
  Set rs = Nothing

connie · Aug 10, 2009

Well I already had this in the BeforeUpdate:

Code:

Dim db As Database
    Set db = CurrentDb
    db.Execute "INSERT INTO [tblDatabaseChanges] " _
    & " SELECT * FROM [tblEquipmentDatabase] WHERE " _
    & " [tblEquipmentDatabase].[RecordID]=" & Me![RecordID] & ";"
    Set db = Nothing

And it was working, but when I put Scooter's code in the AfterUpdate it for some reason cancels out the other command. But I think I'm beginning to understand...if I want the old values to go in an entirely different table, I should use Scooter's method but change the table name to a new one, but if I want it all to dump into tblDatabaseChanges I can add the red parts and both old and new values will be recorded at once in that same table.

It does seem to make more sense to have 2 separate tables...I'm going to play around with it both ways and let you know how it goes. THANK YOU x's 1,000,000!

The_Doc_Man · Aug 11, 2009

Here is the real problem with all questions like this. It is one of the "Old Programmer's Rules." Access won't tell you anything you didn't tell it first.

Now a little secret - tables that don't have primary keys have no inherent order. There IS no previous record unless you have a way to impose an order via a query or primary key.

In practical terms, it means that if you needed to show an order to the audit trail, you needed a field in the audit trail to track the order. I use a date/time field for my audits, but there are other methods. Autonumber isn't always good because it has been known to get confused.

When building your audit record, one thing you could try is to just find DMax of a record number and add one to that to become the next record number. If you are already in VBA context anyway, that's not that hard.

connie · Aug 18, 2009

Scooter's code (with Bob's edits) worked perfectly. Thank you all!

Really stuck on how to retrieve previous record (1 Viewer)

Registered User.

Smeghead

AWF VIP

Registered User.

Smeghead

AWF VIP

Registered User.

Attachments

Registered User.

Registered User.

Smeghead

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Smeghead

Registered User.

Immoderate Moderator

Registered User.

Similar threads

Users who are viewing this thread