Complicated Looping

[dj59]

I know MS Access very well, but am fairly new to writing vba.
This one has me completely confused. Here is my task (written in English), not vba:
[FONT=&quot]‘Variables that already exist in the table I want to loop through are:[/FONT]
[FONT=&quot]‘”Fahrenheit” and “Time”[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]'Create new variables[/FONT]
[FONT=&quot] 'Dim startTime As Date 'Declare start time[/FONT]
[FONT=&quot] 'Dim endTime As Date 'Declare end time[/FONT]
[FONT=&quot] 'Dim result As Integer 'Declare result in minutes[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] 'IF Fahrenheit in row1 >=14[/FONT]
[FONT=&quot] 'THEN make startTime = Time from row1[/FONT]
[FONT=&quot] 'IF Fahrenheit in row2 >=14[/FONT]
[FONT=&quot] 'THEN go to next record[/FONT]
[FONT=&quot] 'Keep looping through each record until the answer is NO[/FONT]
[FONT=&quot] 'THEN make endTime = Time field in the first row where NO is the answer[/FONT]
[FONT=&quot] 'THEN subtract endTime from startTime = result[/FONT]
[FONT=&quot] 'IF the result is <60 minutes, move on to the next row and begin the loop process again.[/FONT]
[FONT=&quot] 'IF the result is >=60 minutes save all rows from startTime to endTime plus result in a table? [/FONT]

If you can give me direction on this or websites to view, I'd appreciate it.

 

[NigelShaw]

Hi,

Before i give you an answer, where is the data? is it kept in a table before being displayed on a form or is it displays on a form from manual input?

What i need-
If its in a table, the name of the table & field wher data is
If its on a form, the name of the form & the name of the text objects


thanks

Nigel

 

[NigelShaw]

Hi,

that doesnt make any sense to me whatsoever? Im happy to help but, you've got to give me readable information that is understandable. Try laying out in a table structure i.e.-

Table Name = tblMyTable

ID Field1 Field2 Field3 Field4 etc
1 data data data data
2 data data data data
3 data data data data

cheers

Nigel

 

[dj59]

Hi,
There is a table named tblFreezer_1 with the fields :
[FONT=&quot]PIN Freezer Time Fahrenheit [/FONT]
[FONT=&quot]1 A 05/15/2012 10:11 74[/FONT]
[FONT=&quot]1 A 05/15/2012 10:13 68[/FONT]
[FONT=&quot]1 A 05/15/2012 10:20 57[/FONT]
[FONT=&quot]1 A 05/15/2012 10:21 45[/FONT]
[FONT=&quot]1 A 05/15/2012 10:28 15[/FONT]
[FONT=&quot]1 A 05/15/2012 10:30 25[/FONT]
[FONT=&quot]1 A 05/15/2012 10:40 40[/FONT]
[FONT=&quot]1 A 05/15/2012 11:13 30[/FONT]
[FONT=&quot] [/FONT]
This is the table to loop through to find the rows that have Fahrenheith >=14 for more than 60 minutes.
If this happens save the first and last rows from that group to a table, named tblFreezerAlert, which will have all the above fields plus startTime (10:11), endTime(11:13), result (minutes). [FONT=&quot][/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]PIN Freezer Time Fahrenheit startTime endTime result[/FONT]
[FONT=&quot]1 A 05/15/2012 10:11 74 10:11 11:13 62[/FONT]
[FONT=&quot]1 A 05/15/2012 11:13 30 10:11 11:13 62[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]
[/FONT]

 

[G37Sam]

You don't need a loop, you need a query. And there's no need to duplicate your fields in a new table. Create a new query and put this in its SQL View:

SELECT tblFreezer.PIN, tblFreezer.Freezer, tblFreezer.Time, tblFreezer.Fahrenheit, Hour(Now()-[Time]) AS ElapsedTime
FROM tblFreezer
WHERE (((tblFreezer.Fahrenheit)>=14) AND ((Hour(Now()-[Time]))>=1));

 

[dj59]

I realize I fail at explaining what I want. I'll have to work on that. I did find code that works great for what I need. Here it is:
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 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)
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
![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
varColumnName = Null
varStartDate = Null
varStartTime = Null
varEndDate = Null
varEndTime = Null
varStartDateTime = Null
varFehrenheitTemp = 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