Report stops module from working

I have a program that opens three tables, reading one, and writing values to the other two depending on what is found in the first. The program worked fine until I tried to open and print a report after I passed through each record. Now I get a message saying I can't go to the next record (this occurs on the MoveNext just before the Loop command, and the report doesn't print either. Anyone see what I am doing wrong?

Private Sub cmdRunInitialIssueProgram_Click()
On Error GoTo Err_cmdRunInitialIssueProgram_Click

Dim AgentConsolKey As String
Dim rsBase As ADODB.Recordset
Dim rsDetails As ADODB.Recordset
Dim IssueBaseAuditAutonumber As Long
Dim stDocName As String
Dim ACat1, ACat2, ACat3 As Integer

Set rsDetails = New ADODB.Recordset

rsDetails.Open "tblIssueDetails", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable

Set rsBase = New ADODB.Recordset

rsBase.Open "tblIssueBase", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable

Set rsAgents = New ADODB.Recordset

rsAgents.Open "tblAgents", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable

'Build the SQL statement for the report printed with each pass
strReportSQL = "SELECT tblIssueBase.AgentConsolKey, tblAgents.AgentCounty, tblAgents.AgentSequence, tblAgents.AgentType, tblIssueBase.IssuedDate,"
strReportSQL = strReportSQL & "tblIssueDetails.StampYear, tblStamps.StampCode, tblIssueDetails.StampClass, tblResidentCode.ResidentDescription, "
strReportSQL = strReportSQL & " tblStamps.Description , tblIssueDetails.IssueQuantity, tblStamps.StampCost, tblIssueBase.AuditAutonumber "
strReportSQL = strReportSQL & " FROM (tblAgents INNER JOIN tblIssueBase ON tblAgents.AgentConsolKey = tblIssueBase.AgentConsolKey) INNER JOIN "
strReportSQL = strReportSQL & " ((tblResidentCode INNER JOIN tblStamps ON tblResidentCode.ResidentCode = tblStamps.ResidentIndicator) INNER JOIN "
strReportSQL = strReportSQL & " tblIssueDetails ON (tblStamps.StampClass = tblIssueDetails.StampClass) AND (tblStamps.StampYear = tblIssueDetails.StampYear))"
strReportSQL = strReportSQL & " ON tblIssueBase.AuditAutonumber = tblIssueDetails.IssueBaseAuditAutonumber WHERE tblIssueBase.AuditAutonumber = "

ACat1 = 1
ACat2 = 2
ACat3 = 3

Do Until rsAgents.EOF
InitialIssueCategory = rsAgents.Fields("InitialIssueCategory")
AgentConsolKey = rsAgents.Fields("AgentConsolKey")
AgentConsolKeyFind = AgentConsolKey
rsBase.AddNew
rsBase.Fields("AgentConsolKey") = AgentConsolKey
rsBase.Fields("IssuedDate") = Date
rsBase.Fields("DateModified") = Date
rsBase.Fields("TimeModified") = Time
TotalIssues = TotalIssues + 1
rsBase.Fields("InitialIssueSequence") = TotalIssues
rsBase.Update
'Now go back to that issue base table record to get its autonumber
sSQL = "InitialIssueSequence = " & TotalIssues
rsBase.MoveFirst
rsBase.Find sSQL
IssueBaseAuditAutonumber = rsBase.Fields("AuditAutonumber")
'Add a detail record for every stamp class
rsDetails.AddNew
rsDetails.Fields("StampClass") = "A"
rsDetails.Fields("StampYear") = 2004
If InitialIssueCategory = 1 Then
rsDetails.Fields("IssueQuantity") = ACat1
ElseIf InitialIssueCategory = 2 Then
rsDetails.Fields("IssueQuantity") = ACat2
ElseIf InitialIssueCategory = 3 Then
rsDetails.Fields("IssueQuantity") = ACat3
Else: rsDetails.Fields("IssueQuantity") = 0
End If
rsDetails.Fields("issueBaseAuditAutonumber") = IssueBaseAuditAutonumber
DetailRecordsAdded = DetailRecordsAdded + 1
rsDetails.Update
'Print the Stamp Issue Report for this agent
strReportSQL = strReportSQL & IssueBaseAuditAutonumber & """"
DoCmd.GoToRecord , , AgentConsolKey = AgentConsolKeyFind
DoCmd.OpenReport stDocName, acNormal, , strReportSQL
DoCmd.Close "rptStam****uesToAgents"
'go to the next agent record
rsAgents.MoveNext
Loop

rsDetails.Close
Set rsDetails = Nothing
rsAgents.Close
Set rsAgents = Nothing
rsBase.Close
Set rsBase = Nothing

Exit_cmdRunInitialIssueProgram_Click:
Exit Sub

Err_cmdRunInitialIssueProgram_Click:
MsgBox Err.Description
Resume Exit_cmdRunInitialIssueProgram_Click

End Sub

This lines serves as a filter to your originial report:



DoCmd.OpenReport stDocName, acNormal, , strReportSQL



Meaning this is basically a filter or parameter to your current report recordsource. Say your originial report is bound to this data source:



"SELECT * FROM myTable"



A viable filter string passed to the report object could be:



sFilter = "Field1 = '" & Text1.Text & "' AND Field2=" & Cint(myNumVal)



This is serving as a where clause or a modification that the original recordsource can resolve.

You are attempting to pass any entirely new recordset to the pre-existing report. For one, what fields are on this report at design time..will the physical report object support the query you are passing..check this first and foremost. Remember, your Access report has a defined data source at this point and fields placed on it..everything must work together. If your report structure will support this query, then to assign a completely new data source to the report, use the .recordsource method



myReport.RecordSource = sSelect



Where sSelect is your complete query string declaration.

I think I understand your advice. I tried to use this in place of the other SQL statement:

strFilter = "tblIssueBase.AuditAutonumber = " & IssueBaseAuditAutonumber & """"

Now I get a message "You can't go to the specified record." Yet the record in question has already been added to the recordset. Why would this happen?? Thanks.