Delete data from two table

[Shamli]

Hi


I am trying to delete data from two table when a button is clicked.
I have
Table:

1. Employee
2. Project
3. Element
4. EmpToProj (EmpID, ProjID)
5. Timesheet (EmpID,ProjID,ElementID, TaskID, hours)Etc

Form Contains

1. lstEmployee : List Box which has employees list
2. lstAssigned: List Box which has Projects that are assigned to the employee
3. lstAvailable: List Box which has Projects that are yet to be assigned to the employee

I have " ADD " and "REmove" buttons.

When I click a project from the "lstAssigned" and click the remove button:
The project have to be removed from EmpToProj Table and also from Timesheet.






The Query I am using is:

---

Private Sub cmdRemove_Click()
On Error GoTo cmdRemove_Click_Error
Dim sSQL As String

Dim nMid As Long 'Member
Dim nWid As Long 'Event
Dim varRow As Variant
Dim ctlChosen As Control
If Len(lstEmployee.Column(0) & vbNullString) = 0 Then
MsgBox "There is no Member selected.", vbOKOnly + vbInformation, " I N P U T N E E D E D "
Exit Sub
End If
nMid = lstEmployee.Column(1)
'Check that at least one item has been selected
If lstAssigned.ItemsSelected.Count = 0 Then
MsgBox "Please select an event to remove from this Members's list.", vbOKOnly + vbInformation, _
" I N P U T N E E D E D "
lstAssigned.SetFocus
Exit Sub
End If
Set ctlChosen = lstAssigned
For Each varRow In ctlChosen.ItemsSelected
nWid = ctlChosen.Column(0, varRow)
sSQL = "DELETE * FROM EmpToProj WHERE EmployeeID = " & nMid & " AND ProjectID = " & nWid & ";"


CurrentDb.Execute sSQL, dbFailOnError
lstAvailable.Requery
lstAssigned.Requery
Next varRow

cmdRemove_Click_EXIT:
Set ctlChosen = Nothing
Exit Sub
cmdRemove_Click_Error:
Select Case Err
Case Else
Call fcnLogError(Err.Number, Err.Description)
End Select
Resume cmdRemove_Click_EXIT
End Sub

---

This query helps in removing the project from Emp to project table.
But now I want to remove this from Timesheet table too.


Please help me with this.

Thanks

[Shamli]

I used this query along with the previous one and it worked.
DoCmd.RunSQL "DELETE * FROM Timesheet_T WHERE EmployeeID = " & nMid & " AND ProjectID = " & nWid & ";"

[orange]

For clarity, what is the business rule(s) associated with these DELETEs?

If an Employee was Assigned to Project X and has worked any hours/time on the Project, then accounting for that Employee's time would seem relevant and required.
If an Employee has never been assigned to Project X, then there should not be a record showing this Employee in EmpToProj having been assigned to Project X. And there wouldn't be a Timesheet record for this Emp/Proj combination.

I just wondering what is causing you to DELETE these records. There may be a legitimate reason, but I'm curious.


Good luck.

[Shamli]

It is for timesheet purpose.Data is first saved in the temp timesheet. so whenever the employee opens the timesheet for next week. certain fields are auto filled so that the employee need not manully chose the fields all the time.
So when an employee is removed from the project next week. It still carries the data from the temp timesheet and appears and only the employee chooses the controls it doesn't appear.
I hope I explained
So in order to avoid this, as soon as the project is being removed from the emptoproj table and from the timesheet table.

For clarity, what is the business rule(s) associated with these DELETEs?

If an Employee was Assigned to Project X and has worked any hours/time on the Project, then accounting for that Employee's time would seem relevant and required.
If an Employee has never been assigned to Project X, then there should not be a record showing this Employee in EmpToProj having been assigned to Project X. And there wouldn't be a Timesheet record for this Emp/Proj combination.

I just wondering what is causing you to DELETE these records. There may be a legitimate reason, but I'm curious.


Good luck.