By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

Update a value that is NotInList

P: 579
Hi everybody,

I have an Employee table with the following:
Expand|Select|Wrap|Line Numbers
  1. Table - Employee
  2. --------------------------
  3. ID: AutoNum, PK
  4. FName: Text
  5. LName: Text
  6. 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 qryEmployeesActive 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub EmployeeIDFK_NotInList(NewData As String, Response As Integer)
  3.     Dim strEmployeeID As String, strWhere As String
  4.     Dim addToListMessage As String, notInListMessage As String, notInListTitle As String
  5.     Dim notInListUpdate As String, notInListUpdateTitle As String
  6.     Dim previousEmployee As Boolean
  7.     Dim varEmployeeStatus As Integer, varEmployeeID As String
  8.     Dim strUpdate As String
  10.     addToListMessage = "The employee ID you entered is undefined." + Chr(13) + Chr(13) + "Do you want to add the employee?"
  11.     notInListMessage = "The employee you selected is not in the list. Please try again."
  12.     notInListTitle = "Employee: Not in List"
  13.     notInListUpdate = "The employee was successfully added."
  14.     notInListUpdateTitle = "List Updated"
  16.     strEmployeeID = NewData
  18.     strWhere = "[EmployeeID] = " & strEmployeeID
  19.     varEmployeeStatus = -1
  22. '------------------------------------------------------------------------------------------------------------
  23.     If IsNull(DLookup("EmployeeID", "qryEmployees", strWhere)) Then
  24.         GoTo EmployeeNotInList
  25.     Else
  26.         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
  27.             Response = acDataErrContinue
  28.             strUpdate = "UPDATE qryEmployees " & _
  29.                         "SET EmployeeStatus =" & varEmployeeStatus & " " & _
  30.                         "WHERE [EmployeeID]= " & strEmployeeID & ";"
  31.             DoCmd.RunSQL strUpdate
  32.             '[Forms]![frmDeficiency].deficiencyTabControl.Pages(1).SetFocus
  33.             '[Forms]![frmDeficiency].Requery
  34.             'Me.EmployeeIDFK.Requery
  35.             'Me.EmployeeIDFK.SetFocus
  37.             GoTo EmployeeExit
  38.         Else
  39.             MsgBox notInListMessage, vbInformation, notInListTitle
  40.             Me.EmployeeIDFK.Value = Null
  41.             Response = acDataErrContinue
  42.             Exit Sub
  43.         End If
  44.     End If
  47. EmployeeNotInList:
  48.     If vbYes = MsgBox(addToListMessage, vbQuestion + vbYesNo, notInListTitle) Then
  49.         Forms!frmDeficiency.Visible = False
  50.         DoCmd.OpenForm "frmEmployeeAddition", DataMode:=acFormAdd, _
  51.             WindowMode:=acDialog, openArgs:=strEmployeeID
  52.         If IsNull(DLookup("EmployeeID", "qryEmployees", strWhere)) Then
  53.             MsgBox notInListMessage, vbInformation, notInListTitle
  54.             Me.EmployeeIDFK.Value = Null
  55.             Response = acDataErrContinue
  56.         Else
  57.             MsgBox notInListUpdate, vbInformation + vbOKOnly, notInListUpdateTitle
  58.             Response = acDataErrAdded
  59.         End If
  60.     Else
  61.         MsgBox notInListMessage, vbInformation, notInListTitle
  62.         Me.EmployeeIDFK.Value = Null
  63.         Response = acDataErrContinue
  64.     End If
  66. EmployeeExit:
  67.     Exit Sub
  69. 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?
Feb 17 '09 #1
Share this Question
Share on Google+
1 Reply

P: 579
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... :)-
Feb 17 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.