Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Oklahoma
    Posts
    10

    Question Unanswered: Change the recordset of a report.

    I've got a form that builds a query. I need to run a report (already built) based on this query (the same query fields but different conditions) by clicking a button on the form. I can't just pass the filter because its too long and access throws a fit.

    How can I do this?
    -Dustin
    Rom 8:28

  2. #2
    Join Date
    Jan 2003
    Posts
    1

    Re: Change the recordset of a report.

    When you have compiled the WHERE clause (withouth WHERE), just pass the clause on to the report as you open it. Use the docmd.openreport, then pass on the where condition or the filter.

  3. #3
    Join Date
    Jan 2003
    Location
    Oklahoma
    Posts
    10

    Re: Change the recordset of a report.

    Originally posted by Toby Sand
    Use the docmd.openreport, then pass on the where condition or the filter.
    Well, thats how I have it working right now.. it doesn't work all the time. Sometimes the queries are pretty complex and access gives me an error saying that the filter is too complicated. I'm working with a large amount of fields here and over 10000 records, I need to limit my records before I open the report, not just filter them afterwards.

    I'm working on something right now using VBA and creating a report object.. I'm a c++/java guy though and my skills with VB are pretty rusty. Any help would be appreciated.
    -Dustin
    Rom 8:28

  4. #4
    Join Date
    Apr 2002
    Posts
    139

    Re: Change the recordset of a report.

    "Sometimes the queries are pretty complex and access gives me an error saying that the filter is too complicated"

    If your query sequence becomes to complex, consider to put a make-table query somewhere inbetween.
    Then proceed with a new query based on your temp table.
    Gives Access a much easier query schedule to execute.

    hth

  5. #5
    Join Date
    Jan 2003
    Location
    Oklahoma
    Posts
    10
    Well, I've reworked a lot of it now. I'm trying to populate my temp table now using INSERT INTO. I'm doing this now because the query takes a while to execute and the user is going to use the data for several reports at a time.

    I'm getting an error when I make the query, "Resultant table not allowed to have more than one autonumber field." when I do so. The query is quite complex (being built off of multiple queries which are built of multiple queries). I built my temp table with matching fields for the query. Two of the fields in the query show up as autonumbers but I made the temp table use regular number fields instead.

    Any ideas how to make this work?
    -Dustin
    Rom 8:28

  6. #6
    Join Date
    Apr 2002
    Posts
    139
    With the Insert Into statement, Access will design your table for you, which sometimes failes, like here.

    Try the following:

    Design the Temp-table yourself with all fields you require.

    In your query sequence, first empty the Temp-table by Delete * and than use an Append query to add the data you want.
    So your original design of the Temp-table is maintained.

    hth

Posting Permissions

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