Yes. These are fun types of vba code to write for. What you'll need to do is essentially open up both recordsets in code (ie. identify one of them as rx and the other as ry.) You may also need to open up another recordset depending on how you set it up (there are several different approaches to how this is done.)
Setting up the looping order and opening the recordsets correctly will be the tricky part (I've had to often do double-loops in these kind of situations where you need to add unmatched records to table A or B and vice versa.) Once the loops are setup correctly, keep in mind that you can also open up another recordset against the 2nd table (I usually create separate functions for these) which does a quick test to see if the matching record exists and if not, it then adds it. You may want to simply do your test/update in the separate function apart from the main loops. It's typically nice to keep these type of routines separate from your larger looping code but doesn't always work to your advantage.
You also have to be VERY careful on knowing what recordsets are open and which ones need to be closed and when!! I can't stress this enough. It would be very nasty if you left recordsets open since you're probably going to be opening a few recordsets in the routine and must keep them open only as long as you need them and then close them. I would avoid opening multiple recordsets simultaneously against the same table as this can cause problems but I have done this successfully. Typically you have problems in code when doing this.
Then you simply incorporate the logic into your looping code. You can utilize rx!SomeField = ry!SomeField and other code like that to compare the 2 values if you setup your looping code to cycle through both tables.
An example of quickly opening the 2nd table to see if it has a matching ID record is a function such as this (ADO example):
Function UpdateTableB(RecID as variant, vSomeValue1, vSomeValue2, vSomeValue3)
if isnull(RecID) then exit function
dim rs as adodb.recordset
set rs = new adodb.recordset
dim strSQL as string
strSQL = "Select * from myTableA where RecordID = " & RecID & ""
rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
if rs.eof and rs.bof then
rs!SomeField = vSomeValue1
rs!SomeField2 = vSomeValue2
rs!SomeField3 = vSomeValue3
set rs = nothing
Note though in the above, I'm passing the autonumber identifier and the values to update. If you need to do this with both recordsets open (ie. rs!SomeField = ry!SomeField), you'll need to put this kind of code in your looping routine. It depends on how you want to do it and how many variables you need to pass.
Last edited by pkstormy; 11-17-09 at 22:41.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)