How to print all records in recordset

[matt753]

I have a subform that displays all of the invoices for the day selected. There is a "Print all" button (code below) that should print all the records in the recordset. I cant figure out why it does not work, but it appears to be only printing one record.

Does anyone have any ideas?

Dim rs As Recordset
 
    Set rs = frmsubAdminEdit.Form.Recordset
 
    rs.MoveFirst
    Do Until rs.EOF
 
        If rs(7) > 0 Then
 
            DoCmd.OpenReport "rptInvoice", acViewNormal, , "OrderID=" & rs(0), , False
            rs.Edit
            rs(6) = -1
            rs.Update
 
        End If
 
        rs.MoveNext
 
    Loop
 
    Set rs = Nothing
 
 

[NeoPa]

I would say only that rs should be declared as DAO.Recordset, as otherwise it is ambiguous. Also, rs(6) & rs(7) are entirely meaningless in your uncommented code.

The only things that appear questionable to my limited understanding is that you set rs(6), but check rs(7). Also you set to -1 but check for > 0.

[matt753]

Thanks

Here is the updated code:

    Dim rs As DAO.Recordset
    Set rs = frmsubAdminEdit.Form.Recordset
 
    If rs.RecordCount > 0 Then
        rs.MoveLast
        rs.MoveFirst
 
        Do Until rs.EOF
            MsgBox (rs(0))   'show id of current record
 
            DoCmd.OpenReport "rptinvoice", acViewNormal, , "OrderID=" & rs("OrderID")
            rs.Edit
            rs.Update
 
            rs.MoveNext
        Loop
 
        rs.Close
        rs = Nothing
 
    Else
        MsgBox ("No records to print")
    End If
 

It still only prints the first record though. If I comment out the DoCmd.OpenReport line it will go through all the items fine, but if its there it will only do the first.

Also do I need the rs.Edit and rs.Update if im not making any changes to the recordset?

[Jim Doherty]

@matt753
Hi,

Where are you closing the report in order for it to re-open again to catch the next value in the loop? You open a report to show either a single record or pass criteria to it show a sequence of records surely. Should you not be opening a report once?

[matt753]

I'm not sure, the report doesnt actually open on the screen, that is just used to print the one invoice. Are you saying I need to close the report so that the loop will go to the next value?

I know this code was working at one point, but im not sure how much it has been changed since then. I'm not the one who originally wrote it, just trying to fix it.

[Jim Doherty]

@matt753
Sorry Matt misunderstood quick glance thought you were previewing as opposed to printing directly I,ll will replicate what you have and test it here

[matt753]

Thanks

Yea i'm not sure why it prints by just opening the report (theres no code for it in the report). Is there something else attached somewhere that tells it to print?

[Jim Doherty]

@matt753
Matt your code is fine ...have no problems here. The acViewNormal constant section of the command section prints the report. (I mistakenly thought earlier you were previewing) hmm strange one. You sure you havent got any filtration criteria sneaking in there thats setting the reports record behaviour somehow? nothing in the reports code?

[matt753]

Is there another way I could code it maybe? Such as having it individually opening and closing each one and printing it or something?

I never actually let it print the one before, I just tried it and it doesnt even do the one. The small print window flashes for a second but it never actually gets sent to the printer. Not a printer problem either, tried this on different computers.

[Jim Doherty]

@matt753
Sounds really strange experience you are having there Matt because everything works out code wise. You could try opening and closing the report each time in the loop (although there is no need to of course). the command for that is this

DoCmd.Close acReport, rptInvoice, acSaveNo

Check the reports Filter property and FilterOn property see what they are saying are they blank?

The usual maintenance options you might look at - repair and compact decompiling and recompiling the database. Am afraid without looking directly at this everything is a shot in the dark!

[matt753]

I tried putting the closing code in there, same thing still happens unfortunately.

Check the reports Filter property and FilterOn property see what they are saying are they blank?

I have the subform (which the recordset is based from) filtered to only show the records for the current date, so the filteron property is true and the filter property is being used. The recordset only contains these items though, and the loop is hitting each item correctly without that code. (I tested having a messagebox appear with each id# and it works fine)



Is there any other methods for printing a report? I tried:

DoCmd.PrintOut acPrintAll

but that just prints a screenshot of the form. It will send one print item for each recordset item, and show the messageboxes mentioned above for testing with each id#, so that part is working correctly in the loop.

I see theres a "DoCmd.RunCommand acCmdPrint" could this be used?

[NeoPa]

You don't need the Edit/Update.

I suspect that something is not right about your OpenReport command. [OrderID] may be alphanumeric for instance, then the filter would be wrong.

I would use the debugging tools to see what's happening more clearly (See Debugging in VBA).

[matt753]

I got it to work, turns out there was an error in the code when the report was opening. There was an if statement seeing if a checkbox was checked but apparenly you cant do that from a form. The error didnt display at all when this code was ran, only when it was opened manually.

[Jim Doherty]

@matt753
Thanks for posting back your result Matt :-)

[matt753]

Thanks for all the help guys