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

GinaWhipp said:

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.

Click to expand...

Yes i would like to modify this macro for current report.
I would say that easier solution would by just type which M_AGENDA_KOD i want to export right to the macro /so question here is where to type these M_AGENDA_KOD/

or maybe more comfortable would be that when i run that macro it would prompt filter where i am able to mark which ones i want to export.

If it were me I would be using a Form so you could select All or Single but since you are not using a Form your second idea with a macro and a prompt sounds like it would work just a little manual for me but it will work.

GinaWhipp said:

If it were me I would be using a Form so you could select All or Single but since you are not using a Form your second idea with a macro and a prompt sounds like it would work just a little manual for me but it will work.

Click to expand...

ok, i will try it with form, but how to construct that form and then, how to run or modify that macro? could you briefly explain it to me pls??

Wait are you running a Macro or the code I gave you... I'm a little confused. As for how...

You have a little Form that opens prior to the Report, you make your selections from whatever controls you have on the Form and click the Preview Button OR you can just send them out to a file. Either way it makes it easier to say, I want to see all or one because it becomes a selection on the Form. Make sense?

GinaWhipp said:

Wait are you running a Macro or the code I gave you... I'm a little confused. As for how...

You have a little Form that opens prior to the Report, you make your selections from whatever controls you have on the Form and click the Preview Button OR you can just send them out to a file. Either way it makes it easier to say, I want to see all or one because it becomes a selection on the Form. Make sense?

Click to expand...

I am running this:
Sub mcrSelectedExport(ctrl As Control)

Dim varItem As Variant
Dim m_agenda_id_list As String
Dim strSQL As String
Dim rs As DAO.Recordset

' loop through list box and build value list
For Each varItem In ctrl.ItemsSelected
m_agenda_id_list = m_agenda_id_list & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
m_agenda_id_list = Mid(m_agenda_id_list, 2)

' build SQL statement restricted to selected items
' by means of an IN operation in the WHERE clause
strSQL = "SELECT m_agenda_id " & _
"FROM m_agenda " & _
"WHERE m_agenda_id IN(" & m_agenda_id_list & ") " & _
"ORDER BY m_agenda_kod"

Set rs = CurrentDb.OpenRecordset(strSQL)

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_KOD & ".pdf"

DoCmd.Close acReport, "rpt_MAIN_REPORT", acSaveNo

.MoveNext

Loop

.Close

End With
End Sub

I created form lst_m_agenda, where have list box with multiple selection property named mcrSelectedExport Me.lst_m_agenda. Then i have button there. I set up on click event mcrSelectedExport Me.lst_m_agenda.
And i got message: "MS Access cannot find the object mcrSelectedExport Me."
Btw i cannot see mcrSelectedExport when i am trying to run code right from VB.
:banghead:

To run a macro from within a Module you to use...

DoCmd.RunMacro

However, what you are showing me...

Sub mcrSelectedExport(ctrl As Control)

Is not a Macro, it appears to be a Module. In which case you would need to use...

mcrSelectedExport("Me.lst_m_agenda")

Now, if you are trying to build a filter to output a report then that is going to require additonal changes to your code. So which is it?

I tried to use form with multiple select list box to select which ones to be exported.
you are right it is module..
Even I used mcrSelectedExport("Me.lst_m_agenda") on click Event i get that message

Yeah, I figured it was going to need some work because you are not exporting the Control (the List Box) you exporting the results.

Adapt this...

'Partial code from http://www.baldyweb.com/
Then build your SQL without the WHERE

Then combine...

Then creating the Recordsource for the Report based on the above and then OutPut your report.

So, not the control the results and you have to build the RecordSource for the Report. Make sense?