Identify New Data on new record (multiple fields) to existing reco

[Guest]

I receive 2 monthly files on members that contain like 50 fields of data.
Each record indicates either New, Change or Term. So 1 member could have
multiple records coming on every file.
On a Change record, what I need to do is when I receive a record on a member
that already exists in my table, I want to check about 10 fields of data to
determine what the Change is.

I am looking for someone to suggest where I start with this. I know it can
be done. I am a fairly experienced user; just looking for some help getting
started. I am assuming this will require some programming, which is great if
anyone has any suggestions.

The database is '97.
THANKS!!

 

[Pieter Wijnen]

Assuming your import table layout is identical (except for the extra field,
Else list the fields ;-) )

Sub DebugChange
Dim Db AS DAO.Database
Dim RsL AS DAO.Recordset, RsI AS DAO.Recordset
Dim Fld AS DAO.Field
Set Db = Access.CurrentDB
Set RsI = Db.OpenRecordset("SELECT * FROM ImportTable WHERE
STATUS='Change'"),DAO.DbOpenSnapshot)
While Not RsI.EOF
Set RsL=Db.OpenRecordset("SELECT * FROM DataTable WHERE PK=" &
RsI.Fields("PK").Value
For Each Fld In RsI.Fields
If Fld.Name <> "STATUS" Then
If Access.Nz(Fld.Value,VBA.vbNullString ) <>
Access.Nz(RsI.Fields(Fld.Name).Value) Then
Debug.Print RsL.Fields("PK").Value,
RsL.Fields(Fld.Name).Value, Fld.Value
End If
End If
RsL.Close : Set RsL = Nothing
Next
RsI.MoveNext
Wend
RsI.Close : Set RsI = Nothing
Set Db = Nothing

End Sub

HTH

Pieter