•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS Access and FileMaker Pro section within the Web Development category of DaniWeb, a massive community of 391,662 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,838 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS Access and FileMaker Pro advertiser:
Views: 811 | Replies: 0
 |
•
•
Join Date: Sep 2006
Location: South Africa
Posts: 15
Reputation: 
Rep Power: 2
Solved Threads: 0
Newbie Poster
MS Access Forms - Deleting related records from other tables when deleting a record
#1
May 28th, 2008
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.
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)
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
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 SubI 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
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS Access and FileMaker Pro Marketplace
Linear Mode
- Previous Thread: Open a pdf in ms access
- Next Thread: MS Access - Running a Query in a Form
