Comparing two database tables

[derekstewart]

I have and Access Database I use for Job Requests. There are two main tables. A requestor enters data in a form that ties to the 30 different fields in the first table. The requests often change midstream, so I have an update button tied to a macro which appends the old data prior to an update to a second table with the same fields. Because there are 30 fields, I need an efficient way to detemine which of the field(s) changed. Ideally I would like to highlight changes (differences between the old and new table) in red so they would be easily identified. The first step however is comparing the 30 fields and isolating the differences. Any thoughts on a good way to do this? Thanks. D

 

[Remou]

How about looping through each record and comparing the fields?

'Typed, not tested
'Numeric ID
Set rsA=CurrentDB.OpenRecordset("Select * From tblA Where ID=" _
& Me.ID)
Set rsB=CurrentDB.OpenRecordset("Select * From tblB Where ID=" _
& Me.ID)

For Each fld In rsA.Fields
    If fld <> rsB.Fields(fld.Name) Then
       Me(fld.Name).Backcolor=vbRed
    End If
Next

Or there abouts.

 

[derekstewart]

I'm not too savy with coding but i do see what you are doing . Do I need to declare rsa and rsb? I reworked things a bit too. I'm comparing the table against a query that pulls data from table b. This table also functions as a change log, so making it a query will allow it to only compare the most recent changes. Do I just select * from queryname in the second step? How is this code called? From the form? Ideally after the update button is clicked the code would execute. How do I do this if the onclick command is already executing an append macro? Thanks. D

 

[Remou]

You can declare rsA and rsB as DAO.Recordsets:

Dim rsA As DAO.Recordset

Make sure you have a reference to the Microsoft DAO 3.x Object Library.

You can add the code after the append macro, if you wish. It does not update anything, but it is still wise to backup, as altering forms can sometimes cause problems.