Solved VBA DO while print different reports for a text box value. (1 Viewer)

[jkdave]

When I put this code into my real data base, the correct forms are printing out but they are just blank reports the text boxes does not have any information.

Same results when I place a text box in the simple data base. No text is being pulled from the record.

 

[Gasman]

Record number is numeric?, so no quotes?
Whenever you get issues like this, test in the immediate window.
I'm surprised you did not get data type mismatch?

 

[Gasman]

Sorry, I forgot your record *number* is text, so quotes are needed.

I get this report below from this code after using Adding Existing Fields

    Do While Not rst.EOF = True
        If rst!Green.Value = "Y" Then
            DoCmd.OpenReport "rptgreen", acPreview, , "[Record Number] = '" & rst![Record Number] & "'"
            DoCmd.OutputTo acReport, "rptGreen", acFormatPDF, "rptgreen" & rst![Record Number] & ".pdf"
            DoCmd.Close acReport, "rptgreen"
        End If

        If rst!red.Value = "Y" Then
            DoCmd.OpenReport "rptred", acPreview, , "[Record Number] = '" & rst![Record Number] & "'"
            DoCmd.OutputTo acReport, "rptred", acFormatPDF, "rptred" & rst![Record Number] & ".pdf"
            DoCmd.Close acReport, "rptred"
        End If


        rst.MoveNext
    Loop

You should be Adding Existing Fields if you want to se anything from the records?
Or add the controls manually then set the Control Source.

 

[jkdave]

When I preview my report I get the information in the text box. See below.

Here is what is looks like after I click on the print button. Nothing in the Text box.


Can some one help me with the code, to get it to work?

Here is my current code:

Option Compare Database
Option Explicit

Private Sub cmdprint_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database


Set db = CurrentDb()
Set rst = db.OpenRecordset("qrywpcmain")

Do While Not rst.EOF
If rst!Green.Value = "Y" Then
DoCmd.OpenReport "rptgreen", acViewPreview, , "[Record Number] ="" & [Record Number]&"""
'DoCmd.Close acReport, "rptgreen"
End If

If rst!red.Value = "Y" Then
DoCmd.OpenReport "rptred", acViewNormal, , "[Record Number]= "" & [Record Number]&"""
DoCmd.Close acReport, "rptred"
End If

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

 

[Gasman]

Look at my code very carefully for the criteria.

 

[jkdave]

Sorry, I forgot your record *number* is text, so quotes are needed.

I get this report below from this code after using Adding Existing Fields

    Do While Not rst.EOF = True
        If rst!Green.Value = "Y" Then
            DoCmd.OpenReport "rptgreen", acPreview, , "[Record Number] = '" & rst![Record Number] & "'"
            DoCmd.OutputTo acReport, "rptGreen", acFormatPDF, "rptgreen" & rst![Record Number] & ".pdf"
            DoCmd.Close acReport, "rptgreen"
        End If

        If rst!red.Value = "Y" Then
            DoCmd.OpenReport "rptred", acPreview, , "[Record Number] = '" & rst![Record Number] & "'"
            DoCmd.OutputTo acReport, "rptred", acFormatPDF, "rptred" & rst![Record Number] & ".pdf"
            DoCmd.Close acReport, "rptred"
        End If


        rst.MoveNext
    Loop

You should be Adding Existing Fields if you want to se anything from the records?
Or add the controls manually then set the Control Source.

View attachment 92054

Gasman, Thank you so much, I noticed that you had preview in the code and I was able to change that to ACNormal, and my reports printed.

 

[Gasman]

Yes, I was not going to waste a pile of paper testing so they went to pdf and I wanted to see them

 

[jkdave]

Once again thank you so much Gasman, this is working great. I am going to go ahead and mark this as solved.