Edit: Instead of updating the query, I set the SQL as the reports record source and now it is working:
Code:
    Report_AudixCallBacks.RecordSource = CallReportQuery(intReps(i), False)
    DoCmd.Save
    Report_AudixCallBacks.Requery
I am working on a database where users enter details about calls left on our answering machine. We then distribute these calls for people to make call backs to these individuals. The number of people that handle these call backs is variable, so I am updating my reports query on the fly to set the number of calls to retrieve (to distribute them equally based on number of people selected for call backs). If I run the report or query manually in Access, it works perfectly. But when I run the report in my form, it gives me the same data for every person. It worked as it should maybe 5 times out of my 50 tests. I don't understand why it decides to work sometimes.



When this is ran, it runs a query/report to pick X calls which are sent as a PDF via email, it then runs a query to flag these calls as handled so they are not picked by the next query to be emailed. Loop based on number of reps making callbacks.

Is there something I need to add to get the report to refresh. I've tried refresh and requery on the form and report and in multiple places.

Code:
For i = 0 To UBound(intReps)
    'Email callbacks
    Set qdfnew = CurrentDb.QueryDefs("CallReportQuery")
    qdfnew.SQL = CallReportQuery(intReps(i), False)
    Set qdfnew = Nothing
    
    On Error Resume Next
    DoCmd.SendObject acSendReport, "AudixCallBacks", acFormatPDF, lstPeople.Column(2, lstPeople.ItemsSelected(i)), , , "Audix Callbacks for " & Format(Date, "mm/dd/yy"), "See attached", True 

    'This flags calls as handled so they are not picked up by the next query to retrieve calls
    Set qdfnew = CurrentDb.QueryDefs("SetAsCalledBackQuery")
    qdfnew.SQL = SetAsCalledBackQuery(intReps(i))
    Set qdfnew = Nothing
    
    DoCmd.OpenQuery "SetAsCalledBackQuery"
Next i

The '8' in both of these queries is what my function inserts/updates since in my testing I have 16 calls, 2 reps.

Report query
Code:
SELECT TOP 8 [Calls].[Call Date], [Calls].[Call Time], [Extensions].[Extension], [Calls].[Policy Number], [Calls].[Named Insured], [Calls].[Caller], [Calls].[Comments]
FROM Calls, Extensions
WHERE (([Calls].[Call Type]=1) AND ([Calls].[Called Back]=False) AND ([Calls].[Extension]=[Extensions].[ID]));
Set as handled query
Code:
UPDATE Calls SET Calls.[Called Back] = True
WHERE (((Calls.[ID]) In (SELECT TOP 8 [ID] 
FROM [Calls]
WHERE (([Calls].[Call Type]=1) And ([Calls].[Called Back]=False)))));