Comparing 2 table in a database

[derekstewart74]

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 (taskmaster). 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. I also use the 2nd table as a change log, capturing all
changes. Because there are 30 fields, I need an efficient way to
detemine which of the field(s) changed. I created a query
(mostrecentupdate) which pull the most recent change to the table. 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.
Below is what I have so far. I have it attached to a button to figure
it out, but will change that to after update. It is hanging on the fld
line. Do I need to declare the fld variable? I'm pretty new to this
stuff and looking for suggestions. Thanks. D

Private Sub Command147_Click()
Dim rsA As DAO.Recordset
Dim rsb As DAO.Recordset

Set rsA = CurrentDb.OpenRecordset("Select * From taskmaster Where ID="
_
& Me.ID)
Set rsb = CurrentDb.OpenRecordset("Select * From mostrecentupdate 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

End Sub

 

[Bob Hairgrove]

It is hanging on the fld
line. Do I need to declare the fld variable?

If you have set the option to require predeclaration of your VBA
variables (which puts the statement "Option Explicit" at the top of
your module), the yes, you need to declare fld as type Variant.

Also, it is a good idea to use a variable of type Field instead of the
Variant variable (maybe it is even required?), i.e.:

Dim fld As Variant
Dim fldObj As DAO.Field
'... etc.
For Each fld In rsA.Fields
Set fldObj = fld
If fldObj <> rsb.Fields(fldObj.Name) Then
Me(fldObj.Name).BackColor = vbRed
End If
Set fldObj = Nothing
Next

Declaring "Option Explicit", BTW, is a good thing to do!

 

[derekstewart74]

Thanks for your help. I've run into a few issues that I fixed. One of
the tables was missing a field the other one had which caused problems.
Now, i'm getting a "object does not support this property or method"
error and the debugger is flagging the "ctl.BackColor = vbRed" line.
Any thoughts? Thanks. D

 

[derekstewart74]

They are quite a few that could change including text, memo, check,
etc. Would it be easier to change the font color to red? Would that
work with check boxes? Thanks. Derek