Can't get the code to sort the data properly.

D

DawnTreader

Here is the data in table utblPrintReportIDs

PrintReportID ReportID
4 36519
5 36522
6 36528
7 36529
8 36532
9 36537

i have a form that looks at the above table and then prints all the
reports according to the ID of the report in the table. i use a button
to execute the following code:

Dim rptName As String
Dim cn As adodb.Connection
Dim rptID As adodb.Recordset
Dim sqlrptID As String
Dim sqlrptIDdel As String
Dim strLinkCriteria As String
Dim rptIDField As String
Dim cntrptIDs As Integer

If IsNull(Me.cboReportType) Then
MsgBox "no report selected"
Me.cboReportType.SetFocus
Exit Sub
End If

Set cn = CurrentProject.Connection

sqlrptID = "SELECT ReportID FROM utblPrintReportIDs ORDER BY
ReportID ASC"
rptName = Me.cboReportType.Column(2)
rptIDField = Me.cboReportType.Column(3)

Set rptID = New adodb.Recordset
With rptID
Set .ActiveConnection = cn
.Source = sqlrptID
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.CursorLocation = adUseClient
.Open
.Sort = "ReportID ASC"
End With

Application.SetOption "Confirm Action Queries", 0
cntrptIDs = 0

rptID.MoveFirst
Do Until rptID.EOF
cntrptIDs = cntrptIDs + 1
MsgBox rptID.Fields("ReportID")
strLinkCriteria = strLinkCriteria & " OR " & rptIDField & " =
" & rptID.Fields("ReportID") '& " Or " & strLinkCriteria
Me.sfrmutblPrintReportIDs.Requery
rptID.MoveNext
Loop

strLinkCriteria = Right(strLinkCriteria, Len(strLinkCriteria) - 4)
DoCmd.OpenReport rptName, acViewNormal, , strLinkCriteria,
acNormal, "Print"

'kill everything
rptID.Close
cn.Close
Set cn = Nothing
Set rptID = Nothing

End Sub

i have been fighting it to actually print the reports in ascending
order of the ReportID. is there anything in this code that would be
causing it to come out randomly?
 
D

David W. Fenton

m:
i have been fighting it to actually print the reports in ascending
order of the ReportID. is there anything in this code that would
be causing it to come out randomly?

I'd think your code would only print the last report in the list,
since the DoCmd.OpenReport is called after the Loop that walks
through the recordset.

Also, I wonder if this line:

Me.sfrmutblPrintReportIDs.Requery

....is interacting somehow. I don't see that you're using it for
anything anywhere and don't see any reason why you should want to
requery it for every row of your recordset.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top