Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform based on crosstab query (Access 2003 SP1)

    I have a problem with a subform which is based on a crosstab query. The crosstab query has a parameter which uses the value in a textbox on the main form.

    The subform is hidden when the main form opens and the SourceObject property of the subform is blank.

    There is a command button on the main form & when it is clicked the SourceObject property of the subform is set and the subform is made visible. However, when the button is clicked the subform only shows as a blank. The subform shows correctly if I open it on its own, as does the crosstab query on which it is based.

    If I set the SourceObject property for the subform in design view I get the following message when the main form opens :

    "You can't use a pass-through query or a non-fixed column crosstab query as a record source for a subform or subreport."

    I tried setting ColumnHeadings property of the crosstab query and saving it - as recommended in other posts - but then I get the following message when I try to pen the query:

    "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements."

    Can anybody help to find a way around this please

    Usual thanks for the expert help

    Nick

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

    Re: Subform based on crosstab query (Access 2003 SP1)

    How did you specify the column headings?

    As a workaround, couldn't you display the crosstab form as a separate form instead of as a subform? Or create a report and display that?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform based on crosstab query (Access 2003 S

    Hans

    I specified the column headings as a string as follows (as they appear when the query runs):

    "Voyage","load Port","Discharge Port","Expo Start","Expo End",01/03/2008","01/04/2008","01/05/2008","01/06/2008","01/07/2008"

    I really need the form as a subform as it fits in with several other subforms on the main form

    Regards

    Nick

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

    Re: Subform based on crosstab query (Access 2003 S

    Does the column heading field in the crosstab query really contain this mixture of text values and date values?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform based on crosstab query (Access 2003 S

    Hans

    It does. The first 5 columns are text, and are fixed. The next 5 are the months from the current one to month+4. These will change every month.

    Regards

    Nick

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

    Re: Subform based on crosstab query (Access 2003 S

    I'd rethink the setup if I were you, I have the feeling the present one is making things more difficult than necessary.

Posting Permissions

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