Update a value that is NotInList

[beacon]

Hi everybody,

I have an Employee table with the following:

Table - Employee
--------------------------
ID: AutoNum, PK
FName: Text
LName: Text
Status: Yes/No
 

Status indicates whether the employee is active/inactive (employed/not employed) with the company.

I have a query setup that is based on the Employee table will show just the active employees.

I have a subform on a form that has a field for the active EmployeeIDs and uses the qryEmployeesAct ive as the rowsource. I have setup code, along with a dialog form, that will handle the standard error and then take the user to the dialog form so the user can enter in an employee that isn't in the list.

When initially setting this up, I failed to account for the fact that an employee might get rehired...that happens a lot where I work. Here's my code with the update:

Private Sub EmployeeIDFK_NotInList(NewData As String, Response As Integer)
 
    Dim strEmployeeID As String, strWhere As String
    Dim addToListMessage As String, notInListMessage As String, notInListTitle As String
    Dim notInListUpdate As String, notInListUpdateTitle As String
    Dim previousEmployee As Boolean
    Dim varEmployeeStatus As Integer, varEmployeeID As String
    Dim strUpdate As String
 
    addToListMessage = "The employee ID you entered is undefined." + Chr(13) + Chr(13) + "Do you want to add the employee?"
    notInListMessage = "The employee you selected is not in the list. Please try again."
    notInListTitle = "Employee: Not in List"
    notInListUpdate = "The employee was successfully added."
    notInListUpdateTitle = "List Updated"
 
    strEmployeeID = NewData
 
    strWhere = "[EmployeeID] = " & strEmployeeID
    varEmployeeStatus = -1
 
'THIS IS THE PART I ADDED TO CHECK FOR AN EXISTING EMPLOYEE
'------------------------------------------------------------------------------------------------------------
    If IsNull(DLookup("EmployeeID", "qryEmployees", strWhere)) Then
        GoTo EmployeeNotInList
    Else
        If vbYes = MsgBox("The employee ID you entered is undefined, but is a previous employee." + Chr(13) + Chr(13) + "Do you want to reactivate the employee?", vbYesNo + vbQuestion, "Previous Employee") Then
            Response = acDataErrContinue
            strUpdate = "UPDATE qryEmployees " & _
                        "SET EmployeeStatus =" & varEmployeeStatus & " " & _
                        "WHERE [EmployeeID]= " & strEmployeeID & ";"
            DoCmd.RunSQL strUpdate
            '[Forms]![frmDeficiency].deficiencyTabControl.Pages(1).SetFocus
            '[Forms]![frmDeficiency].Requery
            'Me.EmployeeIDFK.Requery
            'Me.EmployeeIDFK.SetFocus
 
            GoTo EmployeeExit
        Else
            MsgBox notInListMessage, vbInformation, notInListTitle
            Me.EmployeeIDFK.Value = Null
            Response = acDataErrContinue
            Exit Sub
        End If
    End If
 
'THE PART BELOW WORKS FINE WHEN CHECKING FOR EMPLOYEES NOT IN THE LIST, BUT NOT FOR AN EMPLOYEE THAT MAY BE RETURNING TO WORK AND NEEDS TO BE RE-ADDED TO THE ACTIVE QUERY
EmployeeNotInList:
    If vbYes = MsgBox(addToListMessage, vbQuestion + vbYesNo, notInListTitle) Then
        Forms!frmDeficiency.Visible = False
        DoCmd.OpenForm "frmEmployeeAddition", DataMode:=acFormAdd, _
            WindowMode:=acDialog, openArgs:=strEmployeeID
        If IsNull(DLookup("EmployeeID", "qryEmployees", strWhere)) Then
            MsgBox notInListMessage, vbInformation, notInListTitle
            Me.EmployeeIDFK.Value = Null
            Response = acDataErrContinue
        Else
            MsgBox notInListUpdate, vbInformation + vbOKOnly, notInListUpdateTitle
            Response = acDataErrAdded
        End If
    Else
        MsgBox notInListMessage, vbInformation, notInListTitle
        Me.EmployeeIDFK.Value = Null
        Response = acDataErrContinue
    End If
 
EmployeeExit:
    Exit Sub
 
End Sub
 

If I put an employee ID in the employee field that I know has already been entered in the employee table, it just keeps popping up the same previous employee messagebox. It won't return me to the form and it won't requery the combo box.

The SQL does update the query and shows the employee as active again, but it doesn't do me any good if the user can't choose the employee because the code still thinks it isn't in the list.

Do I need to explicitly reset the rowsource for the employee ID field on the subform each time or is there something I'm missing?

[beacon]

Nevermind....I tried what I suggested and explicitly reset the rowsource and it worked. Because my rowsource was different from the query I was searching/updating, the message box kept popping up.

That's what I get for trying to create combo boxes with rowsources based on logical queries... :)-