Urgent help needed with recordset issue

[Zaeed]

Ok... Im trying to generate a list of users with role 'STC' and department given from a form. Im doing this by first building a recordset of all users with the role of STC, and then based on that, building a second recordset to reference the department.

I.e.
Usertable
User - Role - Department
bob - STC - 1
frank - STC - 2

Location table
ID - location
1 - upstairs
2 - downstairs

The first recordset (rst1) would grab both bob and frank. The second record set uses the department field from rst1 to pull the matching record from the location table, and then compares that to a value on a form.
This is used in a grand scheme to build an array of users for emailing.
The issue is that i get stuck in a loop in the code in bold below. For some reason it just sits there as if i've created rst2 with infinite fields. The msgbox i have there gives me the same value for fld2.name.
i've tried to fix it but have failed, and am in desperate need of a fix..

Any help is extremely appreciated.

        If (DLookup("SafetyText", "tbl_Change", "[ChangeNumber] = " & GetChangeNumber())) = "No Safety risk" Then
            strTemp = "SELECT * FROM qry_STC"
            Set rst = CurrentDb.OpenRecordset(strTemp, dbOpenSnapshot)
                If rst.EOF = True And rst.BOF = True Then
                Else
                    Do Until rst.EOF = True
                        With rst
                            For Each fld In rst.Fields
                            X = MsgBox(rst.RecordCount)
                            strExtract = "SELECT * FROM tbl_Secondary_Location WHERE LocationID = " & rst!department
                            Set rst2 = CurrentDb.OpenRecordset(strExtract, dbOpenSnapshot)
                            primLoc = DLookup("Primary_Location", "tbl_Change", "[ChangeNumber]= " & GetChangeNumber())
                                [B]With rst2[/B]
[B]                                  .MoveLast[/B]
[B]                                  If (rst2.EOF = False) Then[/B]
[B]                                      X = MsgBox(rst2.RecordCount)[/B]
[B]                                      For Each fld2 In rst2.Fields[/B]
[B]                                          If fld2.name = primLoc And fld2.Value = -1 Then[/B]
[B]                                              X = MsgBox("fld2" & i & fld2.name)[/B]
[B]                                              approvedEmail(i) = rst!Email[/B]
[B]                                              approvedName(i) = rst!FullName[/B]
[B]                                              i = i + 1[/B]
[B]                                          End If[/B]
[B]                                      Next[/B]
[B]                                  End If[/B]
[B]                              End With[/B]
                        Next
                        End With
                    Loop
                End If
        End If

 

[Rabbie]

Zaeed, You have posted enough times on this forum to know better. See this link for advice on choosing a thread title that may get you a quicker response.

Your code looks for ever because although you do a .movelast on rst2 you never move from that record so rst2.EOF is always false.