MS Access Forms - Deleting related records from other tables when deleting a record

[Loony064]

Hey all

I have another problem with my payroll database. firstly, i've created a form for creating/editing/deleting an employee record, which has relationships in other tables (i.e. a table of hours each employee worked containing the employee number, occupation id and hours worked, etc). When i create a new employee, i want to create a record in these other tables, i.e. in the employee hours worked table, i want to create a new record that has the employee number and occupation id and the hours worked fields set to zero.

I wrote a query which, when i run it, asks for the employee number and occupation id and will then create the record in the hours worked table. This works, if i do it manually, but i want to automate it.

INSERT INTO employee_hours_worked_table
VALUES (employee_table.employee_id, employee_table.occupation_id, 0, 0, 0, 0);

I want the query to run when i click on the "Add record" button on my form (i.e. as i add the new record, it creates new records in the related tables). I tried inserting the line "DoCmd.OpenQuery (Insert_new_employee_hours_worked(...))" as seen below, but it keeps giving me errors. ("insert_new_employee_hours_worked" beng the name of the query)

Private Sub CmdAdd_Click()
On Error GoTo Err_CmdAdd_Click


    DoCmd.GoToRecord , , acNewRec
    DoCmd.OpenQuery (Insert_new_employee_hours_worked(Employee_ID, Occupation_ID, 0, 0, 0, 0))

Exit_CmdAdd_Click:
    Exit Sub

Err_CmdAdd_Click:
    MsgBox Err.Description
    Resume Exit_CmdAdd_Click
    
End Sub

I also need help deleting the related records from the other tables linked to the employee table when i delete an employee record, but at the moment, when i click the "delete record" button on my form, i get the error "The record cannot be deleted or changed because table "Other_pay_table" includes related records.

Any help would be much appreciated.
Thanks
Laura