Report to multiple text files based on filtered value (1 Viewer)

[mato61]

Hello,
i created report "rpt" with a few subrpts. I would like to export this report to word by value in column M_AGENDA_KOD. M_AGENDA_KOD column has about 370 rows, so i do not want to export each manually.

So i would like to have each M_AGENDA_KOD value as separated word file named by that M_AGENDA_KOD value.
Is that even possible? I went through a lot of threads, but still not sure about that.

thank you in advance

 

[GinaWhipp]

Sounds like this might do the trick...
http://www.access-diva.com/vba3.html

 

[mato61]

Sounds like this might do the trick...


I used this:

Sub expToRTF()

Dim intCounter As Integer
Dim cboCode As ComboBox

Set cboCode = Me![ M_AGENDA_ID ]
For intCounter = 0 To cboCode.ListCount - 1
DoCmd.OpenReport " rptIniciální analýza agendy ", acViewPreview, , _
"[ M_AGENDA_KOD ] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
DoCmd.OutputTo acReport, " rptIniciální analýza agendy ", acFormatRTF, " C :/ Users\martin.janota\Desktop\test formularu\Master\webtest\Master " & Format(intCounter, "000") & ".rtf"
DoCmd.Close acReport, " rptIniciální analýza agendy "
Next

End Sub

but get error message "invalid use of Me keyword" as a newbie i do not know how to handle it

 

[GinaWhipp]

Remove the spaces inside the brackets.

 

[mato61]

Remove the spaces inside the brackets.

Hello, i am not able to run this macro, sorry Gina, i do not why. But meantime i wrote something like this

Sub expt2PDF()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT DISTINCT M_AGENDA_ID FROM M_AGENDA")

With rs

Do Until .EOF

DoCmd.OpenReport "rpt_MAIN_REPORT", _
acViewPreview, _
WhereCondition:="M_AGENDA_ID = " & !M_AGENDA_ID, _
WindowMode:=acHidden

DoCmd.OutputTo acOutputReport, _
"rpt_MAIN_REPORT", _
acFormatPDF, _
"C:\Users\martin.janota\Desktop\test formularu\Master\webtest\Master\ " & !M_AGENDA_ID & ".pdf"

DoCmd.Close acReport, "rpt_MAIN_REPORT", acSaveNo

.MoveNext

Loop

.Close

End With

It is working properly. There is one subreport connected to the main report, this subreport has another 5 subreports. They are connected through the M_AGENDA_ID.
Question is how to get pdf files named by column M_AGENDA_KOD. I would say that just changing M_AGENDA_ID by M_AGENDA_KOD here "C:\Users\martin.janota\Desktop\test formularu\Master\webtest\Master\ " & !M_AGENDA_ID & ".pdf" but it is not working with error statement: "Item not found in this selection"

 

[GinaWhipp]

Before we go changing anything that works... Have you confirmed the path? Are you really trying to save the report to the Desktop?

 

[mato61]

Before we go changing anything that works... Have you confirmed the path? Are you really trying to save the report to the Desktop?

As i wrote, if i run that macro it is working. It will do export how i want, each file contains one record from report. But now files are named by M_AGENDA_ID (means numbers from 1 till 374). M_AGENDA_ID is ID which conects all records within report. But i would like to get files named by M_AGENDA_KOD.
I checked the path as well.

 

[GinaWhipp]

Oops, misread your original post. Okay, where is M_AGENDA_KOD?

 

[mato61]

Oops, misread your original post. Okay, where is M_AGENDA_KOD?

M_AGENDA_KOD is column in M_AGENDA.
M_AGENDA is a table with PK M_AGENDA_ID and other columns, one of these columns is M_AGENDA_KOD.

 

[GinaWhipp]

Is it on the Form? Sounds like that is why you are getting that message?

 

[mato61]

Is it on the Form? Sounds like that is why you are getting that message?

I have M_AGENDA_KOD on main report, not all subreports contain M_AGENDA_KOD.

 

[GinaWhipp]

Okay then what do you want to happen if they don't contain it?

Let's see, let's try putting a field on the subreport that is equal to the field needed on the subreport and make it invisible. Then you can include by using Me.WhateverYouNamedTheHiddenField. But you need something to handle when it is NULL.

 

[mato61]

Okay then what do you want to happen if they don't contain it?

Let's see, let's try putting a field on the subreport that is equal to the field needed on the subreport and make it invisible. Then you can include by using Me.WhateverYouNamedTheHiddenField. But you need something to handle when it is NULL.

i am quite confused now. I thought it will be simple and i am overlooking something.
my main report contains just two comboboxes with M_AGENDA_ID (one for M_AGENDA_KOD and one for M_AGENDA_NAZEV from M_AGENDA table) to identify report. This report have picture, table of contents and a lot of necessary things... Then i attached one subreport. Subsequently this subreport contains 5 another subreports.
These reports collect data from many tables which are connected via M_AGENDA_ID.
I just want to get files named by KOD, not ID as i have i that code above.

Could you provide me more detailed info which part should be replaced with Me.WhateverYouNamedTheHiddenField ??

 

[GinaWhipp]

You said...

I have M_AGENDA_KOD on main report, not all subreports contain M_AGENDA_KOD.

So, I was assuming that was the problem. So, it is me that is confused and perhaps I am instructing you incorrectly.

Before you do anything... I asked this earlier but I see it is not yet answered. Are you using a button on a Form? If note, how are you calling this Report?

 

[mato61]

You said...

So, I was assuming that was the problem. So, it is me that is confused and perhaps I am instructing you incorrectly.

Before you do anything... I asked this earlier but I see it is not yet answered. Are you using a button on a Form? If note, how are you calling this Report?

Sorry!!! No, i am not using a button on a Form. I do not need to call it through a Form. I just created report, and then need to run macro. I do not need open it.

 

[GinaWhipp]

Hmm, never tried it without a From...

Okay, then for whatever reason it can't find M_AGENDA_KOD in the Record Source of the report, which is what this message is saying...

"C:\Users\martin.janota\Desktop\test formularu\Master\webtest\Master\ " & !M_AGENDA_ID & ".pdf" but it is not working with error statement: "Item not found in this selection"

So, one way or the other you have to get that into the Report.

 

[mato61]

Hmm, never tried it without a From...
Okay, then for whatever reason it can't find M_AGENDA_KOD in the Record Source of the report, which is what this message is saying...
So, one way or the other you have to get that into the Report.

Hi Gina,
it is strange but i am getting what i want finally. I tried to add to each subreport that M_AGENDA_KOD, but it wasnt working.
So i was wondering what is wrong and i just tried this:
Set rs = CurrentDb.OpenRecordset( _
"SELECT DISTINCT M_AGENDA_KOD, M_AGENDA_ID FROM M_AGENDA")
so i just forget to add to distinct statement both M_AGENDA_KOD and M_AGENDA_ID... :banghead:
Thank you so much for your time!!!!

 

[GinaWhipp]

Hmm, we both missed that! Glad you finally got it working...

 

[mato61]

Hmm, we both missed that! Glad you finally got it working...

Hello, one additional question here > is it possible to modify the macro i posted here to export reports just for selected value? or better question how
:banghead:

 

[GinaWhipp]

So, you are wanting to modify it just for the current report? What would be the *trigger* to know which you want all or just the current... we are going to need one.