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

    Unanswered: record set problem

    what is wrong with code

    Set rsCriteria = db.OpenRecordset("qryEmailMovieReport")

    What should it be

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Generally speaking, there's nothing wrong with that. However, depending on the rest of it, there may be. Given that you didn't post all the relevant code nor the error you get, there's no way to tell.
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Seconded. Nor did you include the references you have set for your project (specifically in this case DAO).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Agreed! Need more info!
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2004
    Posts
    178
    ok what i am trying to do is send the same report with different data to different people. i found code on the internet to do this and i am trying to change it to fit my data base. here is the code i started with

    Option Compare Database
    Option Explicit

    Sub SeparateEmails()
    '*** error trapping - execution goes to bottom on error
    On Error GoTo Err_SeparateEmails

    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String
    Dim rsGLTable As Recordset
    Dim rsCriteria As Recordset

    Set db = CurrentDb
    Set rsCriteria = db.OpenRecordset("Users", dbOpenSnapshot)

    '*** the first record in the Criteria table ***
    rsCriteria.MoveFirst

    '*** loop to move through the records in Criteria table
    Do Until rsCriteria.EOF
    '*** create the Select query based on
    ' the first record in the Criteria table
    strSQL = "SELECT * FROM GLTable WHERE "
    strSQL = strSQL & "[Acct] = '" & rsCriteria![Param] & "'"

    'MsgBox strSQL
    '*** delete the previous query
    db.QueryDefs.Delete "NewQuery"
    Set qdf = db.CreateQueryDef("NewQuery", strSQL)

    DoCmd.SendObject acReport, "rptGLTable", "RichTextFormat(*.rtf)", rsCriteria![User], "", "", "This is a test", "I am testing a new idea for reports", False, ""
    rsCriteria!Emailed = True
    '*** goto the next record in Criteria table
    rsCriteria.MoveNext

    Loop

    rsCriteria.Close

    Exit_SeparateEmails:
    Exit Sub

    Err_SeparateEmails: '*** if there is an error, execution goes here
    '*** if the error is the table or query missing (3265)
    ' then skip the delete line and resume on the next line
    ' Error 2501 notifies you that the SendObject action
    ' has been cancelled. See the OnNoData Event of the report.
    If Err.Number = 3265 Or Err.Number = 2501 Then
    Resume Next
    Else
    '*** write out the error and exit the sub
    MsgBox Err.Description
    Resume Exit_SeparateEmails
    End If

    End Sub

    and i want to use querys and not tables

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For starters, since you're using A2k, make sure in the VBA editor that the DAO reference is checked in Tools/References (MS DAO 3.6...). Then, change these lines as noted:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rsGLTable As DAO.Recordset
    Dim rsCriteria As DAO.Recordset

    Then give it a whirl and see where we are.
    Paul

  7. #7
    Join Date
    Aug 2004
    Posts
    178
    worked fine thanks

  8. #8
    Join Date
    Aug 2004
    Posts
    178
    what would i have to add to get a read receipt fo each email

  9. #9
    Join Date
    Jul 2003
    Location
    Australia
    Posts
    217

    reply

    I believe this statement won't have any effect :
    rsCriteria!Emailed = True

    You are trying to update the "Emailed" field but the recordset is SNAPSHOT type.

Posting Permissions

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