|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Delete two tables with Delete QueryI 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
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! -- Show quoteRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Chad" <C***@discussions.microsoft.com> wrote in message news:80E2BCF5-621E-4339-A97C-D59CE0B7A78E@microsoft.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! 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! -- Show quoteNewbies need extra loven......... "Jeff Boyce" wrote: > 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 > http://mvp.support.microsoft.com/ > > Microsoft IT Academy Program Mentor > http://microsoftitacademy.com/ > > "Chad" <C***@discussions.microsoft.com> wrote in message > news:80E2BCF5-621E-4339-A97C-D59CE0B7A78E@microsoft.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! > > 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 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. -- Show quoteRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Chad" <C***@discussions.microsoft.com> wrote in message news:9EF9771C-48D0-48DF-9C27-467E69133080@microsoft.com... > 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 > 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! -- Show quoteNewbies need extra loven......... "Jeff Boyce" wrote: > 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 > http://mvp.support.microsoft.com/ > > Microsoft IT Academy Program Mentor > http://microsoftitacademy.com/ > > "Chad" <C***@discussions.microsoft.com> wrote in message > news:9EF9771C-48D0-48DF-9C27-467E69133080@microsoft.com... > > 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 > > > > ?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... -- Show quoteRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Chad" <C***@discussions.microsoft.com> wrote in message news:D305DDAA-9E8B-45A7-8C86-A4712FF44BC2@microsoft.com... > 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! > -- > Newbies need extra loven......... > > > "Jeff Boyce" wrote: > > > 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 > > http://mvp.support.microsoft.com/ > > > > Microsoft IT Academy Program Mentor > > http://microsoftitacademy.com/ > > > > "Chad" <C***@discussions.microsoft.com> wrote in message > > news:9EF9771C-48D0-48DF-9C27-467E69133080@microsoft.com... > > > 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 > > > > > > > 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  |
|||||||||||||||||||||||
Other interesting topics