Additional code needs adding to output report in snp format

I have the code below with an additional area of code in between the ************ (look at the bottom)
The code runs fine and outputs reports with the snp extension and unique file name. This code is sat behind a button, I now want to add the code between the ****. Now i have simply placed it where i thought it would work. It doesn't. What would happen is a parameter is then asked for by the reports underlying query. I think this is purely an issue of where i have placed the code. regards vipa

Private Sub Command10_Click()
On Error GoTo Error_Routine
   
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim RS As DAO.Recordset
    Dim ctlComboBox As Control

' the underlying recordset for the report is a query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qry_herb_exc_design&conagro")
        qdf.Parameters(0) = Forms!frm_shedno_herb!cmb_shedherb.Value
    Set RS = qdf.OpenRecordset()
    Set ctlComboBox = Forms!frm_shedno_herb!cmb_shedherb
   
  Dim i As Integer   'loop counter for items selected from combobox
                     'enumerate through each of the items in combo box
         
For i = 0 To ctlComboBox.ListCount - 1

    ctlComboBox = cmb_shedherb.ItemData(i)
    ctlComboBox.Requery
         
    'preview report to apply filter
    DoCmd.OpenReport "rpt_herb_exc_design&conagro_wklist", acViewPreview, , "Left([Funct# Location],6)='" & ctlComboBox.ItemData(i) & "'"
    DoCmd.OutputTo acOutputReport, "rpt_herb_exc_design&conagro_wklist", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & ctlComboBox.ItemData(i) & ".snp", False
    DoCmd.Close acReport, "rpt_herb_exc_design&conagro_wklist"
   
Next i

RS.Close
Set RS = Nothing
Set ctlComboBox = Nothing
i = 0
       
Exit_Continue:
        Exit Sub
************************  
    'output resource sheet to web area
    DoCmd.OutputTo acOutputReport, "rpt_plan_vs_res_herbs", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & ".snp", False
*************************
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub
vipa2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vipa2000Author Commented:
altered it so it actually attempts to open the report, but it doesn't!

DoCmd.OpenReport "rpt_plan_vs_res_herbs", acViewPreview
    DoCmd.OutputTo acOutputReport, "rpt_plan_vs_res_herbs", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & ".snp", False
0
flavoCommented:
First thing I see is that you haven't given you file a file name

DoCmd.OutputTo acOutputReport, "rpt_plan_vs_res_herbs", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & <** Something needed here> & ".snp"

What do you want it to be called?

Dave
0
vipa2000Author Commented:
10weekherb
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vipa2000Author Commented:
changed my code to

'output resource sheet to web area
    DoCmd.OpenReport "rpt_plan_vs_res_herbs", acViewPreview
    DoCmd.OutputTo acOutputReport, "rpt_plan_vs_res_herbs", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & "10weekherb", ".snp", False

still no joy, regards vipa
0
flavoCommented:
Ahh.. I see now.. It's never going to be called

Do you want it output just after the first report is saved?

Dave
0
cquinnCommented:
The code appears after the Exit Sub, so it will never get called - move it up so it is before the Exit Sub line!
0
stevbeCommented:
drop the <ctlComboBox.Requery> ... this continually reset the combobox which by default has nothing selected when it i Requeried when it is not bound to a data field.

Steve
0
vipa2000Author Commented:
right trying to digest the last 3 comments.
stevbe, without the requery i don't believe the initial run of reports will work.
flavo, about 13 reports are outputted before i go to the final report i want outputing
cquinn, you are close, i moved the code up and it ran. it opened the said report with a parameter but then stalled with error 2498 wrong data type for one of the arguements.

stalls on this line

DoCmd.OutputTo acOutputReport, "rpt_plan_vs_res_herbs", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & "10weekherb", ".snp", False

full code as below

Private Sub Command10_Click()
On Error GoTo Error_Routine
   
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim RS As DAO.Recordset
    Dim ctlComboBox As Control

' the underlying recordset for the report is a query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qry_herb_exc_design&conagro")
        qdf.Parameters(0) = Forms!frm_shedno_herb!cmb_shedherb.Value
    Set RS = qdf.OpenRecordset()
    Set ctlComboBox = Forms!frm_shedno_herb!cmb_shedherb
   
  Dim i As Integer   'loop counter for items selected from combobox
                     'enumerate through each of the items in combo box
         
For i = 0 To ctlComboBox.ListCount - 1

    ctlComboBox = cmb_shedherb.ItemData(i)
    ctlComboBox.Requery
         
    'preview report to apply filter
    DoCmd.OpenReport "rpt_herb_exc_design&conagro_wklist", acViewPreview, , "Left([Funct# Location],6)='" & ctlComboBox.ItemData(i) & "'"
    DoCmd.OutputTo acOutputReport, "rpt_herb_exc_design&conagro_wklist", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & ctlComboBox.ItemData(i) & ".snp", False
    DoCmd.Close acReport, "rpt_herb_exc_design&conagro_wklist"
   
Next i

RS.Close
Set RS = Nothing
Set ctlComboBox = Nothing
i = 0
   
     'output resource sheet to web area
    DoCmd.OpenReport "rpt_plan_vs_res_herbs", acViewPreview
    DoCmd.OutputTo acOutputReport, "rpt_plan_vs_res_herbs", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & "10weekherb", ".snp", False
       
Exit_Continue:
        Exit Sub

Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub
0
stevbeCommented:
<stevbe, without the requery i don't believe the initial run of reports will work.>
if you need to do this 1 time before the run then put that before the loop. the way you have it now ain't gonna work :-(

does your code compile ... Debug --> Compile   menu?
0
vipa2000Author Commented:
stevbe it does and does run. let me have a look.that said my code goes to next step.
regards vipa
0
puppydogbuddyCommented:
Vipa,
You need to open another recordset to process the additional report because your new report has a different recordsource.  Also, you have an error in your Output to snapshot syntax.  See the revised code below:

Private Sub Command10_Click()
On Error GoTo Error_Routine
   
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim RS As DAO.Recordset
    Dim ctlComboBox As Control
    Dim RS2 As DAO.Recordset           ' new recordset to output separate report
    Dim qdf2 As DAO.QueryDef            'new QueryDef
 
' process the first report
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qry_herb_exc_design&conagro")
        qdf.Parameters(0) = Forms!frm_shedno_herb!cmb_shedherb.Value
    Set RS = qdf.OpenRecordset()
    Set ctlComboBox = Forms!frm_shedno_herb!cmb_shedherb
   
  Dim i As Integer   'loop counter for items selected from combobox
                     'enumerate through each of the items in combo box
         
For i = 0 To ctlComboBox.ListCount - 1
 
    ctlComboBox = cmb_shedherb.ItemData(i)
    ctlComboBox.Requery
         
    'preview report to apply filter
    DoCmd.OpenReport "rpt_herb_exc_design&conagro_wklist", acViewPreview, , "Left([Funct# Location],6)='" & ctlComboBox.ItemData(i) & "'"
    DoCmd.OutputTo acOutputReport, "rpt_herb_exc_design&conagro_wklist", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & ctlComboBox.ItemData(i) & ".snp", False
    DoCmd.Close acReport, "rpt_herb_exc_design&conagro_wklist"
   
Next i
 
RS.Close
Set RS = Nothing
Set ctlComboBox = Nothing
i = 0

    'process the second report
      Set qdf2 = db.QueryDefs("qry_plan_vs_res_herbs")   ' assumes no parameters coming from a combo box
    Set RS2 = qdf.OpenRecordset()
   
    DoCmd.OpenReport "rpt_plan_vs_res_herbs", acViewPreview   'output resource report via snapshot to web area
    DoCmd.OutputTo acOutputReport, "rpt_plan_vs_res_herbs", acFormatSNP, "P:\Planning Engineers\Weekly Reports\" & "10weekherb" & ".snp", False
   'close resource report
    DoCmd.Close acReport, "rpt_plan_vs_res_herbs"
 
RS2.Close                                                 'closing code for second recordset
Set RS2 = Nothing
 
Exit_Continue:
        Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
       
 
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vipa2000Author Commented:
Excellent. Problem resolved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.