I realized after some testing that I need this routine embedded in my AddVAC code. I added the Select CASE section but it is not working. The rest of this code works correctly as I have been using it for months. It is running the first 2 routines of IF statements but it does nothing when it gets to the Select CASE.
Code:
Private Sub cmdAddVAC_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Variant
Dim vacancyValue As String
Dim LResponse As Integer
Dim vMsg
Dim TOStart As Date
Dim TOEnd As Date
On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblPerfIssues")
If Len(Me.cboAnalystSpecialist & vbNullString) = 0 Or Len(Me.txtReportDate & vbNullString) = 0 Or Len(Me.cboContractNum & vbNullString) = 0 Then
MsgBox "Please fill in Analyst/Specialist, Report Date and Contract Number"
Exit Sub
End If
If Len(Me.cboTaskOrder & vbNullString) = 0 Or Len(Me.cboCLIN & vbNullString) = 0 Or Len(Me.lstVACReason & vbNullString) = 0 Or Len(Me.txtIndHours & vbNullString) = 0 And Len(Me.txtCovHours & vbNullString) = 0 Or Len(Me.txtVACStart & vbNullString) = 0 Then
MsgBox "Please select a Task Order, CLIN, Vacancy Reason, Individual or Coverage Hours and enter a Vacancy Start Date."
Exit Sub
End If
Select Case True
Case Not IsDate(VACStart)
vMsg = "Vacancy Start is not a date"
Case IsDate(VACStart <= TOStart) And (VACStart >= TOEnd)
vMsg = "Vacancy Start Date must be between Task Order Start and End Date."
Case IsDate(VACStart) And IsDate(VACEnd)
If VACStart > VACEnd Then vMsg = "vacStart must be before the VacEnd date"
Case IsDate(VACEnd >= TOStart) And (VACEnd <= TOEnd)
vMsg = "Vacancy End Date must be between Task Order Start and End Date."
End Select
With rs '<<--!!!!
.AddNew
!AnalystSpecialist = Me.cboAnalystSpecialist
!ReportDate = Me.txtReportDate
!ContractNum = Me.cboContractNum.Column(0)
!Contractor = Me.txtContractor
!NameofMATO = Me.txtNameofMATO
!TaskOrder = Me.cboTaskOrder
!CLIN = Me.cboCLIN.Column(2)
!COR = Me.txtCOR
!MTFDTF = Me.txtMTFDTF
!LaborBand = Me.txtLaborBand
!LaborCat = Me.txtLaborCat
!SiteofService = Me.txtSiteofService
!ClinicalArea = Me.txtClinicalArea
!IndCov = Me.txtIndCov
!MissedHours = Me.txtIndHours
!MissedShifts = Me.txtCovHours
!TOStartDate = Me.txtTOStartDate
!TOEndDate = Me.txtTOEndDate
!VACStart = Me.txtVACStart
!VACEnd = Me.txtVACEnd
!VACReason = Me.lstVACReason.Column(0)
!IssueKey = Me.lstVACReason.Column(1)
!VACComments = Me.txtVACComments
.Update
End With '<<--!!!!
LResponse = MsgBox("Change to a new Task Order?", vbYesNo, "Continue")
If LResponse = vbYes Then
Me.cboTaskOrder.Value = Null
Me.cboCLIN.Value = Null
Me.txtCOR.Value = Null
Me.txtMTFDTF.Value = Null
Me.txtLaborBand.Value = Null
Me.txtLaborCat.Value = Null
Me.txtSiteofService.Value = Null
Me.txtClinicalArea.Value = Null
Me.txtIndCov.Value = Null
Me.txtIndHours.Value = Null
Me.txtCovHours.Value = Null
Me.txtVACStart.Value = Null
Me.txtVACEnd.Value = Null
Me.lstVACReason.Value = Null
Me.txtVACComments.Value = Null
Me.txtIssueKey = Null
Me.txtIndHours.Enabled = True
Me.txtCovHours.Enabled = True
Else
Me.txtIndHours.Value = Null
Me.txtCovHours.Value = Null
Me.txtVACStart.Value = Null
Me.txtVACEnd.Value = Null
Me.txtTOStartDate.Value = Null
Me.txtTOEndDate.Value = Null
Me.lstVACReason.Value = Null
Me.txtVACComments.Value = Null
Me.txtIssueKey = Null
End If
ErrorHandler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Workdays"
Resume ErrorHandler_Exit
End Sub