Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Posts
    45

    Unanswered: report recordsets

    hi all

    im trying to access a reports recorset....

    DoCmd.OpenReport "report", acViewPreview

    Dim reportrst As DAO.Recordset
    Set reportrst = Reports!report.Recordset

    this works when the recorset is from a form ie

    Set rst = forms!formname.Recordset

    but errors on reports..... is this something that cannot be done?

    thanks

    cq
    Sent By Royal Mail

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    An easier approach would be to use a query to generate the report, and then use the same query within the form.
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2005
    Location
    Blacksburg, Virginia
    Posts
    43
    If you are trying to do some processing on the records for the report, you can open a copy of the report's recordset using the report's rowsource:

    Code:
    Dim strSQL As String
    Dim rsData As New ADODB.Recordset
    
    strSQL = Reports("Name of Report").RowSource
    rsData.Open strSQL, CurrentProject.Connection, adOpenKeySet
    
    'now you can access the records
    Do While rsData.EOF = False
    
      Debug.Print rsData.Fields(1).Name
      rsData.MoveNext
    
    Loop
    If the report's rowsource includes a WHERE condition and a filter exists, you'll have to do some fancy string work, but this should suffice otherwise.

    Hope that helps!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by colonelquinn
    this works when the recorset is from a form ie

    Set rst = forms!formname.Recordset

    but errors on reports..... is this something that cannot be done?
    To answer your question directly - no. You are correct that is works fine with forms and buggers up with reports. As mentioned, there are alternatives but the reports Reordset property is not the most useful property incorporated into Access.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2002
    Posts
    45
    cheers for all your replys and your all right the .recordset property does not exist for forms sooooooo i did a work around using the recordsource or query that the report uses..

    Dim qd As QueryDef
    Set qd = db.QueryDefs("queryname")
    qd![parameter] = [Forms]![formname]![cmbbox]


    Dim reportrst As DAO.Recordset
    Set reportrst = qd.OpenRecordset(dbOpenSnapshot)

    and then loop through the recordset to do some ammendments
    Sent By Royal Mail

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Perhaps I am just a pedant -

    Only minor change is I would pull the query name from the report recordsource (a la Lint) so if it ever changes (for whatever reason) it won't throw your report.

    Also - I presume you don't pass any filters to your report as part of the args? If you do you will need to pick these up and add a where condition to your OpenRecordset SQL.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2002
    Posts
    45
    yup i think your right.. ill make the amendments.. thanks both.

    the querydef picks up the where clause and you add the paramter values like ive done above.. saves messing around with strings.
    Sent By Royal Mail

Posting Permissions

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