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?
Click to expand...

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.
 
D

DawnTreader

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.
Click to expand...

Hello and thanks for the reply

actually the loop is creating a criteria that i use when i open the
report.

Do Until rptID.EOF
cntrptIDs = cntrptIDs + 1
MsgBox rptID.Fields("ReportID")

strLinkCriteria = strLinkCriteria & " OR " & rptIDField & " =
" & rptID.Fields("ReportID")

notice how the strLinkCriteria is adding to itself.

Me.sfrmutblPrintReportIDs.Requery
rptID.MoveNext
Loop

i actually figured it out, here is the corrected code:


Dim rptName As String
Dim cn As adodb.Connection
Dim rptID As adodb.Recordset
Dim sqlrptID 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 DESC"
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
.Open
End With

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

rptID.MoveFirst
Do Until rptID.EOF
cntrptIDs = cntrptIDs + 1
strLinkCriteria = strLinkCriteria & " OR " & rptIDField & " =
" & rptID.Fields("ReportID")
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

the requery you were wondering about is a form and yeah it was legacy
code. i used to delete the list as i printed it, now i dont want to do
that so it is gone.
 
Top