Help - Overview
Microsoft Flex Grid Example (MSFLXGRD.OCX) (article date: Apr-1-2009)
This example was built to replace a Microsoft Excel solution with Microsoft Access. In this example the MSFlex Grid is used to improve the Access interface and particulary deal with an issue of editing data from a crosstab query, while leaving the interface close to the customer's original Excel worksheets.
Overview of Situation
The customer had numerous offices and each office was using Microsoft Excel to enter data each month. The data was numbers corresponding to actual outcomes of planned outputs for government grants. Example: they might of expected a grant to create 40 jobs and the actual was 3 jobs were created in Jan 2009. They used one worksheet for each month and had each row corresponding to an output and had columns corresponding to an actual grant number. Every month the number of grants could change so the worksheets would had different numbers of columns. Worked fine in Excel, very easy to add another column and record grant actual numbers. More difficult to rollup the numbers and see for example "How many jobs were created in 2008", or "how many jobs were created for all grants that are part of Office XXX", etc....
Overview of Solution
The solution was to move to an Access database. The table design for this situation was fairly simple, one table for grant information (grantid, grantname, other grant info), one table for outputs (outputid, outputname), and one table to track the actual data (grantid, outputid, date, and the actual number).
Details of the Solution - Installing the example files
Before you can use the example files you must register the MSFLXGRD.OCX file by:
Details of the Solution - Using the example file
They can use the use the Select Grants... button to select individual grants to be added to the grid, for example: at the start of a new month they select the 20 grants that they want to enter data for for that particular month. This would be like starting a new Excel worksheet and adding columns for each grant.
How it works
To understand how it works you will need to look at the code.
The code is a bit tricky but it's best to start with understanding how the crosstab query qryActualCrosstab works. Each of the the buttons on the screen affect how sub parts of this crosstab query pull data so that specific grants and months and offices are used to retrieve the data.
If you liked this article feel free to send us a note or donate a few dollars using paypal. Click here to download the complete code example.