programatically re-open report, re-print it and then close it.

[asrul]

Dear All,
I have below code on on click event of Preview_btn in Menu_frm

DoCmd.DeleteObject acTable, "Worst10byRejectItem_tbl"
strSQL = "SELECT TOP 10 " & vbCrLf & _
" Format([ReinspectedDate],""mmm"") AS [Month] " & vbCrLf & _
" , Format([ReinspectedDate],""yyyy"") AS [Year] " & vbCrLf & _
" , ModelID " & vbCrLf & _
" , Sum([UnitAccepted]) AS [Unit Accepted] " & vbCrLf & _
" , Sum([UnitRejected]) AS [Unit Rejected] " & vbCrLf & _
" , Sum([" & cbo_RejectItem.Column(1) & _
"]) AS [Fail quantity]" & vbCrLf & _
" INTO [Worst10byRejectItem_tbl] " & vbCrLf & _
" FROM [Outgoing_qry] " & vbCrLf & _
" WHERE Format([ReinspectedDate],""mmm"")=""" & Me.[Month_cbo] & """" &
vbCrLf & _
" AND Format([ReinspectedDate],""yyyy"")=""" & Me.[Year_cbo] & """ " &
vbCrLf & _
" AND ([UnitRejected])<>""0"" " & vbCrLf & _
" GROUP BY Format([ReinspectedDate],""mmm"") " & vbCrLf & _
" , Format([ReinspectedDate],""yyyy"") " & vbCrLf & _
" , ModelID " & vbCrLf & _
" ORDER BY Sum([" & cbo_RejectItem.Column(1) & "]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"
CurrentDb().Execute strSQL, dbFailOnError
DoCmd.OpenForm "WorstByItem_frm", acNormal
Me.Visible = False

Then, after the WorstByItem_frm is opened, then I print out
WorstByItem_report from it.
To view another Reject Item, I have to go back to the Menu_frm and edit the
value of
cbo_RejectItem.

Now, I want to change the cbo_RejectItem become a multi select listbox so
that I can
programatically re-open WorstByItem_report, re-print it out, then re-close
it, until all
selected reject item in the multi select listbox is printed out.

Is it possible?.How to modified the code?

Hope I can explained it clearly and somebody would like to advise.

Regards,

 

[asrul]

I have done with main code modification but when I combine my code with the
Lebans code:
( Output report to PDF ), only the last item selected of the multi select
listbox is outputted to PDF.
Hope somebody like to give me a clue.

Here is the modified code:

================
Declaration is skipped
================
For Each varRejectItem In Me.RejectItem_list.ItemsSelected
DoCmd.DeleteObject acTable, "Worst10byRejectItem_tbl"
strSQL = "SELECT TOP 10 " & vbCrLf & _
" Format([ReinspectedDate],""mmm"") AS [Month] " &
vbCrLf & _
" , Format([ReinspectedDate],""yyyy"") AS [Year] " &
vbCrLf & _
" , ModelID " & vbCrLf & _
" , Sum([UnitAccepted]) AS [Unit Accepted] " &
vbCrLf & _
" , Sum([UnitRejected]) AS [Unit Rejected] " &
vbCrLf & _
" , Sum([" & RejectItem_list.Column(1) & _
"]) AS [Fail quantity]" & vbCrLf & _
" INTO [Worst10byRejectItem_tbl] " & vbCrLf & _
" FROM [Outgoing_qry] " & vbCrLf & _
" WHERE Format([ReinspectedDate],""mmm"")=""" &
Me.[Month_cbo] & """" & vbCrLf & _
" AND Format([ReinspectedDate],""yyyy"")=""" &
Me.[Year_cbo] & """ " & vbCrLf & _
" AND ([UnitRejected])<>""0"" " & vbCrLf & _
" GROUP BY Format([ReinspectedDate],""mmm"") " &
vbCrLf & _
" , Format([ReinspectedDate],""yyyy"") " & vbCrLf &
_
" , ModelID " & vbCrLf & _
" ORDER BY Sum([" & RejectItem_list.Column(1) & "])
DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSQL, dbFailOnError

blRet = ConvertReportToPDF("The worst by reject item" _
, vbNullString, strPath & strMonth & " " & strYear &
_
"\The worst by reject item_" &
Me.RejectItem_list.Column(1) & ".pdf", _
False, False, 0, "", "", 0, 0)

Debug.Print Me.RejectItem_list.ItemData(varRejectItem)

Next varRejectItem
End Sub

 

[asrul]

To Mr Lebans,
Do you have any idea why I can print (1 snapshot report of) the last item
selected only?