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

[jkdave]

I have been searching for a VBA that will go through a recordset and print different reports based a text value per record.
here is my current code, and it is giving me an error Loop with out Do. This code is attached to command button. I am using access 2016. Any help to get this to work is greatly appreciated.

Private Sub Printforms_Click()

Dim rst As Recordset
Dim db As Database


Set db = CurrentDb()
Set rst = db.OpenRecordset("tblforForms")
 
If Not (rst.EOF And rst.BOF) Then

rst.MoveLast
rst.MoveFirst
End If

Do While Not rst.EOF = True
    If phone.Value = "Y" And powercord.Value = "Y" Then
    DoCmd.OpenReport "rptgtg", acViewNormal
    DoCmd.Close acReport, "rptgreen"
Else
    If phone.Value = "N" And powercord.Value = "N" Then
    DoCmd.OpenReport "rptphoneerror", acViewNormal
    DoCmd.Close acReport, "rptred"
End If

rst.MoveNext
Loop



rst.Close
Set rst = Nothing
End Sub

 

[theDBguy]

Hi. Welcome to AWF!

Sometimes, error messages could be misleading. Looking at your code, I do see a Do line before the Loop, so the error must be coming from somewhere else, despite what the error message say. In this case, I do see two If/Then lines but only one End If. So, you seem to be missing one End If.

 

[arnelgp]

...
...
 While Not rst.EOF = True
If [B]rst![/B]phone.Value = "Y" And [B]rst![/B]powercord.Value = "Y" Then
DoCmd.OpenReport "rptgtg", acViewNormal
DoCmd.Close acReport, "rptgreen"
Else
If [B]rst![/B]phone.Value = "N" And [B]rst![/B]powercord.Value = "N" Then
DoCmd.OpenReport "rptphoneerror", acViewNormal
DoCmd.Close acReport, "rptred"
End If

rst.MoveNext
Loop
...
...

 

[Gasman]

I cannot see phone or powercord being valid variables? Shouldn't they be prefixed with the rst prefix
If you indented your code and used the code tags, it would be much more readable to everyone

 

[jkdave]

adding rst! in front of the field.value did not work. I still get a Loop without a Do.
What are code tags?

 

[jkdave]

adding rst! in front of the field .value get Compile error: Expected Then or GoTo

 

[Minty]

You are missing an End If.
Indenting your If Then clauses highlights it

 Dim rst As Recordset
    Dim db As Database


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

    If Not (rst.EOF And rst.BOF) Then

        rst.MoveLast
        rst.MoveFirst
    End If

    Do While Not rst.EOF = True
        If phone.Value = "Y" And powercord.Value = "Y" Then
            DoCmd.OpenReport "rptgtg", acViewNormal
            DoCmd.Close acReport, "rptgreen"
        Else
            If phone.Value = "N" And powercord.Value = "N" Then
                DoCmd.OpenReport "rptphoneerror", acViewNormal
                DoCmd.Close acReport, "rptred"
            End If
        End If      '  You Were missing this
   
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing

Not sure your if-then-else is set up correctly for the logic you wanted though.

 

[jkdave]

You are missing an End If.
Indenting your If Then clauses highlights it

 Dim rst As Recordset
    Dim db As Database


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

    If Not (rst.EOF And rst.BOF) Then

        rst.MoveLast
        rst.MoveFirst
    End If

    Do While Not rst.EOF = True
        If phone.Value = "Y" And powercord.Value = "Y" Then
            DoCmd.OpenReport "rptgtg", acViewNormal
            DoCmd.Close acReport, "rptgreen"
        Else
            If phone.Value = "N" And powercord.Value = "N" Then
                DoCmd.OpenReport "rptphoneerror", acViewNormal
                DoCmd.Close acReport, "rptred"
            End If
        End If      '  You Were missing this
  
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing

Not sure your if-then-else is set up correctly for the logic you wanted though.

Thank you for the missing End If, I think you are correct about my if-then-else logic. I added the End if where you put it. I got 20 records in my recordset . In my recordset I have three records that match the first criteria, the first record and the last two. When I click on print I got 60 reports 3 reports for each record in the record set but they were the three that matched the first criteria for each record.

 

[Gasman]

Walk through the code line by line with F8 and see what paths it takes.?
What is the reason for the movelast/first code?

 

[jkdave]

Walk through the code line by line with F8 and see what paths it takes.?
What is the reason for the movelast/first code?

AS I was googling this I saw that on a code so i thought I would try it. I have put rst! in front of my text box. Now it is printing both reports right ones, but instead of getting 20 reports that I was expecting I am get three reports for the first if statement, then I an getting 17 reports for each record for the next 17 records then I am getting 3 reports for each one on the end that has the same criteria. So it is printing both reports, but I am getting the number of reports for each record that matches the criteria. Do I need to loop through the record set?

 

[Gasman]

TBH I'd use that criteria for the recordset, else you could read many records for only a few that match the criteria?
I think you are going to have to upload a sample db,for anyone to see the problem?

 

[jkdave]

I have created a simple data base and got the same out come.
I have attached the simple data base. It has 5 records 3 fields. Record number, Green and Red.
What I am looking for is when I click on print button on the form if it has Y in Green or Y in Red I want it to print 1 Green or 1 Red report for each record then move to the next record and print out 1 report and go through the recordset printing one report per record. I should end up with 5 reports. What I am getting at the moment is 5 Green reports, then I am getting 10 Red reports, and then 10 Green reports. So I am getting 25 reports in total and I only want one per record in this case 5.

I hope i have explained that correctly.

Thanks for any help you guys can provide me.

 

[Gasman]

Notice Option Explicit at te top of the code?
You need this in all your modules. Set it once in Debug Window/Tools/Options/Require variable declaration.

Here is your code
Start indenting it to make life easier for everyone

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", acViewNormal
            DoCmd.Close acReport, "rptgreen"
        End If

        If rst!red.Value = "Y" Then
            DoCmd.OpenReport "rptred", acViewNormal
            DoCmd.Close acReport, "rptred"
        End If

        rst.MoveNext
    Loop

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

You were testing for "N" for red report?
So if you only want 5 reports for 5 records, your logic is all wrong?

Eg: record 1,4 & 5 have both set, so which do you want when both are set?

 

[jkdave]

Notice Option Explicit at te top of the code?
You need this in all your modules. Set it once in Debug Window/Tools/Options/Require variable declaration.

Here is your code
Start indenting it to make life easier for everyone

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", acViewNormal
            DoCmd.Close acReport, "rptgreen"
        End If

        If rst!red.Value = "Y" Then
            DoCmd.OpenReport "rptred", acViewNormal
            DoCmd.Close acReport, "rptred"
        End If

        rst.MoveNext
    Loop

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

You were testing for "N" for red report?
So if you only want 5 reports for 5 records, your logic is all wrong?

Eg: record 1,4 & 5 have both set, so which do you want when both are I have co

 

[jkdave]

I have copied your code, I made sure my data was correct, I now have Green as Y and Red as N for 1, 4, and 5, and I have Green as N and Red as Y for 2 and 3. When I click on print I am still getting 25 reports, and not just 5. Not sure what I am doing wrong.

 

[Gasman]

I just amended the data to what you have stated?

Result

 

[jkdave]

I just amended the data to what you have stated?
View attachment 92041


Result
View attachment 92040

See how you are getting 5 pages for each record, I am trying to get 1 page for for each record. What I need is to click on print and get just 5 sheets of paper. Green, Green, Red, Red, and Green. Does that make sense?

 

[Gasman]

I never looked at the reports
I expect in that case that you need to open the report for just that record? See the OpenReport syntax and the Where clause

 

[jkdave]

I have it working on my simple simple Data base right now.

my record number Data Type is: Short Text
Here is my 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", acViewNormal, , "[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

 

[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.