Access 97 VB

[Guest]

I have a recordset of a table in which I compare differences between the
latest audio test record (sorted by SQL code) and the 'live' form. I also
need to check all the records in the recordset to see if two consecutive
records have an 'Abnormal' reading in the same field
([ComparisonWithBaseline]) and if so I need to define the latest record of
that 'pair' as 'Baseline', fo ruse in the next test. Tests are every year or
so.
Can you please help me with the actual code used to find if two consecutive
records have the same field marked 'Abnormal' and assign the latest record to
a variable called Baseline.

Many thanks

 

[Brendan Reynolds]

To find two consecutive matches, loop through the recordset, setting a flag
each time you find a match and clearing it each time you don't find a match.
Before setting the flag, check whether it is already set - if it is, you've
got two consecutive matches. In the example below, the flag is the variable
named 'boolFoundOne'.

For the second part of the task, assigning the record to a variable, I've
assigned the value of each field in the recordset to an element of a Variant
array. There are several other ways that part of the task might be handled.
The best approach will depend on what you want to do with those values. As
that information isn't available to me, I've just chosen one method of many
possible alternatives, for demonstration purposes.

Note that if there is more than one matching pair, this code will assign the
second record of the last pair found to the variable, discarding any
previously found pairs. Again, whether this is the right thing to do depends
on what you want to do with the result. You might want to use an array of
variables to hold a result for each pair.

Public Sub FindConsec()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim varResult As Variant
Dim boolFoundOne As Boolean
Dim varLoop As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TestID, TestDate, TestText FROM
tblTest ORDER BY TestDate")
With rst
Do Until .EOF
If .Fields("TestText") = "abnormal" Then
If boolFoundOne = True Then
Debug.Print "found consecutive: ID = " &
..Fields("TestID")
varResult = Array(.Fields("TestID").Value,
..Fields("TestDate").Value, .Fields("TestText").Value)
Else
Debug.Print "found first: ID = " & .Fields("TestID")
boolFoundOne = True
End If
Else
boolFoundOne = False
End If
.MoveNext
Loop
.Close
End With

If Not IsEmpty(varResult) Then
For Each varLoop In varResult
Debug.Print varLoop
Next varLoop
End If

End Sub