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","fldP io","fldFam","f ldSer","fldTra" ,"fldInt". 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.