Solved What to do with employees if supervisor is inactive (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Yesterday, 23:54
Joined
Jun 26, 2007
Messages
703
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

Wino Moderator
Staff member
Local time
Yesterday, 20:54
Joined
Aug 30, 2003
Messages
35,383
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

You've got your good things, and you've got mine.
Local time
Yesterday, 23:54
Joined
May 21, 2018
Messages
5,491
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

Learning by pecking away....
Local time
Yesterday, 23:54
Joined
Jun 26, 2007
Messages
703
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

Wino Moderator
Staff member
Local time
Yesterday, 20:54
Joined
Aug 30, 2003
Messages
35,383
Can you post a sample db, since seeing the data structure and your methodology is probably the only way to answer your question.
 

oxicottin

Learning by pecking away....
Local time
Yesterday, 23:54
Joined
Jun 26, 2007
Messages
703
@pbaldy I attached two forms one for supervisors and one for employees. I set Chuck Norris as inactive in the supervisor form.
 

Attachments

  • Test2.zip
    51.7 KB · Views: 30

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:54
Joined
Aug 30, 2003
Messages
35,383
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

Wino Moderator
Staff member
Local time
Yesterday, 20:54
Joined
Aug 30, 2003
Messages
35,383
By the way, an argument could be made for having a single table for supervisors and employees. Supervisors are employees too.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:54
Joined
Feb 28, 2001
Messages
20,860
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:54
Joined
May 7, 2009
Messages
14,113
why not include "active" and "inactive" to the combo?
just add an indicator.
 

Attachments

  • Test2.zip
    52 KB · Views: 30

Sun_Force

Active member
Local time
Today, 12:54
Joined
Aug 29, 2020
Messages
318
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.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:54
Joined
May 7, 2009
Messages
14,113
you need to add Code to the Form's Error Event:
Code:
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

Active member
Local time
Today, 12:54
Joined
Aug 29, 2020
Messages
318
you need to add Code to the Form's Error Event:
Code:
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

Super Moderator
Staff member
Local time
Yesterday, 23:54
Joined
Feb 19, 2002
Messages
33,219
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;

Code:
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

Registered User.
Local time
Today, 14:54
Joined
Jul 4, 2013
Messages
2,544
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

Learning by pecking away....
Local time
Yesterday, 23:54
Joined
Jun 26, 2007
Messages
703
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

Registered User.
Local time
Today, 14:24
Joined
Jul 21, 2013
Messages
76
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!
 

Users who are viewing this thread

Top Bottom