• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

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
0
vipa2000
Asked:
vipa2000
  • 6
  • 2
  • 2
  • +2
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
vipa2000Author Commented:
Excellent. Problem resolved
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now