Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subreport error when getting info from XTab query (Access 2000)

    Ok, so I have two tables, one for permanent employees, and one for temporary employees. I use a union select query to join the two tables to compile the information needed for my crosstab query. I use this crosstab query to populate a subreport. When I try to view the report and subreport, I get the error: "You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property."

    I don't know where or how to set this property. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subreport error when getting info from XTab query (Access 2000)

    See the databases attached to the replies to your previous thread.
    Open the crosstab query in design view.
    Click in an empty part of the upper half of the query window.
    Activate the Properties window (View | Properties if it isn't visible)
    Enter a comma-separated list of column names in the Column Headings property.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subreport error when getting info from XTab query (Access 2000)

    Somehow I overlook the little things, like the type of query I'm looking at......a select query doesn't have a Column Headings property. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Now, I have the problem that my subform will not display itself on the same page as my report. I have my report designed to have each location on a separate page, with every company listed below the location....however, my report goes something like:

    Page 1 - Location 1, Permanent Employees (mainreport)
    Page 2 - Location 1, Temporary Employees (subreport)

    I inserted my subreport in the detail section of my main report. I created my report based of a permanent personnel query, copied and pasted it, and changed the source query to an identical temporary personnel query as the source for my subreport. I guess I'm just not sure how to format the report. If my thoughts seem discombobulated, it's because they are....I'm not really sure what I'm doing wrong or where I should go with this.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subreport error when getting info from XTab query (Access 2000)

    If you have created a union query that combines permanent and temporary employees, as stated in the first post in this thread, I don't understand why you have a main report for permanent employees and a subreport for temporary employees. In fact, it's not clear to me at all what the subreport is for, and how it relates to the main report. Perhaps things will become clearer if you try to explain the structure of the report.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subreport error when getting info from XTab query (Access 2000)

    I have two reports because I have two different queries (one for permanent, one for temporary). If I can source both queries without using a subreport, please enlighten me. <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subreport error when getting info from XTab query (Access 2000)

    What then did you mean by "I use a union select query to join the two tables to compile the information needed for my crosstab query."? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Subreport error when getting info from XTab query (Access 2000)

    Are you sure there is room for both the main report data and the subreport on the same page?

    If the subreport won't completely fit on the page, it moves the whole thing to the next page.

    You can decrease the page margins (I usually put them down to 10 0r 12 mm) and try to tighten up the subreport by making each section as small as possible.

    Another question . Why have two separate employee tables? Why not just one table with a field that indicates whether they are permanent or temporary?
    Regards
    John



Posting Permissions

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