Public Sub BuildRidesD1()
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
strRidesM = "SELECT dbo_Rides.ID, dbo_Rides.ApptDatetime, IIf([dbo_Rides]![ApptDuration]=0,'O','I') AS Dir, dbo_Rides.DriverID, dbo_Patients.LastName, Timevalue([dbo_Rides]![ApptDatetime]) AS rTime, dbo_Clinics.Abbr, dbo_Rides.isFrontSeatOnly, dbo_Rides.isRidealong, dbo_Rides.NumPassengers, dbo_Patients.isWheelchair, dbo_Patients.ExtraPickupMins "
strRidesM = strRidesM & "FROM (dbo_Rides INNER JOIN dbo_Patients ON dbo_Rides.PatientID = dbo_Patients.ID) INNER JOIN dbo_Clinics ON dbo_Patients.DefaultClinicID = dbo_Clinics.ID "
strRidesM = strRidesM & " WHERE DateValue([ApptDateTime]) Between #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 5 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] & "#" & " AND [DriverID] = " & [Forms]![mTM]![subTM1].[Form]![subD1H].[Form]![txtDriverID] & " AND [CxlReasonID] is null"
strRidesM = strRidesM & " ORDER BY dbo_Rides.ApptDatetime, IIf([dbo_Rides]![ApptDuration]=0,'O','I');"
Set rsm = db.OpenRecordset(strRidesM, adOpenDynamic, dbSeeChanges)
For Each c In Me.Form
If left(c.Name, 1) = "z" Then
Select Case Mid(c.Name, 2, 1)
Case "m"
tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
With rsm
.Filter = "DateValue([ApptDateTime]) Between #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 5 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 5 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
Set rsmf = .OpenRecordset
End With
With rsmf
If .RecordCount <> 0 Then
If Not .EOF Then
.MoveLast
.MoveFirst
'Do Until .EOF
If .RecordCount > 1 Then
c = .RecordCount & " Rides" & vbNewLine & vbNewLine
Do Until .EOF
c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine & "------------------" & vbNewLine
.MoveNext
Loop
Else
Do Until .EOF
c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
.MoveNext
Loop
End If
'.MoveNext
'Loop
End If
End If
End With
Forms!mTM!subTM1.Form!subD1H.Form!mRideCount = ""
Case "t"
tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
With rsm
.Filter = "DateValue([ApptDateTime]) Between #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 4 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 4 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
Set rsmf = .OpenRecordset
End With
With rsmf
If .RecordCount <> 0 Then
If Not .EOF Then
.MoveLast
.MoveFirst
Do Until .EOF
c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
.MoveNext
Loop
End If
End If
End With
Case "w"
tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
With rsm
.Filter = "DateValue([ApptDateTime]) Between #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 3 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 3 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
Set rsmf = .OpenRecordset
End With
With rsmf
If .RecordCount <> 0 Then
If Not .EOF Then
.MoveLast
.MoveFirst
Do Until .EOF
c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
.MoveNext
Loop
End If
End If
End With
Case "r"
tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
With rsm
.Filter = "DateValue([ApptDateTime]) Between #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 2 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 2 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
Set rsmf = .OpenRecordset
End With
With rsmf
If .RecordCount <> 0 Then
If Not .EOF Then
.MoveLast
.MoveFirst
Do Until .EOF
c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
.MoveNext
Loop
End If
End If
End With
Case "f"
tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
With rsm
.Filter = "DateValue([ApptDateTime]) Between #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 1 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 1 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
Set rsmf = .OpenRecordset
End With
With rsmf
If .RecordCount <> 0 Then
If Not .EOF Then
.MoveLast
.MoveFirst
Do Until .EOF
c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
.MoveNext
Loop
End If
End If
End With
End Select
End If
Next
rsmf.Close
rsm.Close
db.Close
Set rsm = Nothing
Set rsmf = Nothing
Set db = Nothing
End Sub