Results 1 to 3 of 3
  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

    Crosstab query as recordsource for form (Access 20

    I have a crosstab query which I would like to be the recordsource for a datasheet form. However, I have a problem in that I do not know until the query is run how many columns it is going to return (it totals missing records by product), therefore I do not know in advance how many controls the form will need.

    Is there an easy way to dynamically add controls as required ?

    Thanks for the usual expert help

    Nick

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

    Re: Crosstab query as recordsource for form (Access 20

    If the possible column headings come from a fixed set, you can specify the Column Headings property of the crosstab query. It will always have the specified columns, even if there are no data for some of them when the query is run. This enables you to use a fixed form setup.

    If, however, the set of columns changes over time, you'll have to use a dynamic setup. Personally, I'd use a dynamic crosstab report instead of a form (since a crtosstab query is not updatable, a form based on it will be read-only anyway). You'll find information if you search this forum for dynamic crosstab report - for example <post#=35485>post 35485</post#>, <post#=134439>post 134439</post#> or <post#=248210>post 248210</post#>.

    If you prefer to use a form, see <post:=297,950>post 297,950</post:> - <!profile=Zave>Zave<!/profile> wrote code to create such a form from scratch.

  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: Crosstab query as recordsource for form (Acces

    Hans

    Thanks for the pointers - I think I can re-use Zave's code.

    Nick

Posting Permissions

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