Access 2010 After Insert data macro to update another table

Question

I have an Access DB with 3 tables.

Table 1
Appointments
Fields with Data that is not used anywhere else, but some of the information is inserted into 3 with a after insert Script.

Table 2
Usage Data of Salesmen
I need some of the fields from here and put them on Table 3 after the afterinsert-Script

example Row:

ID Date Month NT-Account Data
-- -------- ------ ---------- ---------
 1 1.1.1899 201409 Some User (Numbers) 

Table 3
Performance
(IDFS to the Appointment ID, Date of the Coaching, NT-Account, Personal Information)
(is from the insert Script and works well.

Now to the usage Data, it's daily and my seniors want to test the sum of this daily data with weekly usage, so i did a query which holds the information i want.

Agent Login Date Week Sum1 Sum2 Sum3
------ ---------- ------ ---- ---- ----
*user* 01.01.1991 199101 10 2 0

So basically we just download the Usage data from a big data collecting Program and it's giving us the Infos we need.

Now to my problem.

I made a AfterInsert Macro which will automatically put some of the Appointments data in it , but i need some information from a different table. How can i set the macro to execute a update or a script?

• Chris Bruhin: hope that helped :)

Answer 1

An "After Insert" data macro can certainly modify records in other tables. You can use the LookupRecord, EditRecord, and CreateRecord Data Blocks as required.

However, a data macro cannot run a VBA script. Your actions are limited to those available in the data macro environment itself.

• Chris Bruhin: what a drag, i have recently found out that you can start a query from vba, which seems like the most usufuel thing to do. Or i will convert it fully to vba and program the rest, would this be better?