Delete two tables with Delete Query

[Chad]

I have two different tables and I need to run a delete query to delete all
information pertaining to an employee when he/she is expired threw a button
on a continious form which uses one of the tables that needs deleted. How
would I do this and please explain in newbie terms.....Thanks!

 

[Jeff Boyce]

Chad

Are you quite certain the former employee's record(s) should be deleted? Is
there any possibility of legal questions (or Human Resources issues) that
might require historical information?

Or are you trying to find a way to "hide" information about someone who
isn't a current employee?

If the latter, you could add a Yes/No field ([CurrentEmployee]), mark it No
for former employees, and use a query that excludes the records with "No".
Or if you might need to know when a former employee left, use a date/time
field instead. You could still query records without an entry in that
[DateDeparted] field.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Chad]

Thanks Jeff, thats a good point! If you have any sugestions on how I could
acomplish this that would be great! I have a table that is a continuios form
with the employees name, Department, Date of Hire, Job Title, Clock number
and an Active/Inactive check box. If I check the box then it removes them
from all the combo boxes so thier name doesnt show up ect. but my problem is
that this list would get long over the years if a lot of employees are
InActive. Is there a way I could hide them in the list or do something?
Thanks!
--
Newbies need extra loven.........

Chad

Are you quite certain the former employee's record(s) should be deleted? Is
there any possibility of legal questions (or Human Resources issues) that
might require historical information?

Or are you trying to find a way to "hide" information about someone who
isn't a current employee?

If the latter, you could add a Yes/No field ([CurrentEmployee]), mark it No
for former employees, and use a query that excludes the records with "No".
Or if you might need to know when a former employee left, use a date/time
field instead. You could still query records without an entry in that
[DateDeparted] field.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have two different tables and I need to run a delete query to delete all
information pertaining to an employee when he/she is expired threw a button
on a continious form which uses one of the tables that needs deleted. How
would I do this and please explain in newbie terms.....Thanks!

 

[Chad]

Jeff, I think im going to go with leaving the data and just setting the row
for tat employee to enabled=false if the chkStatus box is checked. The
problem im having with the code below is that it sets all the boxes "Except
chkStatus" for all employees to enabled=false. How do I get it to where it
disables the boxes for that row instead of every row? Since it is a
continious form then everyone uses the same text box/cbo box names... Thanks!

Private Sub chkStatus_Click()
'Warnings for employee status check box
If chkStatus Then
If MsgBox("Are you sure you wish to change employee status to
INACTIVE?", vbQuestion + vbYesNo, "Set to InActive?") = vbYes Then
DoCmd.SetWarnings False
DoCmd.SetWarnings True
End If
End If
'Sets text boxes to InActive
If Me.chkStatus = False Then
txtEmployeeName.Enabled = True
txtDateofHire.Enabled = True
cboDepartment.Enabled = True
cboJobTitle.Enabled = True
txtClockNumber.Enabled = True
Else
txtEmployeeName.Enabled = False
txtDateofHire.Enabled = False
cboDepartment.Enabled = False
cboJobTitle.Enabled = False
txtClockNumber.Enabled = False
End If


End Sub

 

[Jeff Boyce]

Chad

We're not there. We can't see what you're doing.

From your description, I'll guess that you are using a continuous form, and
trying this in the continuous form. As you've found, this doesn't work.

If you create a Single Form that looks like a row of data, then expand the
vertical size of the window to show multiple (single) forms, it looks quite
a bit like a "continuous form". But without the bad behavior...

Your previous email asked "how?". Please see my response -- you can use a
Yes/No field in your table and a checkbox, or you can use a Date/Time field.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Chad]

Are you saying create a fow for each employee? What if I get a new employee
because a single form doesnt create a new record below the last one...Thanks!

 

[Chad]

Ok, I got it to work! I got rid of the “Enable Code†portion and used
Conditional formatting. I
clicked on each box in the row except the check box “clkStatus†then I
selected Format/Conditional Formatting then I used
“Expression is†and the expression “[chkStatus]=True†and clicked the
enabled button and applied and closed. Now it works as if I was using the VBA
code I placed behind the chkStatus box. If I click on the check box is
disables the row in question not every row!

Code Discarded:

'Sets text/cbo boxes to InActive
If Me.chkStatus = False Then
txtEmployeeName.Enabled = True
txtDateofHire.Enabled = True
cboDepartment.Enabled = True
cboJobTitle.Enabled = True
txtClockNumber.Enabled = True
Else
txtEmployeeName.Enabled = False
txtDateofHire.Enabled = False
cboDepartment.Enabled = False
cboJobTitle.Enabled = False
txtClockNumber.Enabled = False
End If

 

[Jeff Boyce]

?A "fow" (?form) for each employee? Absolutely not! Forms have properties,
including "Single", "Continuous", ...

Again, we aren't there. We don't know how your data is organized. Unless
you give us specific information, we can't really offer specific
suggestions...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/