Insert Into and Delete SQL functions in VBA

[Loony064]

Hey all!

I'm currently working on a database for a payroll system. I've created all the tables and the queries to calculate wages, etc, as well as forms so that the user can add/delete/edit/view records.

There is an employee table containing details about an employee (employee id - primary key, name, surname, project id - references project table, occupation id - references occupation table, etc). There are also tables containing each employee's hours worked, total income, total deductions, etc.

When a new employee is added to the database, a new record must be created for him/her in the following tables: hours_worked, income_deductions_wages, total_deductions, total_wages and obviously the employee table.

Here is my VBA code that creates the new record in he employee table, and then inserts a new record into the hours_worked table. i want it to take the employee_id and project_id from the form which has the data fro the new employee record. At the moment, all this code does is create the new record in the Employee table.

Private Sub CmdAddRecord_Click()
On Error GoTo Err_CmdAddRecord_Click
 
    DoCmd.GoToRecord , , acNewRec
    'Prevent user warnings
  DoCmd.SetWarnings False
 
Dim SQL As String
   SQL = "INSERT INTO employee_hours_worked_table values(frmEmployee_id,project_id ,0,0,0,0);"
 
    DoCmd.RunSQL SQL
 
'Allow user warnings
  DoCmd.SetWarnings True
 
Exit_CmdAddRecord_Click:
    Exit Sub
 
Err_CmdAddRecord_Click:
    MsgBox Err.Description
    Resume Exit_CmdAddRecord_Click
 
End Sub

When an employee is removed from the database, all the records related to that employee must also be deleted.

i have queries to delete the record from each table. they are all as follows, just with different table names.

DELETE *
FROM other_pay_table
WHERE employee_id=FrmEmployee_ID;

FrmEmployee_ID is the name of the textbox on the form. FrmEmployee is the Employee_ID of the record to delete. At the moment, when i choose to delete an employee record, a textbox pops up asking me to enter the FrmEmployee_ID. It does that for all 7 queries. I want the query to either take the FrmEmployee_ID from the active record, or alternatively, only enter it once but i don't know how to do that.

the VBA code is as follows:

Private Sub CmdDelete_Click()
On Error GoTo Err_CmdDelete_Click
 
 Dim SQL As String
 
'Prevent user warnings
  DoCmd.SetWarnings False
 
DoCmd.OpenQuery "delete_record_allowances_pay_table"
DoCmd.Close acQuery, "delete_record_allowances_pay_table"
 
DoCmd.OpenQuery "delete_record_other_pay_table"
DoCmd.Close acQuery, "delete_record_other_pay_table"
 
DoCmd.OpenQuery "delete_record_employee_hours_worked_table"
DoCmd.Close acQuery, "delete_records_employee_hours_worked_table"
 
DoCmd.OpenQuery "delete_record_employee_income_deductions_wages_table"
DoCmd.Close acQuery, "delete_record_employee_income_deductions_wages_table"
 
DoCmd.OpenQuery "delete_record_employee_total_deductions_table"
DoCmd.Close acQuery, "delete_record_employee_total_deductions_table"
 
DoCmd.OpenQuery "delete_record_employee_total_wages_table"
DoCmd.Close acQuery, "delete_record_employee_total_wages_table"
 
DoCmd.OpenQuery "delete_record_employee_table"
DoCmd.Close acQuery, "delete_record_employee_table"
 
'Allow user warnings
  DoCmd.SetWarnings True
 
Exit_CmdDelete_Click:
    Exit Sub
 
Err_CmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_CmdDelete_Click
 
End Sub

Any help would be much appreciated.
Regards
Laura

 

[twoplustwo]

Hey,

With regards to entering the ID just have a text box and set each query to look at that by putting [forms].[formname].[fieldname] in the criteria part of the column.

Also, if you have a relational db set up deleting one user should remove all of the underlying details no?

Not to be patronising, but is your db fully (or mostly ) normalised?

 

[Loony064]

Thanks for the help. I'll try that. originally when i tried to delete the record, it wouldn't let me because there were related records in other tables.

I have normalised all my tables. If i did it right, it should be in 3NF, but this is the first time i'm doing such a huge project. I'm still a student and for our class assignments, we were given small databases to normalize and often subtle clues were given to help ensure we were on the right track.

 

[twoplustwo]

Hi dude,

If I understand correctly when you set up your relationships check the Cascade Update Related Fields and Cascade Delete Related Field. This should delete all child records to the main i.e. if you delete Dave Access from the db all of Dave's records should go. Are you sure you want to be deleting people? You could just use a flag or some such and toggle it on/off.

 

[Loony064]

Twoplustwo, YOU ARE AWESOME! I wish i knew to do that before i spent 4 hours trying to do it other ways! thanks a million!

 

[twoplustwo]

Surprised they didn't go through that when they were teaching normalisation?

Anyway, glad it work.

gl with the project!