Compare queries instead of tables 1

[jcw5107]

Below is an example of code I am working with. It compares to tables and writes the differences out to a 3rd table.
The problem I'm having is when runs thru "For Each fld In tdf.Fields" part - it compares every single field in each table - I don't want it to. Is there a way to use select queries with just the fields I need for the comparison?? Maybe using Query Defs...??? I'm not sure..!! I just don't need the comparison for every field...
Any suggestions or examples..??
Thanks in advance..!!
jcw5107

Sub Test()
Call CompareTables("DocMaster", "TaskNo", "qryTaskNoBase", "qryTaskVarying")
End Sub

Sub CompareTables(BaseTable As String, PrimaryKeyField As String, BaseTableQuery As String, VaryingTableQuery As String)
On Error GoTo Err_CompareTables
Dim db As DAO.Database
Dim rstBase As DAO.Recordset
Dim rstVarying As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim FieldChanged As Boolean
Dim ErrorMessage As String
Dim ErrorMessage1 As String
Set db = CurrentDb
Set rstBase = db.OpenRecordset(BaseTableQuery)
Set rstVarying = db.OpenRecordset(VaryingTableQuery)
Set tdf = db.TableDefs(BaseTable)
rstBase.MoveFirst
rstVarying.MoveFirst
Do Until rstBase.EOF
For Each fld In tdf.Fields
If Nz(rstBase(fld.Name)) <> Nz(rstVarying(fld.Name)) Then
ErrorMessage = "" & fld.Properties("Caption") & " has changed from " & Nz(rstBase(fld.Name), "NO ENTRY") & " to " & Nz(rstVarying(fld.Name), "NO ENTRY")
ErrorMessage1 = "U"
db.Execute "INSERT INTO TaskNoChanges (TaskNo, ChangeDescription, StatusCode) SELECT (" & rstBase(PrimaryKeyField) & "), ( '" & ErrorMessage & "' ), ( '" & ErrorMessage1 & "' )"
FieldChanged = True
End If
Next fld
rstBase.MoveNext
rstVarying.MoveNext
Loop
End Sub

 

[Remou]

Yes. In fact you imply that you are using two saved queries called "qryTaskNoBase" and "qryTaskVarying".

 

[jcw5107]

Well its just that when it runs thru the fields its based on: Set tdf = db.TableDefs(BaseTable)
I just don't know how to get it to look at the queries...
Do I just switch everything over to Query Defs..??

Thanks,
jcw5107

 

[Remou]

Ok.

<...>
'Set tdf = db.TableDefs(BaseTable)
rstBase.MoveFirst
rstVarying.MoveFirst
Do Until rstBase.EOF
        For Each fld In rstBase.Fields
<...>

 

[jcw5107]

Sweet..!!
Thank you, I was able to utilize what you suggested..
It was that simple after all, heh..??
Thanks..!!

jcw5107

 

[jcw5107]

Remou,

I'm tryin' to take this one step further....
How can I update/edit each field in rstBase when the fields are different between rstBase & rstVarying?

Right now I'm working with something like:
rstBase.Edit
rstBase!FleetID = rstVarying!FleetID
rstBase!DocNo = rstVarying!DocNo
rstBase!Rev = rstVarying!Rev
rstBase.Update

Is there a way to do edit/update the fields without listing every single field??
Something like:
rstBase.Edit
Nz(rstBase(fld.Name))= Nz(rstVarying(fld.Name))
rstBase.Update

I sure do appreciate your help...!!
jcw5107

 

[Remou]

That would be:

[tt]rstBase(fld.Name)= Nz(rstVarying(fld.Name))[/tt]

You might be better off looking at a query.