Multiple Report with vba loop (1 Viewer)

[farsey]

Hey,

I have a problem - I want to create a report for every customer id.

I have a Form which contains a List field - that reports me every ID for my representative.
I need a button which use my list field and loop this into my report to pdf but I don't know how to build a loop for this.

Private Sub Befehl210_Click()
      
    strReportName = "2022_PS_EN_Ohne_Abfrage"
    
    strCriteria = "[Vertretung]='" & Me![Vertretung] & "' AND [gepa]='" & Me![gepa] & "' "
    
    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria, acHidden
    myPDF = "C:\Fettzuschlag\" & Land & "_" & gepa & "_" & Name_1 & ".PDF"
    DoCmd.OutputTo objectType:=acOutputReport, objectName:="2022_PS_EN_Ohne_Abfrage", outputformat:=acFormatPDF, outputfile:=myPDF, outputquality:=acExportQualityPrint
End Sub

gepa is the customer ID in this case and Vertretung the representative.

hope someone understand it ^^
thanks

 

[CJ_London]

deleted - not answering the question

 

[jdraw]

See similar threads below.

 

[farsey]

for the context

I have representatives with own customers. They should get a report for each customer.

the export from a customer works, but only one customer id is searched for.

I want all customer ids to be queried for the representative and then saved as a report

 

[jdraw]

Do you have a table or query that shows which Customers are associated with which Representatives?

 

[farsey]

yes I have a query which contains customers and reprensentatives.

In my form it's like he ask me at first for the Representatives and then I have a field list created which also show me all the customer ids.

I was thinking I can use the field list to build a recordset of it but I don't find any way to do it.

 

[jdraw]

Show us whatever you can-- tables involved and query sql.
Sample report.. whatever

 

[Gasman]

yes I have a query which contains customers and reprensentatives.

In my form it's like he ask me at first for the Representatives and then I have a field list created which also show me all the customer ids.

I was thinking I can use the field list to build a recordset of it but I don't find any way to do it.

Build a string of the ID,s comma separated and use the IN clause of SQL

 

[farsey]

so I'm one step closer now ...


My Button creates now for every customer an own report .
Now I wanna select the "Vertretung" (Representatives) before and create only for them the reports

Is there any solution to get before an answer who is the Representative ?

Private Sub Befehl211_Click()
    
    '-----------------------------------------------------------------
    ' table: "2022"
    ' data in 2022: "gepa"
    ' report: "2022_PS_EN_Ohne_Abfrage"
    ' target: "C:\Fettzuschlag\" & Land & "_" & gepa & "_" & Name_1 & ".PDF"
    '-----------------------------------------------------------------
    
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strDatei As String, strWhere As String
    
    Set db = CurrentDb
    'The code should set the current database active
    strSQL = "SELECT Distinct [gepa],[Name 1],[Land],Vertretung FROM 2022"
    'The data field "gepa" from the table "2022" should be selected
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    'The first entry should be taken
    Do Until rs.EOF
        strDatei = "C:\Fettzuschlag\" & rs.Fields(2).value & "_" & rs.Fields(0).value & "_" & rs.Fields(1).value & ".pdf"
        'The file should be given the name of the data field in the target folder
        DoCmd.OpenReport "2022_PS_EN_Ohne_Abfrage", acViewPreview, , "gepa = '" & rs(0) & "'", acHidden
        'the report is opened and the invoice number is entered as a selection criterion.
        DoCmd.OutputTo acOutputReport, "2022_PS_EN_Ohne_Abfrage", acFormatPDF, strDatei, False
        'The PDF is generated and stored in the folder with the invoice number.
        DoCmd.Close acReport, "2022_PS_EN_Ohne_Abfrage"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
    Set db = Nothing
End Sub

 

[jdraw]

?? From post #1 I want to create a report for every customer id.
Now I wanna select the "Vertretung" (Representatives) before and create only for them the reports

What are you trying to achieve in simple, plain English?
If you're more comfortable with German, than write the details in German and use Google translate to post in English.

One step closer?? How many steps do you plan until you get what you need?

 

[farsey]

sorry for any confusion - it's maybe better to go in a german forum - thanks in advance you can delete my thread

 

[Gasman]

I would do it this way.

Obtain a recordset for all your required Vertretung
Then obtain a recordset within that loop that obtains all the customers for that particular Vertretung
Then output the report for that customer as you have at present.

So a loop for Vertretung and within that a loop for Customer, and within that the report.

 

[Gasman]

sorry for any confusion - it's maybe better to go in a german forum - thanks in advance you can delete my thread

No need to delete it, as it could help someone else doing exactly the same thing, regardless of language.

 

[jdraw]

We're trying to help you. We just need some facts and some of the data you are working with.
Language may be an issue, but it is not insurmountable. If German is your native language, then write in German and use Google translate and convert to English and post. I think Gasman has given you good advice, it isn't clear that you have understood what has been given.
As per tradition, we do not delete posts that have responses in the thread. Often, posters tell us they have learned lots by following the dialog and examples in threads.

We don't know you, nor your environment, nor your ability with database concepts, nor Access. We are asking you to fill in some of the missing pieces.

Google's German:
Wir versuchen Ihnen zu helfen. Wir brauchen nur einige Fakten und einige der Daten, mit denen Sie arbeiten. Die Sprache mag ein Problem sein, aber es ist nicht unüberwindbar. Wenn Deutsch Ihre Muttersprache ist, dann schreiben Sie auf Deutsch und verwenden Sie Google Translate und konvertieren Sie ins Englische und posten Sie. Ich denke, Gasman hat Ihnen gute Ratschläge gegeben, es ist nicht klar, ob Sie verstanden haben, was gegeben wurde. Wie es Tradition ist, löschen wir keine Beiträge, die Antworten im Thread enthalten. Poster teilen uns oft mit, dass sie viel gelernt haben, indem sie den Dialogen und Beispielen in Threads folgen. Wir kennen weder Sie, noch Ihre Umgebung, noch Ihre Fähigkeiten mit Datenbankkonzepten, noch Access. Wir bitten Sie, einige der fehlenden Teile zu ergänzen.

 

[Pat Hartman]

The weakness with OutputTo is that it does not take a WHERE argument the way that OpenReport does. This causes you to have to use BOTH OpenReport and OutputTo to export a filtered pdf. Then you must close the OpenReport.

This isn't a problem if you are only exporting as single report. However, once you put the export into a loop, you run the risk of slowness and memory issues. To solve the problem, I bind the report to a query that references a hidden field on the form that is running the procedure for the WHERE argument. So, my version of the code would be:

        strDatei = "C:\Fettzuschlag\" & rs.Fields(2).value & "_" & rs.Fields(0).value & "_" & rs.Fields(1).value & ".pdf"
        'The file should be given the name of the data field in the target folder
        Me.gepa =  rs(0)
        'the selection criterion is placed in a hidden field on the form.
        DoCmd.OutputTo acOutputReport, "2022_PS_EN_Ohne_Abfrage", acFormatPDF, strDatei, False
        'The PDF is generated and stored in the folder with the invoice number.
        rs.MoveNext

So, the WHERE clause would be:

WHERE gepa = Forms!yourform!gepa