Loop through two similar tables highlight field value changes in rows.

[algomez2011]

First post. Hi all!

I have two tables both with identical column structure (110 columns). TableA has old records and TableB CAN have updated records or not. They have the same key in both tables. Field types are the same in both tables and there are null values in both.


TableA:
ID | F1 | F2 |.....| F110 |
1 | A | B |.....| C |

TableB:
ID | F1 | F2 |.....| F110 |
1 | Z | X |.....| C |

Output needed:

TableC:
ID | F1 | F2 |.....| F110 |
1 | A | B |.....| C |
1 | Z | X |.....| C |

So show the same "ID" for old and updated record and then highlight the fields that changed as I export this to excel.

I'm thinking worrying about creating TableC first then tackling the export/highlight thing after would probably be wiser.

I'm pretty much learning as I go.

Help! Thanks

 

[algomez2011]

Success!

Now on to the reporting/excel export. I'll poke at this for a while.

MajP, I can't thank you enough!

 

[MajP]

There is lots of code on the web for transferring a spread sheet to excel and getting a reference of the excel worksheet. Google something like "controlling excel from access". Once you have a variable of the new workseet it should be relatively simple.
0) export to excel and create an excel object. Set a variable equal the workseet.
1)create a recordset of your tblChanges.
2)loop the recordset
have a variable for the id and for the fld position.
3)build a function that when you pass it an ID it does a search for the row and returns the row number
4)build a function that you pass a field position it returns a column position. May likely have to just add or subtract one.
5)create a range using the row and column
6) hilite the row and column
keep looping.

There is lots of excel code on the web, probably more than Access because more users. You should be able to get good examples without writing a lot of new stuff.