Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    178

    Unanswered: problem same report different data

    i have a report which i want to send to different people with different data in it. is used to work fine for some reason it dont work now.

    here is the code for seting up the report to different people

    strSQL = "SELECT * FROM qryEndOfWeekFilmReportEmailing WHERE "
    strSQL = strSQL & "[Maker] = '" & rsCriteria![ID Number] & "'"
    MsgBox strSQL

    db.QueryDefs.Delete "NewQuery"
    Set qdf = db.CreateQueryDef("NewQuery", strSQL)


    DoCmd.OutputTo acReport, "repEndOfWeekFilmEmailing", acFormatSNP, sName, False

    the first query works fine but if i try to run the report it tells me run time error 3071 thast its to complated to work out. if i go look at newquery it only has 1 field with critea in it. if i change the query to all the fields then set the critea it works fine. can1 help

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Here's what you do: Mod your query so that it works like you want. Then go into SQL view and copy the SQL statement and paste it into your program code with the other runtime paramenters that you add on the fly ... You're butting up against the interpreted vs compiled queries ... Especially since you're querying from a query all of it's elements. And Access gets confused ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Aug 2004
    Posts
    178
    i found out what was wrong with the code i fixed it but now it wont loop it will do the first then tell me output object cancled dont know why here is the full code i am using

    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim MyDB As Database
    Dim MyRS As dao.Recordset
    Dim TheAddress As String
    Dim qdf As dao.QueryDef
    Dim db As dao.Database
    Dim strSQL As String
    Dim blRet As Boolean
    Dim sPDF As String
    Dim sName As String
    sName = DLookup("[DefaultfileLocation]", "Parms") & "\reports\Movie Report.snp"

    Set MyDB = CurrentDb
    Set db = CurrentDb
    Set MyRS = MyDB.OpenRecordset("qryEmailMovieReport", dbOpenSnapshot)
    MyRS.MoveFirst
    Do Until MyRS.EOF

    strSQL = "SELECT * FROM qryEndOfWeekFilmReportEmailing WHERE "
    strSQL = strSQL & "[Maker] = '" & MyRS![ID Number] & "'"
    'MsgBox strSQL

    db.QueryDefs.Delete "NewQuery"
    Set qdf = db.CreateQueryDef("NewQuery", strSQL)


    DoCmd.OutputTo acReport, "repEndOfWeekFilmEmailing", acFormatSNP, sName, False


    If Len(sName & vbNullString) = 0 Then Exit Sub
    ' let's use the name of the selected Snapshot file
    ' to name our converted PDF document.
    sPDF = Mid(sName, 1, Len(sName) - 3)

    blRet = ConvertReportToPDF(vbNullString, sName, sPDF & "PDF", False)



    TheAddress = MyRS![EmailAddress]
    Set objOutlook = CreateObject("Outlook.Application")

    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
    Set objOutlookRecip = .Recipients.Add(TheAddress)
    objOutlookRecip.Type = olTo
    .Subject = "End of week report for week starting " & Forms![frmreports]![Text4]
    .Body = ""

    Set objOutlookAttach = .Attachments.Add(DLookup("[DefaultfileLocation]", "Parms") & "\reports\Movie Report.PDF")


    .Send


    End With
    MyRS.MoveNext
    Loop

    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing

    can any1 see what is wrong with this code

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I'm not sure, but I think if you delete Movie Report.snp each time through the loop before you execute the DoCmd.OutputTo statement your problem may be solved.

    HTH,
    Sam

  5. #5
    Join Date
    Aug 2004
    Posts
    178
    do you know how to delete a file

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    FileSystemObject.DeleteFile sName
    should work.

    Sam

  7. #7
    Join Date
    Aug 2004
    Posts
    178
    ok the problem was not the code the problem was no data because 1 query had more data then the other. so in reducing the data in the query i have reduced the data by date the same sa the other query. both querys are picking the dates from the same form. if i run each query they will work. but when i run the above code i get an error 3061 in this line

    Set MyRS = MyDB.OpenRecordset("qryEmailMovieReport", dbOpenSnapshot)

    does any1 know what i am doing wrong

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just skimmed this thread..
    Quote Originally Posted by mega1
    do you know how to delete a file
    Code:
    Kill "<FullFilePath>"
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •