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

[KP_SoCal]

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. Thanks for any help!!!

[ajetrumpet]

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.

I think the one thing you are missing here is the coverage of all the "what if's" in a situation like this. Before you run the code I've written, make a new field in table1, call it "fldupdated" and make it a BOOLEAN type. make the default value "false" in table design view.


after the new field, here's the solution to use if you read it the way I read it:

function kp()

dim ctr as integer
dim updaterec as boolean
dim db as dao.database
dim rs as dao.recordset
dim rs2 as dao.recordset

set db = currentdb
set rs = db.openrecordset("SELECT " & _
                          "fldProd, fldPio, fldFam, fldSer, fldTra, fldInt, fldupdated " & _
                          "FROM table1", dbopendynaset)
set rs2 = db.openrecordset("SELECT " & _
                           "fldProd, 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 5
               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

end function 

This of course says that more than one rec in table1 can be updated from the same record in mastertable. But then again, that's how I read it...

[KP_SoCal]

ajetrumpet, please extend your hand and give yourself a nice big firm handshake from myself! There were a couple of bugs (syntax error in queries and Ctr needed a Dim statement), but I was able to easily fix them and get the code running. Your code is BEAUTIFUL!!! Thank you, thank you, thank you my friend! It will be easy to manage should I need to add or remove additional fields. Very nice!!!

KP

[ajetrumpet]

ajetrumpet, please extend your hand and give yourself a nice big firm handshake from myself! There were a couple of bugs (syntax error in queries and Ctr needed a Dim statement), but I was able to easily fix them and get the code running. Your code is BEAUTIFUL!!! Thank you, thank you, thank you my friend! It will be easy to manage should I need to add or remove additional fields. Very nice!!!

KP

glad to help out. I fixed the errors too, just for good measure...

[KP_SoCal]

Ooopps, disregard this post. See my post directly below this one.

[KP_SoCal]

Hey Ajetrumpet, I'm not sure if you're still out there, but I ran into a snag. Your code runs great for a small amount of records, but for large amounts of records I get the following error:

"File sharing lock exceeded. Increase MaxLocksPerFile registry entry."

And here's the help description:

"You have exceed the maximum number of locks allowed on a recordset. This limit is specified by the MaxLocksPerFile setting in your system registry.  The default value is 9500, and can be changed either by editing the registry or with the SetOption method."

Is there any tweaking that can be done to the code to fix this? If not, is there a way to program the "SetOption method" in the VBA module to increase the MaxLocksPerFile setting?

I attached a sample database that demo's my error. Run the first module (modGo) and it will work with no problem due to the small amount of records in the source table. Run the second module (modNoGo) and the error message will fire due to its source file having a large amount of records.

Any ideas to fix this would be HUGELY appreciated! Thanks!!!