To loop output for all the records in a form

[mercapto]

Hello.

I have a form with a pdf output button. The button has this code:

Private Sub Comando18_Click()
Me.Refresh

Dim myPath As String
Dim stDocName As String
Dim theFileName As String

stDocName = "Hoja de Pedido"
DoCmd.OpenReport stDocName, acPreview, , "NumPedido = " & Nz(Me.NumPedido, 0)

myPath = "C:\"
theFileName = "Pedido " & NumPedido & ".pdf"

DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, theFileName, False

End Sub

So everytime I press the button I get a pdf file. I want to make a macro that allows me to loop this task with all the records loaded in the form, generating a single pdf for each.

How can I do it??



thanks a lot.

Offtopic: as I said in a previous thread, my code always outputs in My Documents (not in mypath), if anyone knows how to fix this...

[pbaldy]

The path is easy; you didn't use the myPath variable, so you only have a file name in the OutputTo. You can either loop the recordset clone of the form, or a simple recordset if you know how the form is filtered.

[mercapto]

The path is easy; you didn't use the myPath variable, so you only have a file name in the OutputTo.

LOL...
Now I remember that I didn't know were to put this variable, because either in theFileName line or in the cmd line it gives me error 2501.

You can either loop the recordset clone of the form, or a simple recordset if you know how the form is filtered.

Can you translate this to code? My VB knowledge goes from copy-paste to if/then

[pbaldy]

Here's my template code for a loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."  'replace with appropriate SQL statement
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'code to work with current record here
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing