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....
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.
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.
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.
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 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.
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
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
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
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.
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
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