Implementing a Delete Query

[mwabbe]

I built a delete query that will delete records from each table corresponding to the empName, but I do not know how to "call" this. I have a form that the user types information into and there is a delete button on it but that will only delete the information in the table that the current form is joined. here the problem, there are 4 tables where that employee would have information and i would want all of that to be deleted from the first form because otherwise if the user goes through and hits the delete button on each form. access gives an error because the empID is used to open the next form to get the right information about the employee and if the information on the form before the next one is deleted the button does not have an empID to bring up the right information on the next form.

[ajetrumpet]

you need to change the time at which you delete. If you are deleting employee information before the 2nd form opens, and that 2nd form needs to display the information you deleted already, that obviously won't work.

One thing to note is that if you are opening two forms that require locking of the same tables (even one common table), the 2nd form will most likely not be updateable. Access locks a recordsource table when it is being used by a form to display information. It acts just like two users trying to edit a record or open the same form at once.

As for the calling of it, simply use: docmd.openquery "deleteQuery" in your vba code. Another thing, if you haven't already done this, is that you might want to uncheck the option in the access options > current database that says "confirm action queries". If you do this, you can write either docmd.runSql or currentdb.execute to run the delete query without any pop up messages.

If you want all employee info delete from the db as well, make sure that your CASCADES are turned on. That way, you'll only need one table in your query statement.

[mwabbe]

i do not have cascade deletes turned on because i am using allen brownes audit trail and it will not log deletes unless they are done on the form.

now come to think of it using a delete query would not work either because the delete would not be done on the form.

lets say i have two forms one- empInfo and two- empBenefits. they are joined to two seperate tables which are linked by ID. and this button i have on form empInfo saves and opens empBenefits passing the id and name.

when i want to delete an employee i open them up and the first from empInfo I hit delete, then hit the empBenefits button, but it will not open because the record on the previous form was deleted the ID is gone.

so, im just going to guess its probably impossible to log the deletes the way i want.

Probably just going to turn on cascade deletes and dont worry about the log

[ajetrumpet]

are you doing work for a public company? are they required to be audited? i guess you can make educated decisions on this based on what the audit requirements are. If you're working for a privately held company, most of the time an audit trail can take a back seat or not even exist at all unless you are the owner and you want to see it.

An audit trail is really good IMO for two reasons:

*You are audited by an outside firm or are required to have a satisfactory internal controls system.
*You are concerned about the security of your data and you're the owner of your company.

You might want to consider things like that before working out how to make it work. Do you really need one?

The other thing you mention is that you're deleting BEFORE viewing the emp benefits. Why, for any reason, would you delete an emp's record before viewing their benefits, if in fact you need to analyze those benefits? That just doesn't make sense. It's also not conducive to a normalized data structure.

[mwabbe]

I honestly dont think this db needs it. only one person will have access to this but, an audit trial is requested. I think the point of this one is if an employee is not sure about why something is the way it is in their record, the log can be checked for any mistake changes.

[mwabbe]

The only reason that an employee would be deleted, I think would be because of a mistake typing them in.