By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

How to print all records in recordset

P: 91
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?

Expand|Select|Wrap|Line Numbers
  1. Dim rs As Recordset
  2.  
  3.     Set rs = frmsubAdminEdit.Form.Recordset
  4.  
  5.     rs.MoveFirst
  6.     Do Until rs.EOF
  7.  
  8.         If rs(7) > 0 Then
  9.  
  10.             DoCmd.OpenReport "rptInvoice", acViewNormal, , "OrderID=" & rs(0), , False
  11.             rs.Edit
  12.             rs(6) = -1
  13.             rs.Update
  14.  
  15.         End If
  16.  
  17.         rs.MoveNext
  18.  
  19.     Loop
  20.  
  21.     Set rs = Nothing
  22.  
  23.  
May 13 '10 #1
Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
May 14 '10 #2

P: 91
Thanks

Here is the updated code:

Expand|Select|Wrap|Line Numbers
  1.     Dim rs As DAO.Recordset
  2.     Set rs = frmsubAdminEdit.Form.Recordset
  3.  
  4.     If rs.RecordCount > 0 Then
  5.         rs.MoveLast
  6.         rs.MoveFirst
  7.  
  8.         Do Until rs.EOF
  9.             MsgBox (rs(0))   'show id of current record
  10.  
  11.             DoCmd.OpenReport "rptinvoice", acViewNormal, , "OrderID=" & rs("OrderID")
  12.             rs.Edit
  13.             rs.Update
  14.  
  15.             rs.MoveNext
  16.         Loop
  17.  
  18.         rs.Close
  19.         rs = Nothing
  20.  
  21.     Else
  22.         MsgBox ("No records to print")
  23.     End If
  24.  
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?
May 14 '10 #3

Jim Doherty
Expert 100+
P: 897
@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?
May 14 '10 #4

P: 91
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.
May 14 '10 #5

Jim Doherty
Expert 100+
P: 897
@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
May 14 '10 #6

P: 91
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?
May 14 '10 #7

Jim Doherty
Expert 100+
P: 897
@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?
May 14 '10 #8

P: 91
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.
May 14 '10 #9

Jim Doherty
Expert 100+
P: 897
@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

Expand|Select|Wrap|Line Numbers
  1. 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!
May 14 '10 #10

P: 91
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:
Expand|Select|Wrap|Line Numbers
  1. 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?
May 14 '10 #11

NeoPa
Expert Mod 15k+
P: 31,186
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).
May 14 '10 #12

P: 91
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.
May 17 '10 #13

Jim Doherty
Expert 100+
P: 897
@matt753
Thanks for posting back your result Matt :-)
May 17 '10 #14

P: 91
Thanks for all the help guys
May 17 '10 #15

Post your reply

Sign in to post your reply or Sign up for a free account.