Crosstab query as recordsource for form (Access 20

Topic

New Reply

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

Reply | Quote

Replies

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 134439 or post 248210.

If you prefer to use a form, see post 297,950 – Zave wrote code to create such a form from scratch.

Reply | Quote

Hans

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

Nick

Reply | Quote