Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 08-01-2018, 08:26 AM   #31
laza
Newly Registered User
 
Join Date: Aug 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
laza is on a distinguished road
Re: Exporting reports by group

Hi all,

I am VERY new to VBA!

I found this code followed the steps and now troubleshooting...

My report is based on a query ONLY and the query prompts the user to select a date range (i.e. 07/01/2018 to 07/21/2018.) when opening the report.

I followed the steps with the exception that the PDF button location is on the report.

When I click the button - it begins printing but then errors out "Run-time error '2501': The OutputTo action was cancelled." Not sure why?

CODE BELOW:

Private Sub Command128_Click()

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Client ID] FROM [Schedule] ORDER BY [Client ID];", dbOpenSnapshot)

Do While Not rst.EOF
strRptFilter = "[Client ID] = " & rst![Client ID]

DoCmd.OutputTo acOutputReport, "BillingReport", acFormatPDF, "C:\Users\sandy\Desktop\test" & "" & rst![Client ID] & ".pdf"
DoEvents
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub

Private Sub Report_Close()

strRptFilter = vbNullString

End Sub

Private Sub Report_Open(Cancel As Integer)

If Len(strRptFilter) <> 0 Then
Me.Filter = strRptFilter
Me.FilterOn = True
End If

End Sub

Thanks in advance!
Attached Images
File Type: jpg 1.JPG (15.3 KB, 14 views)
File Type: jpg 2.JPG (11.7 KB, 12 views)
File Type: jpg 4.JPG (21.3 KB, 10 views)
File Type: jpg 5.JPG (19.7 KB, 9 views)
File Type: jpg 6.jpg (86.9 KB, 8 views)

laza is offline   Reply With Quote
Old 08-01-2018, 08:36 AM   #32
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Exporting reports by group

Quote:
Originally Posted by laza View Post
I followed the steps with the exception that the PDF button location is on the report.
Reports are for display, not interaction. You would want the button on a form.
Mark_ is offline   Reply With Quote
Old 08-01-2018, 09:15 AM   #33
laza
Newly Registered User
 
Join Date: Aug 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
laza is on a distinguished road
Re: Exporting reports by group

Mark thanks for the quick response. Can I just have a button on the form that's it? I ended up getting the pdf button to work on the report (the troubles was with the location path. I took it out and it worked perfectly)... but my problem now is it isn't separating the files based on client ID.

Please excuse my beginner-ness!

Thanks,

Sandy

laza is offline   Reply With Quote
Old 08-01-2018, 09:19 AM   #34
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Exporting reports by group

Normally you would have the button on a form and the form would also have fields for the dates. That way the user enters what they want first and hit the button. Makes it so they can verify their entry.

This also means you can have a text field (maybe with file picker) to select WHERE you want to save the files. That way you don't have to code in "C:\Users\sandy\Desktop\test" but can let the users decide where to put them.
Mark_ is offline   Reply With Quote
Old 08-01-2018, 09:26 AM   #35
laza
Newly Registered User
 
Join Date: Aug 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
laza is on a distinguished road
Re: Exporting reports by group

Not much validation needs to be done other than the dates which on my report I have a field of duration where she can check what dates she put in.

Understood on the form; however, this is due by tomorrow AM.

Any suggestions on how I can get these pdfs to print seperately based on client ID or is the form the only option I have?
laza is offline   Reply With Quote
Old 08-01-2018, 09:56 AM   #36
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Exporting reports by group

DoCmd.OutPutTo does not allow you to pass a "Where" clause the same way as DoCmd.OpenReport.

Since you can't pass the parameters in the DoCmd line, you will need to update your QUERY to use values you have on your calling form. Upside, you could have a text control (or potentially a label referenced by LabelName.Caption) that you update inside your loop and use in your query to limit your data to ONLY the values you are looking for.

Let us know if this is something you can work with.
Mark_ is offline   Reply With Quote
Old 08-01-2018, 12:23 PM   #37
laza
Newly Registered User
 
Join Date: Aug 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
laza is on a distinguished road
Re: Exporting reports by group

Not sure what all that means.....unfortunately I am a beginner of two days with access.

I'm going to try and create a form based on my query and test out the form print to pdf button. Hopefully this will save multiple pdfs with only that specific client id's information within in.

Any beginner tips are welcome!

laza is offline   Reply With Quote
Old 08-01-2018, 12:51 PM   #38
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Exporting reports by group

OK, to make this easy;
1) Make a BLANK form. This is called an "Unbound" form.
2) Add Textbox control for your StartDate
3) In your QUERY, go down to the criteria for that you use StartDate in and use the form's <= StartDate for your criteria. Use this LINK for help if you are not comfortable using form controls in queries.
4) Add a textbox on your form to hold the [Client ID]
5) For each record, where you currently have
Code:
strRptFilter = "[Client ID] = " & rst![Client ID]
you will replace it with
Code:
Me.YourTextFieldForTheClientID = [rst![Client ID]
6) You will have the same type of code as 3 to set your querie's [Client ID] = your forms's [Client ID]

This should give you what you are looking for; transactions on or after your startdate and matching the client ID for each of the reports you generate.

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
exporting reports as pdf from vba? CHAOSinACT Modules & VBA 3 09-07-2010 04:28 PM
Exporting Reports aziz rasul Modules & VBA 3 06-21-2006 11:39 PM
Exporting Reports Dazzla Reports 1 05-24-2005 07:22 PM
exporting reports stevehooley Reports 0 03-14-2005 05:57 AM
Exporting Reports SASHA_D Reports 6 06-04-2003 02:08 AM




All times are GMT -8. The time now is 09:46 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World