datalogger-DateDiff calculation for EOF

[dj59]

The following code checks the value of field(7) to see if it is "true" or "false".
It loops through each record until it finds a "true" value and makes that the "start time".
It keeps looping until it finds a "false" value and makes that the "end time".
It stores the calculation in the "Diff (mins)" and "Diff2" fields.
So far, so good.....
However, if at the EOF, there is no "false" value, it does not do the calculation.(because there is no end date).

I need help writing the code to say if, at the EOF, field 7 is "true"; calculate the last row value as "end time" and make Diff (mins)="Alert".

Private Sub cmdTest_Click()
On Error GoTo Err_cmdTest_Click
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim rstResults As DAO.Recordset
Dim intFldCtr As Integer
Dim blnFoundATrue As Boolean
Dim varEventID As Variant
Dim varPIN As Variant
Dim varColumnName As Variant
Dim varStartDate As Variant
Dim varStartTime As Variant
Dim varEndDate As Variant
Dim varEndTime As Variant
Dim intResponse As Integer
Dim varStartDateTime As Variant

blnFoundATrue = False

'DELETE all previous Results from the Results Table
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("qryFreezer", dbOpenSnapshot)
Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)

DoCmd.Hourglass True        'Indicate activity, at least

With rst
  For intFldCtr = 7 To .Fields.Count - 1        'Skip all Fields except 5 (flag)
    Do While Not .EOF
      If .Fields(intFldCtr) = True Then
        If Not blnFoundATrue Then        '1st true Value not found, but Field is True
          varEventID = .Fields(0)
          varPIN = .Fields(2)
          varStartDateTime = .Fields(1)
          varColumnName = .Fields(intFldCtr).Name
          varStartDate = Format(.Fields(1), "mm/dd/yyyy")
          varStartTime = Format(.Fields(1), "hh:mm AM/PM")
            blnFoundATrue = True         'Found 1st True, Reset
        End If       '1st True was found, now need the 1st False
      Else      'Field is False
        If blnFoundATrue Then     'False Value with a True found
          varEndDate = Format(.Fields(1), "mm/dd/yyyy")
          varEndTime = Format(.Fields(1), "hh:mm AM/PM")
            With rstResults
              .AddNew
                 ![EventID] = varEventID
                 ![PIN] = varPIN
                 ![Column] = varColumnName
                 ![Start Date] = varStartDate
                 ![Start Time] = varStartTime
                 ![End Date] = varEndDate
                 ![End Time] = varEndTime
                 ![Diff (mins)] = DateDiff("n", varStartDateTime, rst.Fields(1))
                 ![Diff2] = fCalcTime(DateDiff("n", varStartDateTime, rst.Fields(1)))
              .Update
            End With
            Debug.Print varEventID & " " & varColumnName & " " & " " & varStartDate & " " & _
                         varStartTime & " " & varEndDate & " " & varEndTime & " " & _
                         DateDiff("n", varStartTime, varEndTime) & " minutes"
            blnFoundATrue = False         'RESET for next True Run - same Column
        End If
      End If
        .MoveNext
    Loop
      'RESET Variables in preparation for next Column move
      varEventID = Null
      varPIN = Null
      varColumnName = Null
      varStartDate = Null
      varStartTime = Null
      varEndDate = Null
      varEndTime = Null
      varStartDateTime = Null
        blnFoundATrue = False
      .MoveFirst
  Next
End With

DoCmd.Hourglass False

rstResults.Close
rst.Close
Set rst = Nothing
Set rstResults = Nothing

intResponse = MsgBox("View Results Table?", vbInformation + vbYesNo + vbDefaultButton1, "Results Table")
If intResponse = vbYes Then
  DoCmd.OpenQuery "qryResults", acViewNormal, acReadOnly
  DoCmd.Maximize
End If

Exit_cmdTest_Click:
  Exit Sub

Err_cmdTest_Click:
  DoCmd.Hourglass False
    MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    Resume Exit_cmdTest_Click
End Sub

 

[pr2-eugin]

It stores the calculation in the "Diff (mins)" and "Diff2" fields.

I have literally stopped reading after this statement.. Calculated vales has no need to be inside the table.. Please refer to Allen Brown's information on 'Storing Calculated Values'..

I would suggest that you to reconsider this design. If you think it is hard, please explain what is that you are trying to do, so that we might be able to help you..