Results 1 to 11 of 11
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Repeat Code Until If Statement Evaluates To True

    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

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There are just way too many variables in that code that are who knows what, so impossible to guess from here I'd say. You need to step through and watch them to see what happens. You might benefit from opening the Watch window as an aid to keeping an eye on what these values are. That will probably pinpoint your issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Let me start with the major issue that I have....

    I need the code to continue to execute until we get a value that is <= 5, not stop after the else block.

    Is it possible to alter the format of the code to continue iterating until we get a value for this?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Forget the vba code for the moment. In plain, simple English what are you trying to do?
    Tell us a little about your tables to tie things together.

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by orange View Post
    Forget the vba code for the moment. In plain, simple English what are you trying to do?
    Tell us a little about your tables to tie things together.

    I am needing each row in the table manger name with a usrLvl of <= 5.

    for further, the set-up is crazy
    I have to get the ManagerName from tblEmpInfo where the userID = the userID in the recordset
    Then from there i query tblGU and look up the usrLvl based off the usrName
    If the usrLvl <= 5 we are done
    If it is not let's repeat this process until we get a usrLvl that is


    Does that help clarify?

  6. #6
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    This is probably breaking about a million rules, but this does what I need it to....
    Code:
    Function GetLowest()
    Dim rst As Recordset
    Dim i As Integer
    Dim xyz As String
    
    
        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
    startOver:
    				If xyz = "a" Then
    					a = DLookup("ManagerName", "tblEmpInfo", "userID=" & c)
    				Else
    					a = DLookup("ManagerName", "tblEmpInfo", "userID=" & rst.Fields("managerID").Value)
    				End If
    				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
    					xyz = "a"
    					GoTo startOver
    				End If
    				
    			End If
    		Next i
    
    
        rst.MoveNext
    
    
        Wend
    
    
        rst.Close
    End Function

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe you should post an example of the data and specify the desired results. Simply repeating what you already wrote doesn't make it any clearer. You say you want to get each record where a value is <5, yet you also appear to be iterating over table fields:

    For i = 3 To rst.Fields.Count - 1

    so the code is confusing. Better to be explicit as Orange suggested. Sample data will help a lot, I think.
    Usually a query is sufficient for this, which could make much of that code unnecessary perhaps.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ????
    I have to get the ManagerName from tblEmpInfo where the userID = the userID in the recordset of InactiveSiteUsers
    Then from there i query tblGU and look up the usrLvl based off the usrName
    If the usrLvl <= 5 we are done

    It isn't clear if you are looking for(post #5) usrLvl <= 5 OR (from post #1) get the managerID that is <= 5.

    Describe an example with a specific InactiveSiteUser- let's call him Bob (userid 1234 ).
    What do you have to do/report for Bob? No vba code, just plain English.
    Where/How do ManagerName, ManagerId, userName and usrLvl fit in this?

    Clarity is key.

    Good luck.

  9. #9
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Sorry - I thought i explained below in English with no code what I was trying to accomplish.

    The main issue is the data is all over the place here and i'm trying to make the best of no table structure in these .csv files we receive.

    Lets see if this set of instructions helps clarify

    These three tables are imported from csv files (naming conventions are AWFUL)
    First we query to get a list of all users that have been inactive for > 15 days
    Then we iterate over that recordset and look at the managerID
    If managerID >= 5 we want to find that managers manager (Iterating from Store, to District, To Regional, etc etc)
    We lookup that managers name from tblEmpInfo
    We then lookup that managers level from tblGU
    if the new manager Level is <= 5 then we want to update the recordset with this manager name and lvl
    if the new manager Level is still >= 5 we want to repeat this process until we find a manager whose level is <= 5

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You can import a csv and use a query to put it into some order relevant to your needs.
    Use that query as the recordsource of your recordset.
    I am still confused as what you are trying to accomplish, or put another way, Why you are doing this process.

    So you find an InactiveSiteUser---so what--what do you do with it.

    Send some sample data (csv) and we can work through the processes. Make up some data if necessary for each of the tables involved and I'll help you work on some code to accomplish what you need --but clear requirement is needed.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is manager level a hierarchy? i.e.0=CEO, 22=Janitor?

    First we query to get a list of all users that have been inactive for > 15 days
    Then we iterate over that recordset and look at the managerID
    If managerID >= 5

    the above should be doable in one query

    we want to find that managers manager (Iterating from Store, to District, To Regional, etc etc)

    We lookup that managers name from tblEmpInfo
    We then lookup that managers level from tblGU


    could still be one query


    if the new manager Level is <= 5 then we want to update the recordset with this manager name and lvl
    if the new manager Level is still >= 5 we want to repeat this process until we find a manager whose level is <= 5


    for this you would use a recursive function - but what recordset are you referring to? The inactivesiteusers?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-06-2018, 11:55 AM
  2. Whaa? Why is this skipping my True If-Statement??
    By schulzy175 in forum Programming
    Replies: 3
    Last Post: 03-20-2018, 05:18 PM
  3. Replies: 7
    Last Post: 04-10-2014, 02:22 PM
  4. Replies: 30
    Last Post: 08-15-2012, 02:25 PM
  5. Replies: 7
    Last Post: 01-11-2012, 12:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums