We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Additional code needs adding to output report in snp format

vipa2000
vipa2000 asked
on
Medium Priority
633 Views
Last Modified: 2008-09-22
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
Comment
Watch Question

Author

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

Commented:
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

Author

Commented:
10weekherb

Author

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

Commented:
Ahh.. I see now.. It's never going to be called

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

Dave

Commented:
The code appears after the Exit Sub, so it will never get called - move it up so it is before the Exit Sub line!

Commented:
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

Author

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

Commented:
<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?

Author

Commented:
stevbe it does and does run. let me have a look.that said my code goes to next step.
regards vipa
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Excellent. Problem resolved
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.