By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

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

P: 23
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.
Attached Files
File Type: zip Example1.zip (14.9 KB, 96 views)
Mar 6 '10 #1

✓ answered by 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.


Expand|Select|Wrap|Line Numbers
  1. Function kp()
  2.  
  3. Dim updaterec As Boolean
  4. Dim db As dao.Database
  5. Dim rs As dao.Recordset
  6. Dim rs2 As dao.Recordset
  7.  
  8. Dim Ctr As Integer
  9.  
  10. Set db = CurrentDb
  11. Set rs = db.OpenRecordset("SELECT " & _
  12.                           "fldPio, fldFam, fldSer, fldTra, fldInt, fldupdated " & _
  13.                           "FROM table1", dbOpenDynaset)
  14. Set rs2 = db.OpenRecordset("SELECT " & _
  15.                            "fldPio, fldFam, fldSer, fldTra, fldInt " & _
  16.                            "FROM mastertable", dbOpenDynaset)
  17.  
  18. rs.MoveFirst
  19. rs2.MoveFirst
  20.  
  21. With rs2
  22.  
  23.    Do Until .EOF
  24.       Do Until rs.EOF
  25.  
  26.       If rs!fldupdated = 0 Then
  27.  
  28.          updaterec = True
  29.  
  30.             For Ctr = 0 To 4
  31.                If Not IsNull(.Fields(Ctr)) Then
  32.                   If .Fields(Ctr) <> rs.Fields(Ctr) Then
  33.                      updaterec = False
  34.                         Exit For
  35.                   End If
  36.                End If
  37.             Next Ctr
  38.  
  39.          If updaterec = True Then
  40.             rs.Edit
  41.                'rs!fldprod = IIf(IsNull(!fldprod), Null, !fldprod)
  42.                rs!fldPio = IIf(IsNull(!fldPio), Null, !fldPio)
  43.                rs!fldFam = IIf(IsNull(!fldFam), Null, !fldFam)
  44.                rs!fldSer = IIf(IsNull(!fldSer), Null, !fldSer)
  45.                rs!fldTra = IIf(IsNull(!fldTra), Null, !fldTra)
  46.                rs!fldInt = IIf(IsNull(!fldInt), Null, !fldInt)
  47.                rs!fldupdated = -1
  48.             rs.Update
  49.          End If
  50.  
  51.       End If
  52.          rs.MoveNext
  53.  
  54.       Loop
  55.            .MoveNext
  56.          rs.MoveFirst
  57.    Loop
  58.  
  59. End With
  60.  
  61. rs.Close
  62. rs2.Close
  63.  
  64. Set db = Nothing
  65. Set rs = Nothing
  66. Set rs2 = Nothing
  67.  
  68. MsgBox "Done!"
  69. End Function
  70.  

Share this Question
Share on Google+
6 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Mar 6 '10 #2

P: 23
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.


Expand|Select|Wrap|Line Numbers
  1. Function kp()
  2.  
  3. Dim updaterec As Boolean
  4. Dim db As dao.Database
  5. Dim rs As dao.Recordset
  6. Dim rs2 As dao.Recordset
  7.  
  8. Dim Ctr As Integer
  9.  
  10. Set db = CurrentDb
  11. Set rs = db.OpenRecordset("SELECT " & _
  12.                           "fldPio, fldFam, fldSer, fldTra, fldInt, fldupdated " & _
  13.                           "FROM table1", dbOpenDynaset)
  14. Set rs2 = db.OpenRecordset("SELECT " & _
  15.                            "fldPio, fldFam, fldSer, fldTra, fldInt " & _
  16.                            "FROM mastertable", dbOpenDynaset)
  17.  
  18. rs.MoveFirst
  19. rs2.MoveFirst
  20.  
  21. With rs2
  22.  
  23.    Do Until .EOF
  24.       Do Until rs.EOF
  25.  
  26.       If rs!fldupdated = 0 Then
  27.  
  28.          updaterec = True
  29.  
  30.             For Ctr = 0 To 4
  31.                If Not IsNull(.Fields(Ctr)) Then
  32.                   If .Fields(Ctr) <> rs.Fields(Ctr) Then
  33.                      updaterec = False
  34.                         Exit For
  35.                   End If
  36.                End If
  37.             Next Ctr
  38.  
  39.          If updaterec = True Then
  40.             rs.Edit
  41.                'rs!fldprod = IIf(IsNull(!fldprod), Null, !fldprod)
  42.                rs!fldPio = IIf(IsNull(!fldPio), Null, !fldPio)
  43.                rs!fldFam = IIf(IsNull(!fldFam), Null, !fldFam)
  44.                rs!fldSer = IIf(IsNull(!fldSer), Null, !fldSer)
  45.                rs!fldTra = IIf(IsNull(!fldTra), Null, !fldTra)
  46.                rs!fldInt = IIf(IsNull(!fldInt), Null, !fldInt)
  47.                rs!fldupdated = -1
  48.             rs.Update
  49.          End If
  50.  
  51.       End If
  52.          rs.MoveNext
  53.  
  54.       Loop
  55.            .MoveNext
  56.          rs.MoveFirst
  57.    Loop
  58.  
  59. End With
  60.  
  61. rs.Close
  62. rs2.Close
  63.  
  64. Set db = Nothing
  65. Set rs = Nothing
  66. Set rs2 = Nothing
  67.  
  68. MsgBox "Done!"
  69. End Function
  70.  
Mar 6 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im glad you solved your problem.

We also appreciate that you take the time to return, and properly close your question. :)
Mar 6 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
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?
Mar 7 '10 #5

P: 23
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. =)
Mar 8 '10 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 10 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.