"Group" Data for Auto Emailing

[tryinghard]

Hi. I'm setting up an program to automatically generate and email
reports on a per-project basis to the respective project managers. I
know how to make the emails happen, but I don't know how to
programmatically separate my data on a per-project basis (currently the
user has to select the projects one-by-one, click a command button to
launch the report generation/email sequence. The sequence first
generates a temporary, new table based on the project selected).

My source table has a field called "projectnum", and has a varying
number of records for each project. The number of projects also varies
somewhat over time.

Thanks for any help. I have extensive Access experience, but little VB
experience.

 

[SusanV]

Hi trying,

You can simply use a table or query containing all the data, and use that as
your report's recordsource. Then loop through a recordset or the different
project types and set a filter on the report to only include each project on
open, and email it then go to the next, email and close etc, something like
the following:

dim rs as new adodb.recordset
dim cnx as new adodb.connection
dim strSQL as string

set cnx = currentproject.connection
strSQL = "SELECT DISTINCT projectnum from YourTable

rs.Open sq, cnx, adOpenKeyset, adLockOptimistic

rs.MoveFirst
Do While Not rs.EOF
DoCmd.OpenReport "YourReport", acViewPreview, , wh
With Reports![rptTMSearchResults]
.FilterOn = True
.Filter = rs.fields("projectnum ").Value
End With
DoCmd.SendObject ' fill remainder of send info
docmd.close acReport, "YourReport"
rs.movenext
End Loop

rs.close
cnx.close

 

[SusanV]

Oops - Minor changes to the code:

dim rs as new adodb.recordset
dim cnx as new adodb.connection
dim strSQL as string

set cnx = currentproject.connection
strSQL = "SELECT DISTINCT projectnum from YourTable

rs.Open sq, cnx, adOpenKeyset, adLockOptimistic

rs.MoveFirst
Do While Not rs.EOF
DoCmd.OpenReport "YourReport", acViewPreview
With Reports![rptTMSearchResults]
.FilterOn = True
.Filter = rs.fields("projectnum ").Value
End With
DoCmd.SendObject ' fill remainder of send info
docmd.close acReport, "YourReport"
rs.movenext
End Loop

rs.close
cnx.close

Hi trying,

You can simply use a table or query containing all the data, and use that
as your report's recordsource. Then loop through a recordset or the
different project types and set a filter on the report to only include
each project on open, and email it then go to the next, email and close
etc, something like the following:

dim rs as new adodb.recordset
dim cnx as new adodb.connection
dim strSQL as string

set cnx = currentproject.connection
strSQL = "SELECT DISTINCT projectnum from YourTable

rs.Open sq, cnx, adOpenKeyset, adLockOptimistic

rs.MoveFirst
Do While Not rs.EOF
DoCmd.OpenReport "YourReport", acViewPreview, , wh
With Reports![rptTMSearchResults]
.FilterOn = True
.Filter = rs.fields("projectnum ").Value
End With
DoCmd.SendObject ' fill remainder of send info
docmd.close acReport, "YourReport"
rs.movenext
End Loop

rs.close
cnx.close




--
hth,
SusanV

Hi. I'm setting up an program to automatically generate and email
reports on a per-project basis to the respective project managers. I
know how to make the emails happen, but I don't know how to
programmatically separate my data on a per-project basis (currently the
user has to select the projects one-by-one, click a command button to
launch the report generation/email sequence. The sequence first
generates a temporary, new table based on the project selected).

My source table has a field called "projectnum", and has a varying
number of records for each project. The number of projects also varies
somewhat over time.

Thanks for any help. I have extensive Access experience, but little VB
experience.