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

[tomwor2]

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?

Private Sub CreatePersonalReport_bttn_Click()
Dim i As Long
 
'for looping listbox entries
For i = 0 To Me.MailSelect_lst.ListCount - 1
 
'select if You want
Me.MailSelect_lst.selected(i) = True
MsgBox Me.MailSelect_lst.Column(0, i), vbOKOnly, "Item number: " & i + 1
' how do i open the filtered report and send it to the mailadress in the list instead of just generating a message?
Next i
 
'end of listbox entries
i = Me.MailSelect_lst.ListCount - 1
MsgBox "Last row: " & i + 1, vbOKOnly, "Your reports has been created"
 
End Sub
 
 

[zmbd]

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.

(^_^)

[tomwor2]

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

[zmbd]

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:

''>>AIR CODE>> Code Omitted<<<
Dim strWhereClause as string
 
''Start you looping code here
''make sure are returning some value before you build the string
'
strWhereClause = "[MailReceiver]= " & Me.MailSelect_lst.Column(0, i)
'
DoCmd.OpenReport _
    ReportName:= "YourReportNameHere, _
    View := acViewPreview, _
    WhereCondition := strWhereClause, _
    WindowMode:= acDialog 
'
''>> set the report to open in dialog so that the code pauses until you close the report.<<
''
''end your looping code here
''
''>>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.

[tomwor2]

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.

Sub loopmsgbox()
 
Dim db As DAO.Database
Dim rstSendReport_maillist_tbl As DAO.Recordset
Dim strmailreceiver As String
Dim strreportset As Variant
 
 
   Set dbs = CurrentDb
   Set rstSendReport_maillist_tbl = dbs.OpenRecordset("SendReport_maillist_tbl")
       rstSendReport_maillist_tbl.MoveFirst
 
   Do While Not rstSendReport_maillist_tbl.EOF
    strreportset = rstSendReport_maillist_tbl!ReportSetCreated
    strmailreceiver = rstSendReport_maillist_tbl!mailreceiver
        'DoCmd.OpenReport "SendReport_rpt", acViewPreview, , "mailreceiver = " & strmailreceiver
        'plan to do a send report here instead of msgbox
        MsgBox strmailreceiver & "-" & strreportset
    DoCmd.Close , "SendReport_rpt"
    rstSendReport_maillist_tbl.MoveNext
Loop
 
 
End Sub
 

[zmbd]

Refering to the codeblock in Post#5

Add line 7 :

Dim zstrWhereClause as String

Insert line 15A:

zstrWhereClause = "mailreceiver = " & strmailreceiver 
 

Insert line 15B:

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:

 DoCmd.OpenReport _
   ReportName := "SendReport_rpt", _
   View := acViewPreview, _
   WhereCondition := zstrWhereClause, _
   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

ʕ•ᴥ•ʔ