Update fields in a table on data entry in a second with VBA

[MrMode]

I would like to update 3 fields in TblA, through 2 steps when a date in the future is entered in to a field in a second table called ProcessBase. The two tables have the same unique reference ESPID.

TblA ProcessBase

ESPID ESPID
RAT CallBackDate
RAT2
AgentID

I would like to do this through VBA After Update rather than having to manually run several queries.

I am stuck. I am not clear how to even start to do this...

I understand that I have to first capture the ESPID of the relevant records which I have been able to do with a query:

SELECT ProcessBase.ESPID
FROM ProcessBase
GROUP BY ProcessBase.ESPID, Format(Date(),"yymm"), Format(processbase.CallBackDate,"yymm")
HAVING (((Format(processbase.CallBackDate,"yymm"))>Format(Date(),"yymm")));

If this were to be behind a control on a form, then I could simply put something like:

If me.callbackdate > Format(Date(),"yymm") Then ...

But this is where I get stuck, how do I update the fields on TblA using the ESPID of this record on ProcessBase tbl?

Then I want to update TblA.RAT2 with the value in TblA.RAT (it is important that this is done first before any changes are made to RAT value.)

Then I want to change TblA.RAT and TblA.AgentID to specific values (TblA.RAT = Unallocated, TblA.AgentID = "12")

Any help would be gratefully appreciated

 

[easyit]

Hi,

Would running several queries in VBA be sufficient? If so, create the necessary queries, the execute them in the right order.

docmd.QpenqQuery ("queryname")

Maarten

EasyIT

"Do you think that’s air you're breathing?

 

[MrMode]

It is worth a try, I will let you know if the wheels fall off and I get stuck.

Thanks