VBA to update matching fields from one table and set unmatching fields to null

[KPR1977]

In my Access Database in Table1, I have one record that looks something like this. (eg. "fldProd" represents field name, while "Apple" represents field value.)
fldProd - Apple (text value)
fldPIO - CF (text value)
fldFam - 1 (text value)
fldSer - 2 (text value)
fldTra - 3 (text value)
fldInt - LZ (text value)
fildQty - 5 (integer value)

The MasterTable looks like this.
fldProd - Apple
fldDesc - 3D All
fldPio - CF
fldFam - 1
fldSer - null
fldTra - 3
fldInt - null

So essentially I would need a VBA module the would match up the MasterTable fields from a record with corresponding fields in the record from Table1. Notice, "fldDesc" in MasterTable does not exist in Table1, so it wouldn't be apart of the code's matching process. For each record it will only attempt to match "fldProd","fldPio","fldFam","fldSer","fldTra","fld Int". So "fldQty" in Table1 would remain untouched as well.

Then the module will need to nullify fields in Table1 that appeared "null" in the MasterTable. So after the VBA module was executed, the record in Table1 would look like this.

fldProd - Apple (text value)
fldPIO - CF (text value)
fldFam - 1 (text value)
fldSer - null
fldTra - 3 (text value)
fldInt - null
fildQty - 5 (integer value)

Any of you experts have any ideas? I'm not a code writer, so I'm totally stuck.

I attached an excel file that illustrates what I'm trying to accomplish for your reference.

[TheSmileyCoder]

Could you attach your DB to this thread? Would make it alot easier to write the SQL statements.

Make a new DB, and just import those 2 tables into the new db, and attach that. That would give us only the information we need. Should be rather simple to write the 2 SQL statements to perform what you want.

[KPR1977]

Thanks so much for answering my post. =)

I've been trying to get this figured out for three days now, but I finally received an answer in another forum. Here's the function that will accomplish precisely what I need. The credit goes to ajetrumpet from Iowa City.

Function kp()
 
Dim updaterec As Boolean
Dim db As dao.Database
Dim rs As dao.Recordset
Dim rs2 As dao.Recordset
 
Dim Ctr As Integer
 
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT " & _
                          "fldPio, fldFam, fldSer, fldTra, fldInt, fldupdated " & _
                          "FROM table1", dbOpenDynaset)
Set rs2 = db.OpenRecordset("SELECT " & _
                           "fldPio, fldFam, fldSer, fldTra, fldInt " & _
                           "FROM mastertable", dbOpenDynaset)
 
rs.MoveFirst
rs2.MoveFirst
 
With rs2
 
   Do Until .EOF
      Do Until rs.EOF
 
      If rs!fldupdated = 0 Then
 
         updaterec = True
 
            For Ctr = 0 To 4
               If Not IsNull(.Fields(Ctr)) Then
                  If .Fields(Ctr) <> rs.Fields(Ctr) Then
                     updaterec = False
                        Exit For
                  End If
               End If
            Next Ctr
 
         If updaterec = True Then
            rs.Edit
               'rs!fldprod = IIf(IsNull(!fldprod), Null, !fldprod)
               rs!fldPio = IIf(IsNull(!fldPio), Null, !fldPio)
               rs!fldFam = IIf(IsNull(!fldFam), Null, !fldFam)
               rs!fldSer = IIf(IsNull(!fldSer), Null, !fldSer)
               rs!fldTra = IIf(IsNull(!fldTra), Null, !fldTra)
               rs!fldInt = IIf(IsNull(!fldInt), Null, !fldInt)
               rs!fldupdated = -1
            rs.Update
         End If
 
      End If
         rs.MoveNext
 
      Loop
           .MoveNext
         rs.MoveFirst
   Loop
 
End With
 
rs.Close
rs2.Close
 
Set db = Nothing
Set rs = Nothing
Set rs2 = Nothing
 
MsgBox "Done!"
End Function
 

[TheSmileyCoder]

Im glad you solved your problem.

We also appreciate that you take the time to return, and properly close your question. :)

[NeoPa]

Thanks for posting your answer.

I'm a little confused though. Mainly with the description of the problem. Is it an update of existing records that's required? Or an Append of new ones?

Either way, I would expect a simple UPDATE (or APPEND) SQL script to do the whole job for you in a single go. Am I missing something? Is it more complicated than I'm giving it credit for?

[KPR1977]

Hi NeoPa, it's somewhat more complicated. Basically I'm wanted to update existing records. I wasn't able to get an update query to accomplish fully what I needed. The above VBA module actually identifies which records did not have any match in the MasterTable, which is extremely pertinent to another process that I'm working with. Hope this makes sense. =)

[NeoPa]

It makes sense, but it doesn't really explain why an UPDATE query would not have done the job for you.

Never mind. I'm sure it's fine if you're happy.