Access Form

Mac M
Mac M used Ask the Experts™
on
Access Form Design issue, I have a Pivot Table I need to build as a form but I'm unsure how to get my columns and rows like that of the Pivot table attached to build it in the form...Sample data...A crosstab query will not do the job and I'm not that crafty with Transform or UNION....I have attached the sample Spreadsheet with Pivot Table and data for any suggestions or samples on how to concur this is greatly appreciated....Thanks
Copy-of-Copy-of-QryTest.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
The question here is you want A)to reproduce exactly the Excel or B)you want to have a functional form
If  A)you want  to keep the Excel format i think its better to stay on Excel ...is just it where it "excels"
else if B) you  have to take a step back and start thinking about what kind of filtering you want and what kind of pivoting is needed
Mark EdwardsChief Technology Officer

Commented:
Ah, the great issue of displaying pivot tables (crosstab queries) in forms and reports.  The real issue is setting up your form/report to the column headings of your crosstab query.
I use a crosstab query in my work, and put the form/report in design mode and open the query using VBA to set the controlsources for the controls to the column headings, then save and open the form/report - that's one way.  
It can be a little more complicated than that to build a flexible crosstab query-capable form/report, but it can be done.  One issue, however, is you don't have design mode available in runtime mode.
Chief Technology Officer
Commented:
The attached Access file demonstrates how to create a Dynamic Crosstab Form that adapts to the changing column headings in a crosstab query without having to put the form in design mode first.
Change the criteria in the crosstab query to change the headings.
The form's Open event is used to set the label caption and textbox controlsources based on the crosstab recordset field names.
The form's Load event is used to hide/show columns that aren't used.
The form's Unload event is used to reset the form for next time.

Have fun....
Dynamic-Crosstab-Form.accdb
Mac MSolutions Engineer

Author

Commented:
Thanks Mark and John but I took Mark's approach here and it worked out perfectly in my form...I was able to reproduce the spreadsheet view with a drop down to filter the records as well...Thanks again and I hope you both had a wonderful Thanksgiving Holiday...
Mac MSolutions Engineer

Author

Commented:
Thanks Mark and John but I took Mark's approach here and it worked out perfectly in my form...I was able to reproduce the spreadsheet view with a drop down to filter the records as well...Thanks again and I hope you both had a wonderful Thanksgiving Holiday...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial