none
Access 2007 - refresh report from table

    Question

  • having a problem refreshing a report in access 2007. The report is attached to a report-table. In the report load, The table is preloaded with all information and then based upon some selection criteria - deletes off any unnecessary rows. after the table is fully loaded and it is ready for the report, I do a docmd.requery, that did not work, so I added a docmd.repaintobject. That didn't work eather.

    When I get out of the report preview and look at the report-table and it is correct, but the report does not reflect these table values (there are 3 selected rows on the table, yet the report showed 300 rows (none were duplicates). it looks like the table in the initial load before the removal of unnecessary rows.

    Is there a command that I should use to reload the report from the table?  thanks

    Friday, August 13, 2010 8:42 PM

Answers

  • Pete, did you check to make sure you had correct indexing in place for that subselect? if not, it could make a _world_ of difference in ther performance of that subquery.
    • Marked as answer by pete schenk Monday, August 23, 2010 6:23 PM
    Wednesday, August 18, 2010 2:00 PM

All replies

  • Use a where condition to open the report to the records you require, like "[ID] = " & lngID (assuming ID is a number data type).

    DoCmd.OpenReport "ReportName",  ,  ,"[ID] = " & lngID.

    Usually you would get the ID value from a query based on that table.

     

     


    Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia
    Friday, August 13, 2010 9:30 PM
  • Hello,

    Instead of DoCmd use Me.Requery

    Nadia

    Friday, August 13, 2010 9:31 PM
  • It seems you're preparing the report's data-source table during the Load event.
    Are you sure you are using a report, and not a form? 

    In my version of Access, Forms have an Open and a Load event.  Reports have an Open event, but not a Load event.

    If you have the choice, use the Open event procedure to prepare the data and/or change the RecordSource property of the Form or Report.  Don't use the Load event procedure.  For example:

    Private Sub Report_Open(Cancel As Integer)

         Me.RecordSource = "tblReportData"
         Me.RecordSource = "qryMyQuery"
         Me.RecordSource = "SELECT * FROM MyTable WHERE ........."

    End Sub

    Private Sub Form_Open(Cancel As Integer)

         Me.RecordSource = "tblReportData"
         Me.RecordSource = "qryMyQuery"
         Me.RecordSource = "SELECT * FROM MyTable WHERE ........."

    End Sub

    Sunday, August 15, 2010 4:51 PM
  • In addition to the other comments... ordinarily it is not necessary to create a new table (or fill a table) in order to generate a report. It's extra work, it bloats your database, it wastes time creating the new table and all its system table references...

    Have you established (rather than just assuming) that you cannot get adequate performance using a select query based on your original data tables as the recordsource of the Report?

    A Requery will of course not change the contents of a table - only rerun the Recordsource query upon which the report is based. I suspect you're trying to do too much of the work that Access will do for you free of charge!

     


    John W. Vinson/MVP
    Monday, August 16, 2010 12:04 AM
  • Thank you for your input. They were all great suggestions. I did find that I was doing the requery command incorrectly, when I changed it to DoCmd.Requery and DoCmd.RepaintObject it worked.

    The filter and where options for the report would have worked if there were just a few options the client could pick from. Unfortunaly when selecting more than just a few options, the filter and where options were running into size problems.

    The report is a merge of 8 tables, 3 of which are a 1 to 1 correlation. the other 5 are 1 to many. this caused multiple issues in a single select. 3 of the 5 tables also required the use of only the "last" record (or most current record) for that key combination. I tried using a subselect to get the "last" record for the key, and it worked, but was real real slow. on those, I copied the table to a temporary table in key,date sequence (I could not be assured that the original table was in any type of date order). then updated the report table using this sorted table. This produced quite a few more updates to the table than the subselect, but processed much quicker than the subselect.

    Tuesday, August 17, 2010 6:13 PM
  • Pete, did you check to make sure you had correct indexing in place for that subselect? if not, it could make a _world_ of difference in ther performance of that subquery.
    • Marked as answer by pete schenk Monday, August 23, 2010 6:23 PM
    Wednesday, August 18, 2010 2:00 PM