Solved What to do with employees if supervisor is inactive

[oxicottin]

I just had a question and wanted some ideas or thoughts on how to handle this. Lets say I have a supervisor and for that supervisor they have employees. Now what can I do for the employees that are under a supervisor if I set that supervisor as an inactive? As it is now if I set an supervisor as inactive then go to another form that uses Cascading Combo boxes, the employees wont show because their supervisor is inactive. Thoughts or solutions please....

Thanks,

 

[pbaldy]

Presumably the code/query used for the cascading combos is excluding them. I would tweak that, based on whatever method you use to inactivate them.

 

[MajP]

In the first Supervisor query you can do a union query to put in a dummy supervsior with an ID of 0. Assuming the supervisor combo is unbound.
Select SupervisorID, SupervisorName from tblSupervisors
Union Select 0, "Inactive Supervisor" from tblSupervisors

Then in the second combobox
you would likely need an iif for the supervisorID_FK. If their supervisor is inactive then there supervisor id is 0 else it just SupervisorID_FK.
This is again assuming that you are just picking an employee.

Probably need to see both queries.

 

[oxicottin]

I want to exclude their names in the cascading combos because there no longer active. I have a form that you can add/edit and set an supervisor inactive and on that form after setting them inactive they still show but show grayed out (disabled). I want to be able on that form to be able to have maybe another combo or something so I can set every employee that's attached to that supervisor be changed over to someone else.

 

[pbaldy]

Can you post a sample db, since seeing the data structure and your methodology is probably the only way to answer your question.

 

[June7]

Run an UPDATE sql?

 

[oxicottin]

@pbaldy I attached two forms one for supervisors and one for employees. I set Chuck Norris as inactive in the supervisor form.

 

[pbaldy]

So what behavior are you looking to change? The employee form doesn't work, as it's looking for a query that doesn't exist (when you select a supervisor). You could have some sort of button on the supervisor form that let you select a new supervisor for Chuck's employees and run an update query.

 

[pbaldy]

By the way, an argument could be made for having a single table for supervisors and employees. Supervisors are employees too.

 

[The_Doc_Man]

I had a similar problem where a person would be the leader of a working group but that person might get another job and leave us. What I did was to create one action sequences based on two combo boxes.

1. Select the losing supervisor.
2. Select the gaining supervisor.
3. Click a button that executed these two SQLs:

3.a "UPDATE WrkGroups SET [Supv] =" & [GainingSupv] & " WHERE [Supv] = " & [LosingSupv] & " ;"
3.b "UPDATE GrpSupv SET SupActive = FALSE WHERE [Supv] = " & [LosingSupv] & " ;"

I leave it to you to see where this is going and perhaps adapt it to your situation. Note that in my cases, the fields in question were ALL numeric. The combo boxes showed the supervisor names but the bound column for each combo was the supervisor ID number.

 

[oxicottin]

@The_Doc_Man Im going to try that....

@pbaldy , sorry I fixed it and reuploaded it in the post.

 

[pbaldy]

@pbaldy , sorry I fixed it and reuploaded it in the post.

Doc spelled out what I was suggesting so I'll let you play with that.

 

[arnelgp]

why not include "active" and "inactive" to the combo?
just add an indicator.

 

[Sun_Force]

why not include "active" and "inactive" to the combo?
just add an indicator.

@arnelgp and all members.
I'm really sorry and I don't intend to hijack the thread, but it's a problem I've had for a while and since @arnelgp's sample shows the same error, I ask it here.
In frm_UpdateEmInfo, if you select a Supervisor from the combo box under "Filter Employee By Supervisor" and delete the filtered result, you will receive a "No current record." Error.
Why this error occurs and how can I bypass this error?
I think (not sure) it mostly happens when I delete all records of a filtered form.
Thank you.

 

[arnelgp]

you need to add Code to the Form's Error Event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3021 Then
    Response = acDataErrContinue
Else
    MsgBox "Error: " & DataErr & vbCrLf & Err.Description
End If
End Sub

 

[Sun_Force]

you need to add Code to the Form's Error Event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3021 Then
    Response = acDataErrContinue
Else
    MsgBox "Error: " & DataErr & vbCrLf & Err.Description
End If
End Sub

I appreciate your help.
Thank you.

 

[Pat Hartman]

The RowSource of a combo only works correctly if ALL of the underlying values are in the list. So, when I have to inactivate someone/something, I include the ActiveFlg as the first column in the sort order and this drops the inactive records to the bottom of the list so they are out of the way. In addition to this, you need code in the combo's BeforeUpdate event to prevent users from choosing an inactive item.

This is one of the few cases where I actually undo the user's update. I do it because he must select a new item from the list. In the code below, the .Column property of the combo is used to reference the columns of the RowSource. This array is zero based so (3) refers to the fourth item in the select clause. Adjust as necessary. You also will want to change the ColumnWidths property to show the status. The list can be very busy if every row says Active or Inactive, so I tend to show only the "Inactive" values. The query hides the active ones.

Select SupervisorID, SupervisorName, IIf(ActiveFlg = True, Null, "Inactive") As InactiveDesc, ActiveFlg
From YourTable
Order By ActiveFlg, SupervisorName;

Private Sub cboSupervisor_BeforeUpdate(Cancel As Integer)
    If Me.cboSupervisor.Column(3) = True then
    Else
        msgbox "This supervisor is inactive and you may not select this item.", vbOKOnly
        cancel = true
        Me.cboSupervisor.Undo
        Exit Sub
    end if
End Sub

 

[Cronk]

A business rule should be enforced that a supervisor cannot be made inactive if that supervisor has any subordinates. While a supervisor might be replaced so that the new supervisor takes over all the subordinates of the previous supervisor, it could be a staffing reorg means that subordinates are assigned to a number of other supervisors.

 

[oxicottin]

I got it worked out to where I choose a replacement supervisor and it moves all the employees that are under them to the new supervisor and then sets the old supervisor status to inactive. If anyone wants to see how I did it I can make up a test DB.... But wont be till NW

Thanks All for the help!

 

[HealthyB1]

I got it worked out to where I choose a replacement supervisor and it moves all the employees that are under them to the new supervisor and then sets the old supervisor status to inactive. If anyone wants to see how I did it I can make up a test DB.... But wont be till NW

Thanks All for the help!

Hi, I would like to see your end result please!