Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

"Group" Data for Auto Emailing

Discussion in 'Information Technology' started by tryinghard, Jul 28, 2006.

  1. tryinghard

    tryinghard
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. SusanV

    SusanV
    Expand Collapse
    Guest

    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


    "tryinghard" <fourcallmepop@hotmail.com> wrote in message
    news:1149876697.551659.161040@j55g2000cwa.googlegroups.com...
    > 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.
    >
     
  4. SusanV

    SusanV
    Expand Collapse
    Guest

    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



    "SusanV" <svanallen@nospam-mvps.org> wrote in message
    news:ORpRuL$iGHA.4044@TK2MSFTNGP03.phx.gbl...
    > 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
    >
    >
    > "tryinghard" <fourcallmepop@hotmail.com> wrote in message
    > news:1149876697.551659.161040@j55g2000cwa.googlegroups.com...
    >> 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.
    >>

    >
    >
     

Share This Page