I am trying to get the managerID that is <= 5. My code works great when the first result set is <=5 meaning
Code:
If CStr(rst.Fields(i).Value > 5) Then
a = DLookup("ManagerName", "tblEmpInfo", "userID=" & rst.Fields("managerID").Value)
b = DLookup("usrLvl", "tblGU", "usrName='" & a & "'")
c = DLookup("managerID", "tblGU", "usrName='" & a & "'")
If b <= 5 Then
rst.Edit
rst.Fields(i) = a
rst.Update
if the b <= 5 statement evaluates to True then we are set! My issue with my code is that the ELSE only iterates once, so if e returns 10, well we get 10 instead of continuing to execute until we hit 5. How should this code be re-factored in order to continue to execute UNTIL we get a managerID <= 5?
Code:
Function GetLowest()
Dim rst As Recordset
Dim i As Integer
Set rst = CurrentDb.OpenRecordset("InactiveSiteUsers")
While Not rst.EOF
For i = 3 To rst.Fields.Count - 1
If CStr(rst.Fields(i).Value > 5) Then
a = DLookup("ManagerName", "tblEmpInfo", "userID=" & rst.Fields("managerID").Value)
b = DLookup("usrLvl", "tblGU", "usrName='" & a & "'")
c = DLookup("managerID", "tblGU", "usrName='" & a & "'")
If b <= 5 Then
rst.Edit
rst.Fields(i) = a
rst.Update
Else
d = DLookup("ManagerName", "tblEmpInfo", "userID=" & c)
e = DLookup("usrLvl", "tblGU", "usrName='" & d & "'")
End If
End If
Next i
rst.MoveNext
Wend
rst.Close
End Function