Looping

[AmberB1849]

We have an access database that is used to pull work based on the users profile. Currently the database is only set up to pull 1 task at a time. We woudl like to modify the database to pull mutiple tasks (10) at one time. Where in this below code would I make this change?

Set rstDeskItems = CurrentDb.OpenRecordset("Funnel - Task to be assigned - Current User", dbOpenSnapshot)
If rstDeskItems.RecordCount > 0 Then
    ThisId = rstDeskItems![ID]
    'ThisPolNum = rstDeskItems![Policy Number]
    Set rstDeskItems = Nothing

    strSQL = "UPDATE Desk SET Desk.Specialist = (DLookUp(" & Chr(34) & "[Specialist]" & Chr(34) & "," & Chr(34) & "Specialist" & Chr(34) & "," & Chr(34) & "[N #]=" & Chr(34) & " & """""""" & Environ(" & Chr(34) & "Username" & Chr(34) & ") & """""""")) WHERE  (((Desk.Id)=(" & ThisId & ")) and Desk.Specialist is null);"
    Set qdfPullWork = CurrentDb.CreateQueryDef("", strSQL)
    qdfPullWork.Execute
    
    
    If qdfPullWork.RecordsAffected <> 1 Then
        MsgBox "Problem updating Desk record"
        Set qdfPullWork = Nothing
        
        
    Else
        Set qdfPullWork = Nothing


        If Len(ThisPolNum) > 0 Then
            ThisFieldName = "Policy Number"
            ThisFieldValue = ThisPolNum
        Else
            ThisFieldName = "Id"
            ThisFieldValue = ThisId
            
        
            
        End If
            

        'DoCmd.OpenForm "Assignment Desk", , , "[" & ThisFieldName & "] = " & ThisFieldValue, , , ThisId
    End If
Else
    Set rstDeskItems = Nothing
    MsgBox "There are currently no unassigned workflow items which match your profile."
End If


'MsgBox rstDeskItems.RecordCount
'Set rstDeskItems = Nothing

Exit_Command150_Click:
    With Me.Command150
        .Enabled = True
        .Caption = "Pull Work"
    End With
    
    Exit Sub

Err_Command150_Click:
    MsgBox Err.Description
    Resume Exit_Command150_Click

End Sub

[gershwyn]

Can you describe the desired behavior in more detail? It seems like the function is updating a single field on a single record to assign it to a user. If so, rather then running that in a loop I would modify the SQL to do all the updates at once. I would need to better understand the details of the table you're working with to do that, though.

Also, please use the code tags (with the # symbol) when posting your code... it is very hard to match up the ifs and elses when the code isn't properly indented.