Help with delete statement.

[Ezyflo]

I have an Employee table with a yes/no field for "Employee Allocation".

I have an employee allocation table that stores the percent and the "to department".

On the Employee form, they can check yes for the allocation and then go to another form to enter the allocation percent etc... and this information gets stored in the employee allocation table.

I want to create a way to automatically delete the data stored in the employee allocation table if the employee allocation was changed to no.

Any help would greatly be appreciated. Thank you.

 

[scriverb]

In the After Update event procedure of the form control Allocation on the Employee form put the following VBA code:
If me.Allocation = No then
Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("Employee Allocation", dbOpenDynaset)
'Use this one if EmpID field is Text(alphanumeric)
rs.FindFirst "[EmpID] = '" & Me.EmpID & "'"
'Use this one if EmpID field is Numeric
rs.FindFirst "[EmpID] = " & Me.EmpID
If Not rs.NoMatch then
rs("AllocationPerCent&quot = 0
else
'MsgBox if no record was found
end if
rs.close
db.close
End If

The code in red has to be adjusted to your table, field, and form control names as appropriate. This should do what you have asked. Bob Scriver

 

[Ezyflo]

Thanks for your response!

Unfortunately I am getting a runtime error: Update or CancelUpdate without AddNew or Edit.

I'm not too familiar with VB - and I would like to understand the code. Can you explain this part of the code to me?

If Not rs.NoMatch then
rs("AllocationPerCent&quot = 0

Also, my Allocation table has the following fields: EmployeeID, ToDept, Percentage, and Comments

I'm thinking the above code means to set my allocation percent to 0. Would I need clear each field?

Thanks!