Updating in an Append Action Query (Access 2000)

[traceyr]

I have a simple Append query which adds records to table3 when it finds matching records in table1 and table2.

In table1 and table2 there is a boolean field 'Matched' which I need to set to True whenever a match is found (to prevent processing a second time).

Is it possible to do this?
On the assumption that it *should* work I have adopted the following approach:

I have set a query field to
expr: SetMatched([table1].[AutonumId],[table2].[AutonumId])
to call the function, which is in the General module.

(code generalised)
Function SetMatched(lngTable1Id As Long, lngTable2Id As Long)
'
' when a match is found call this function
' to set the Matched boolean value to True
' in both tables
'
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngTab1 As Long
Dim lngTab2 As Long

lngTab1 = lngTable1Id
lngTab2 = lngTable2Id

Set db = CurrentDb

' Update Table1
Set rst = CurrentDb().OpenRecordset("Table1", dbOpenTable)
rst.Index = "AutonumId"
rst.Seek "=", lngTab1

rst![Matched] = True

rst.Update
rst.Close
Set rst = Nothing

'same repeated for the second table

End Function
===============
The query executes OK, no errors, but the function isn't called.
Any pointers to how this could be made to work OR to an alternative approach would be much appreciated.

 

[rubbernilly]

Select the records that match from t1 and t2 based on the criteria that would match them. Make sure you include the primary key field from both tables.

Assign a DAO.recordset equal to that recordset, and step through it in a loop.

For each step of the loop
add your record to Table3
run an update query through DoCmd.RunSQL where you set the Matched field of Table1 to -1 (selected)
run an update query through DoCmd.RunSQL where you set the Matched field of Table2 to -1 (selected)

Build your SQL dynically based on the primary key field in your original recordset, and it will update that record in the two tables.

HTH

 

[traceyr]

Thanks for your suggestion and for taking the time to reply.

I didn't explain that this is for my use only, i.e. I am running this 'by hand' from within Access, not via a form or application. It doesn't have to be beautiful, just do the job.

I was hoping to simply start the append query and have it do the record selection and looping through the table and calling the SetMatch function for each match and doing the update. Is that possible?

 

[rubbernilly]

To my knowledge, append queries do not "step through" the recordset. If you want to step through the recordset like that, you have to do it through code.

The sort of code I suggested would do that, even if you threw it away afterwards.

Maybe someone else has a better solution, but you could also have 3 action queries.

1 - takes matches from t1 and t2 and creates a record in t3
2 - finds matches in t1 and t3 where the t1.Matched flag is false, and updates the Matched flag
3 - finds matches in t2 and t3 where the t2.matched flag is false, and updates the Matched flag

It ain't pretty, but it will do what you need.

 

[traceyr]

That's just what I need. Many thanks indeed. I'll do it now!