prints same report for all records

[rick roberts]

i have a form that contains three types of records i.e. 7, 14 and 21 days overdue accounts. i click a button to batch print all overdue with the appropriate report but instead of printing the right letter to the correct overdue stage it prints all records with the first report

 

[JamesMcS]

Can you give us some more information, like the code behind the button, the fields in the table you're reporting on, etc.?

 

[rick roberts]

this is he code
my query pulls out all the outstanding accounts from my main form
the code also splits accouns to those with and without emails

Function BatchPrint()
Email.SetFocus

'Stop error if email isnt sent
On Error GoTo Error

'Check for over 21 days
If Date > StartDate + 20 Then
'Email
If Email.Text <> "" Then
DoCmd.SendObject acReport, "rptLetter3", acFormatTXT, Email, , , "Overdue Fee Payment", "Please open the attached file", True
CheckLetter3.Value = True

Else
'Post
CheckLetter3.Value = True
DoCmd.OpenReport "rptLetter3", acViewNormal
End If

'Check for over 14 days
ElseIf Date > StartDate + 13 Then
'Email
If Email.Text <> "" Then
DoCmd.SendObject acReport, "rptLetter2", acFormatTXT, Email, , , "Overdue Fee Payment", "Please open the attached file", True
CheckLetter2.Value = True

Else
'Post
CheckLetter2.Value = True
DoCmd.OpenReport "rptLetter2", acViewNormal
End If

'Check for over 7 days
ElseIf Date > StartDate + 6 Then
'Email
If Email.Text <> "" Then
DoCmd.SendObject acReport, "rptLetter1", acFormatTXT, Email, , , "Overdue Fee Payment", "Please open the attached file", True
CheckLetter1.Value = True

Else
'Post
CheckLetter1.Value = True
DoCmd.OpenReport "rptLetter1", acViewNormal
End If
End If

'Catch error
Error:
'Resume Next
End Function

Private Sub Command13_Click()
'get a record count
Set rdset = CurrentDb.OpenRecordset("qryOutstanding")
rdset.MoveLast
rdset.MoveFirst
reccount = rdset.RecordCount
Set rdset = Nothing

   

If reccount = 1 Then
    BatchPrint
    Exit Sub
Else
    a = 1
    Do Until a = reccount
        BatchPrint
        
        a = a + 1
    Loop
    BatchPrint 'to get the last one
End If
End Sub

 

[vbaInet]

Rick, showing us the full code helps a bit but you still haven't clearly explained your objective.

 

[rick roberts]

i have a from that shows records split into 3 catergories namely 7, 14 and 21 days overdue and i have 3 differnt reports to match. these are catergorised seperately with tick boxes when i click the command button a report is printed - 3 different reports to suit the 3 different catergories and also the appropriate tick box is ticked
my problem is that when i click the command all records are printed but with one report - the first that is instigated - it doesnt seem to do the search to determine which report to assign to the record

 

[vbaInet]

Use just one report:

* Group By the category field and set the Force New Page property of this Group Footer's section to Yes
* I imagine the check box on the form is bound so in the Record Source of the report, set the criteria Yes/No field to Yes
* In the click event of the button used to open the report put this:

If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport "ReportName", acPreview

 

[rick roberts]

i cant use one report cos they are all worded differently depending on the number of days oustanding . the code is supposed to check howe many days is outstanding and print a report worded for that timeset unforunately it seems to skip this and just print the first report for all the records
i dont have a butto that opens a report it does it automatically with each record read

 

[vbaInet]

You will need to use separate IF blocks and call the batch function once:

Function BatchPrint()
'Stop error if email isnt sent
'On Error GoTo Error
    
    Email.SetFocus
    
    'Check for over 21 days
    If Date > startDate + 20 Then
            'Email
        If Email.Text <> "" Then
            DoCmd.SendObject acReport, "rptLetter3", acFormatTXT, Email, , , "Overdue Fee Payment", "Please open the attached file", True
            CheckLetter3.Value = True
        Else
            'Post
            CheckLetter3.Value = True
            DoCmd.OpenReport "rptLetter3", acViewNormal
        End If
    End If
    
    'Check for over 14 days
    If Date > startDate + 13 Then
            'Email
        If Email.Text <> "" Then
            DoCmd.SendObject acReport, "rptLetter2", acFormatTXT, Email, , , "Overdue Fee Payment", "Please open the attached file", True
            CheckLetter2.Value = True
        Else
            'Post
            CheckLetter2.Value = True
            DoCmd.OpenReport "rptLetter2", acViewNormal
        End If
    End If
    
    'Check for over 7 days
    If Date > startDate + 6 Then
            'Email
        If Email.Text <> "" Then
            DoCmd.SendObject acReport, "rptLetter1", acFormatTXT, Email, , , "Overdue Fee Payment", "Please open the attached file", True
            CheckLetter1.Value = True
        Else
            'Post
            CheckLetter1.Value = True
            DoCmd.OpenReport "rptLetter1", acViewNormal
        End If
    End If

'Catch error
'Error:
'Resume Next
End Function

Private Sub Command13_Click()
    BatchPrint
End Sub

 

[rick roberts]

you mean not use Else ?

 

[vbaInet]

you mean not use Else ?

Correct, as shown in my last post.

 

[rick roberts]

oops didnt see the difference lol i tried your but it only prints out the first record - i tried putting the loop back in but then it goes back to printing all one report for all records

 

[vbaInet]

To be honest, your explanations still aren't clear and the code didn't make things any clearer. Let's see a stripped down version of your db and the form and repors in concern. Maybe I can work it out from there.

 

[rick roberts]

this is the program i hope you can make sense of it

 

[rick roberts]

can anyone else help me with this

 

[vbaInet]

See attached, I've re-written the code.

I would advise that you get in the habit of explicitly declaring your variables unless you will end up with a bunch of Variants. In each module, at the Declarations section, write Option Explicit just under the line Option Compare Database so that it prompts you when you haven't declared a variable.

Once you've done the above, in the VBA editor go to Debug > Compile Swimtime and fix any errors. You need to do this quite regularly especially everytime you write (or modify) a sub or function.

 

[rick roberts]

i tried the code but it just prints out 3 of the first letter to the fiirst account

 

[vbaInet]

Yes, your report isn't filtered.

 

[rick roberts]

thanks for the info regarding declaration of variables
ive tried the code (took out the email option cos it only confuses the issue) but all it seems to do is print out the same letter for the number of oustanding accounts -- all with the first record details ( i placed a text box in the last 2 letters with the last names to identify the record the report relates to)
thank you for the time youve invested in this problem

 

[rick roberts]

thanks for the advice on declaring variables
i tried the code but cant get it to do what i want - it still just prints the same report for the first record and not the chosen ones no matter what stage the date is at (i took out the email option to simplify the problem and placed a textbox with last name for each report so i could see which record it referred to)
thanks for taking the time to help me with this matter

 

[vbaInet]

thanks for the advice on declaring variables
i tried the code but cant get it to do what i want - it still just prints the same report for the first record and not the chosen ones no matter what stage the date is at (i took out the email option to simplify the problem and placed a textbox with last name for each report so i could see which record it referred to)
thanks for taking the time to help me with this matter

Clever little thing you did there - deleting your last post and re-posting it to bump it up Please don't delete posts. You could have simply posted a quick follow-up message.

Your form logic is wrong. The type of letter to send or print is determined by the number of elapsed days but also you want it to be determined by the checkboxes. It's supposed to be one or the other not both. Re-think your logic and I will tell you how to drive this forward.