Looping through 2 recordsets (1 Viewer)

[Indigo]

Using Office 365, I have an Access database that generates an email. Currently it loops through a recordset to determine if any records are greater than 30 minutes in duration. If they are, they appear in red font in an HTML table in the body of the email, if not, then black. The database is tracking downtime duration for multiple equipment stations. Below is a snippet of my original code.

             rsDowntime.MoveFirst
             Do While Not rsDowntime.EOF
             If rsDowntime!Duration >=30 Then
                           ….red font
             Else
                           …black font
                                        End If
             rsDowntime.MoveNext
             Loop
             rsDowntime.Close

I have now been asked to also have any repeat station incidents show up in a red font as well.

For example, the recordset I am currently using to test has 17 records in it. Of those 17, there are 3 repeats for one station and 7 repeats for another station. So, I created another recordset that would only display the repeated station numbers. I altered my code, above, as follows:

             rsDowntime.MoveFirst
             Do While Not rsDowntime.EOF
             rsPlus3.MoveFirst
             Do While Not rsPlus3.EOF
             If rsDowntime!Duration >=30 Or rsPlus3!Station = rsDowntime!Station Then
                           ….red font
             Else
                           …black font
                                        End If
             rsPlus3.MoveNext
             Loop
             rsDowntime.MoveNext
             Loop
             rsDowntime.Close
             rsPlus3.Close

However, now the results are duplicating the records and I end up with 34 records returning instead of 17. So, I thought I could use a filter, but I am struggling to get the results I need. Does anyone have any suggestions that might help? Thank you.

 

[jdraw]

On rereading, I think I have misinterpreted the post.

 

[Indigo]

Thank you for your reply. Both recordsets are based on a query from the same tables. The Downtime table contains records for the Station Name, duration of the downtime and comments regarding the downtime. There is a one-to-many relationship with a main table that records the date and shift. The rsPlus3 is the same query but counts the Stations and only displays records where the station is repeated 3 or more times over the course of a date/shift. This is a snippet of what I am seeing... due to confidentiality, I had to blur some of the details:

I hope this makes sense.

 

[plog]

Both recordsets are based on a query from the same tables.

I didn't do a full mental dive into your code, but the above stuck out at me. Why not have 1 query do all your work? Add a field to it called 'FontColor' and then use logic on the data of that record to determine the color you need for it. Then there's no shoehorning logic into your VBA or merging recordsets--you just load that query to a recordset, spit out the results as needed and use FontColor to assign the color.

 

[Mike Krailo]

Your modification of the code doesn't make sense to do two loops because added together they will create 34 records. You only need to go through the loop once or as plog said create one single query to pick out the one's that get the red font.

I like plog's idea of creating one single query the picks out all records that meet either of your conditions and updating an added field with the desired color. Or you can iterate through your new query that contains the count of repeat failures.

rsTwoConditions.MoveFirst
Do While Not rsTwoConditions.EOF
    If rsTwoConditions!Duration >=30 or rsTwoConditions!StationRepeat >= 3 Then
        ….red font
    Else
        …black font
    End If
    rsTwoConditions.MoveNext
Loop
rsTwoConditions.Close

 

[Indigo]

Mike and plog, I understand what you are suggesting, but I am not certain as to how to arrive at the desired results in SQL.

@Mike Krailo you wrote: "...you can iterate through your new query that contains the count of repeat failures." Do you have any suggestions on how I can do this? I have been trying several options and have not been able to come up with the desired solution.