DoCmd.OutputTo in a Loop

[new@this]

Hi,

I am trying to automate a form to create a pdf file of the form, then move on to the next naqmeno in the db and do the same for it, until the end of the file.

My DoCmd.OutputTo works when I dont have it in the loop. Since I have put it in the loop it gives me Run Time Error 2487 "The object type argument for the action or method is blank or invalid".

Here is my code, can anyone please assist me with this.

Option Compare Database
Private Sub Form_Load()
Dim strFormName As String
Dim strName As String
Dim strPath As String
Dim MyDB As Database
Dim rs As DAO.Recordset
Set MyDB = CurrentDb
Set rs = CurrentDb.OpenRecordset("DebtMasters")
srtFormName = "InvoicesDue"
strName = DLookup("NameNo", "Debtmasters")
strPath = "C:\Test\"
Do While Not rs.EOF
If rs("NameNo") <> "" Then


DoCmd.OutputTo acForm, strFormName, "PDFFormat(*.PDF)", strPath & strName & ".PDF", False, "", 0

End If
rs.MoveNext
Loop
rs.Close

End Sub

Thanks in advance.

 

[MarkK]

Hello and welcome to the forum

You've misspelled the variable name strFormName. You should "require variable declaration" in VBA and the compiler would catch this error.
Also, there's not really any need to declare a variable and then assign it a constant value, and then use the variable in an expression. Also, there is an unused database variable in your code, MyDB.
So if we deal with all that and use code tags, and regular indents, we get . . .

Private Sub Form_Load()
    Dim strName As String
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("DebtMasters")
    strName = DLookup("NameNo", "Debtmasters")
    Do While Not rs.EOF
        If rs("NameNo") <> "" Then
            DoCmd.OutputTo acForm, "InvoicesDue", "PDFFormat(*.PDF)", "C:\Test\" & strName & ".PDF", False, "", 0
        End If
        rs.MoveNext
    Loop
    rs.Close
End Sub

Then, rather than check every record in the recordset, we can open exactly the recordset you need, and maybe use a With block, and we get. . .

Private Sub Form_Load()
    Dim strName As String
    
    strName = DLookup("NameNo", "Debtmasters")
    With CurrentDb.OpenRecordset("SELECT * FROM DebtMasters WHERE NameNo <> ''")
        Do While Not .EOF
            DoCmd.OutputTo acForm, "InvoicesDue", "PDFFormat(*.PDF)", "C:\Test\" & strName & ".PDF", False, "", 0
            .MoveNext
        Loop
        .Close
    End With
End Sub

So the code becomes much clearer now and you can see that the filename will never change because the DLookup() is outside the loop. This code will save exactly the same file in exactly the same location for each loop.
Hope this helps,

 

[MarkK]

Here. See how you open that recordset? Well you can use a field in there, like . . .

Private Sub Form_Load()
    With CurrentDb.OpenRecordset("SELECT * FROM DebtMasters WHERE NameNo <> ''")
        Do While Not .EOF
            DoCmd.OutputTo acForm, "InvoicesDue", "PDFFormat(*.PDF)", "C:\Test\" & [B][COLOR="Red"]!NameNo[/COLOR][/B] & ".PDF", False, "", 0
            .MoveNext
        Loop
        .Close
    End With
End Sub

. . . see how that draws a different value for each iteration of the loop (depending on what is in the table)?

 

[new@this]

Both these codes do exactly what mine original code did...it keeps creating a PDF for the current form using the name number in the Textbox..this is correct, but i need the textbox to display each name number in the db and create a pdf for each name number.
So how do I get the textbox to display a name number, then create the pdf and then move to the next name number in the db, and do that until all the name numbers has displayd in the textbox and created a pdf?

 

[pr2-eugin]

I am not sure if I am following you.. This LOC,

DoCmd.OutputTo acForm, "InvoicesDue", "PDFFormat(*.PDF)", "C:\Test\" & !NameNo & ".PDF", False, "", 0

Will create a PDF file, with a different nameNo everytime the loop is executed. First it would be sample1.pdf, then sample2.pdf. Until the loop hits the EOF.

 

[new@this]

yes, but it will do this for every name number that is displayd in the textbox. So basically i want a loop that wil get the first name number from the db, display it in the textbox and create a pdf, then get the second name number from the db, display it in the textbox and created a pdf...this should continue until there is a pdf file for every name number in the db.

 

[pr2-eugin]

So if your table has 1000 records, you want 1000 PDF's with each record on its own file. I think the above code will produce 1000 copies of the same file with different name. Am I following you on this?

 

[new@this]

yes you are..thats exactly what i want..the reason I have the Name No in the docmd.outputto command is so that its uses the number in the textbox as the name for the pdf file. keeps creating the same pdf over and over again. It might work if I can get the text box to move to the next record after a pdf has been created

 

[stopher]

Surely Lagbolt's solution (post 3) gives you what you want. It grabs the records from DebtMasters and loops through each one. It takes the NameNo specific to each record (not from the form). So assuming NameNo is different for each record, you will get a whole lot of differently named PDFs.

But you should not try to use the form to iterate through your records automatically. To do so would be just clunky.

 

[pr2-eugin]

Before the DoCmd.OutputTo, add a DoCmd.GoToRecord line. It should move the record one at a time. Not tested. Just a method that popped on my head.

 

[new@this]

It creates the same name, so it just keeps overwriting the same file.

 

[pr2-eugin]

It creates the same name, so it just keeps overwriting the same file.

Who is that reply aimed at?

 

[new@this]

Both of you I guess...That code creates the same file the whole time.

 

[pr2-eugin]

Try this code, again not tested !

Private Sub Form_Load()
    Dim iCtr As Long
    With Me.Recordsetclone
        For iCtr = 1 To .RecordCount
            DoCmd.OutputTo acForm, "InvoicesDue", "PDFFormat(*.PDF)", "C:\Test\" & [COLOR=Red][B]Me.[/B][/COLOR][COLOR=Red][B]<<NameNo>>[/B][/COLOR] & ".PDF", False, "", 0
            DoCmd.GoToRecord
        Next
        .Close
    End With
End Sub

Please replace the <<NameNo>> with the actual name of the control on your form !

 

[new@this]

It says invalid use Me key word.. Ho do I set the textbox value = to the field in the db? I used a DLOOKUP

 

[stopher]

Try this code, again not tested !

Private Sub Form_Load()
    Dim iCtr As Long
    With Me.Recordsetclone
        For iCtr = 1 To .RecordCount
            DoCmd.OutputTo acForm, "InvoicesDue", "PDFFormat(*.PDF)", "C:\Test\" & ![COLOR=Red][B]<<NameNo>>[/B][/COLOR] & ".PDF", False, "", 0
            DoCmd.GoToRecord
        Loop
        .Close
    End With
End Sub

Please replace the <<NameNo>> with the actual name of the control on your form !

That's not how I see Lagbolt's solution. The <<NameNo>> should be replaced with the name of the field in the DebtMasters table. The exclamation mark signifies that we are referring to a field in the recordset.

 

[pr2-eugin]

That's not how I see Lagbolt's solution. The <<NameNo>> should be replaced with the name of the field in the DebtMasters table. The exclamation mark signifies that we are referring to a field in the recordset.

Yes, it is the name of the Field in the table, if you are using the Recordset, I am using the Recordset clone, just to loop through. I should have edited the code, it should read Me.<<nameNo>>, replacing nameNo as the name of the control !

 

[new@this]

Same error, invalid use of the Me keyword.
Then I used Forms!InvoicesDue!txtNameNo.RecordsetClone, and it gives me and error at the loop saying loop without do

 

[pr2-eugin]

Use Forms!InvoicesDue.RecordsetClone, I have edited the code, please check again.

 

[new@this]

Still says compile error, loop without a do.