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

how to loop the records in a listbox and for each record open a filtered report

P: 3
I have a single select listbox in a form where when a record is double clicked a report is opened with a filter.

The open report action is performed with a macro applying a filter: [SendReport_qry]![MailReceiver]=[Forms]![SendReport_selectset_frm]![MailSelect_lst]

IŽd like to loop all records in the list and for each record open the report and send it to the mail address in the listbox. I have found a code to loop the records but it does not seam to actually select each record, as when i run the macro it opens the first filtered results over and over.

Can anyone help me to figure out how to do this?

Expand|Select|Wrap|Line Numbers
  1. Private Sub CreatePersonalReport_bttn_Click()
  2. Dim i As Long
  3.  
  4. 'for looping listbox entries
  5. For i = 0 To Me.MailSelect_lst.ListCount - 1
  6.  
  7. 'select if You want
  8. Me.MailSelect_lst.selected(i) = True
  9. MsgBox Me.MailSelect_lst.Column(0, i), vbOKOnly, "Item number: " & i + 1
  10. ' how do i open the filtered report and send it to the mailadress in the list instead of just generating a message?
  11. Next i
  12.  
  13. 'end of listbox entries
  14. i = Me.MailSelect_lst.ListCount - 1
  15. MsgBox "Last row: " & i + 1, vbOKOnly, "Your reports has been created"
  16.  
  17. End Sub
  18.  
  19.  
Feb 2 '14 #1
Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,397
1) Just to make sure we're on the same page:
Macro is not the same as VBA script.
They are two seperate; however, parallel, scripting languages within Access. Unlike Excel, which uses the VBA scripting language, yet calls it a macro (IMHO: an unfourtunate choice of word).

2) Your code as written has no command to open the record. In fact, there is no recordset active in the code.

3) Open your form in design view, show the properties for the control that had the embedded macro. You'll know it's a macro because the property field will have "[Embedded Macro]"
If you open this macro up, more than likely you will see that there is an action to open the report.

3) Provided your filter is already set, then all you need to do to get the report to open is use the docmd.openreport() method

4) If you will goto the above link, you'll find that you can actually set the filter within the command. I HIGHLY advise that you build the filter string first, assigning it to a variable and then use that variable within the command. This will help with trouble shooting.

5) Now as far as sending the report as an email, you have several options. Given that you are wanting to open each one up first, then I would suggest that you simply use the external data tab on the ribbon and click on the send email. You can automate this entire process too by using the sendobject method or more advanced code. Both methods are easily located here on bytes with a simple search.

Well, the kids are up and I have a few projects to accomplish here this morning.

(^_^)
Feb 2 '14 #2

P: 3
Hi zmbd,

Thank's for your quick reply, maybe I should say I am not too familiar with VBA but i normaly know enough to modify some scripts I find on the internet.

Anyhow I have tried several way's to do this:

As described but by saving the embeded macro, then triggering it from the script as the script loops using docmd.

triggering it as you suggest by docmd.openreport (in the script), and between records also docmd.close..., but as the script I posted obviously did not select the records properly it did not work.

I actually started out trying to create a recordset from the same query that populates the list box which is a query where I select the mail address distinct from the query that populates the report. This query contains the e-mail (for each record) together with some more data that populates the report.

Either way would be fine by me but I found looping the list would be easier for me a s I am not at all familiar with creating recordsets. As i did not even manage to view the report properly I kind of gave up to send it, but I know there are several ways.

Any more suggestions on how to solve it the bet way?

/Tomas
Feb 2 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
I've PM's you my list of standard tutorials.

IN the meantime, you need to post your new code.

Please understand, Bytes.com is usually more of a teach-to-fish site and very little of a give a fish.

So in this case,
Take a look at the macro, it will provide a hint on how to use the VBA to open the record.
Take a look at the link I gave you, you can then use the concepts of how to step thru the list box to build your string filter, place this in the where conditional, etc...

so you need something like:

Expand|Select|Wrap|Line Numbers
  1. ''>>AIR CODE>> Code Omitted<<<
  2. Dim strWhereClause as string
  3.  
  4. ''Start you looping code here
  5. ''make sure are returning some value before you build the string
  6. '
  7. strWhereClause = "[MailReceiver]= " & Me.MailSelect_lst.Column(0, i)
  8. '
  9. DoCmd.OpenReport _
  10.     ReportName:= "YourReportNameHere, _
  11.     View := acViewPreview, _
  12.     WhereCondition := strWhereClause, _
  13.     WindowMode:= acDialog 
  14. '
  15. ''>> set the report to open in dialog so that the code pauses until you close the report.<<
  16. ''
  17. ''end your looping code here
  18. ''
  19. ''>>AIR CODE>> Code Missing
Personally I would use the simple sendobject:
DoCmd.SendObject Method (Access) Office 2010 and send the report via email directly. Setting the parameter "EditMessage=True" so that you can review the email. This however, really only works if you have an email client installed.
Feb 2 '14 #4

P: 3
So time for another trial, I did actually not manage to get the script to select the valies in the listbox correctly, but decided to give the recordset loop another go.

I have managed to make script that loops a recordset of mailrecepiants and show a messagebox with the selected data for each record. but I can't get the where clause correct for the open report function.

The "mailreceiver" stated in the script is on each row of the query that the report is based upon the reported is then grouped on this value. The SendReport_maillist_tbl is a table created with selected values that also form the basis for the report.

Anybody have any ideas of what is wrong with the where clause? I am in access 2007 by the way.

Expand|Select|Wrap|Line Numbers
  1. Sub loopmsgbox()
  2.  
  3. Dim db As DAO.Database
  4. Dim rstSendReport_maillist_tbl As DAO.Recordset
  5. Dim strmailreceiver As String
  6. Dim strreportset As Variant
  7.  
  8.  
  9.    Set dbs = CurrentDb
  10.    Set rstSendReport_maillist_tbl = dbs.OpenRecordset("SendReport_maillist_tbl")
  11.        rstSendReport_maillist_tbl.MoveFirst
  12.  
  13.    Do While Not rstSendReport_maillist_tbl.EOF
  14.     strreportset = rstSendReport_maillist_tbl!ReportSetCreated
  15.     strmailreceiver = rstSendReport_maillist_tbl!mailreceiver
  16.         'DoCmd.OpenReport "SendReport_rpt", acViewPreview, , "mailreceiver = " & strmailreceiver
  17.         'plan to do a send report here instead of msgbox
  18.         MsgBox strmailreceiver & "-" & strreportset
  19.     DoCmd.Close , "SendReport_rpt"
  20.     rstSendReport_maillist_tbl.MoveNext
  21. Loop
  22.  
  23.  
  24. End Sub
  25.  
Feb 11 '14 #5

zmbd
Expert Mod 5K+
P: 5,397
Refering to the codeblock in Post#5

Add line 7 :
Expand|Select|Wrap|Line Numbers
  1. Dim zstrWhereClause as String
Insert line 15A:
Expand|Select|Wrap|Line Numbers
  1. zstrWhereClause = "mailreceiver = " & strmailreceiver 
  2.  
Insert line 15B:
Expand|Select|Wrap|Line Numbers
  1. debug.print zstrWhereClause
Run your code once,
press <ctrl><g>
you should see an entry for every report that was opened when the VBA editor opens in the immediates window.

Change line 16:
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.OpenReport _
  2.    ReportName := "SendReport_rpt", _
  3.    View := acViewPreview, _
  4.    WhereCondition := zstrWhereClause, _
  5.    WindowMode := acWindowNormal
DoCmd.OpenReport Method Office 2007
CAUTION:

Line 18 will show every record in the opened recordset because the whereclause is only valid for line 16. The only thing this line is currently good for is pausing the code you can do this line 16 by changing to "WindowMode:= acDialog"

Line 19 is closing your reports before you can do anything with them

ʕ•ᴥ•ʔ
Feb 11 '14 #6

Post your reply

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