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).

To retrieve data from this table design fell naturally to a crosstab query, where rows are outputs and columns are grants and the data is the actual data points. Unfortunately you can’t enter or modify data using a crosstab query in Access. The solution was to use a grid control (currently using the MSFLXGRD.OCX control, because it’s free, other grid controls would work as well or better).

Details of the Solution – Installing the example files

Before you can use the example files you must register the MSFLXGRD.OCX file by:

Copying the MSFLXGRD.OCX file to your \Windows\System32 directory and using Start/Run and running regsvr32.exe msflxgrd.ocx or in Vista you right click on a command prompt (choose Run as Administrator) and then type regsvr32.exe msflxgrd.ocx You should see dllRegisterServer in msflxgrd.ocx succeeded. After this control is registered the example database should work fine. Note: You must use version 6.1.98.12 or later, Microsoft issued a patch that makes earlier versions of this OCX file not work.

Details of the Solution – Using the example file

The main screen:

Lets the user do a few things.

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.
They can use the Get Grants With Data… button to retrieve grants that have data recorded for a particular month. Example: user selects Jan 2009 for a particular office and it retrieves all grants that have data for Jan 2009 for that particular office and populates the grid. This would be like returning to an existing excel spreadsheet.
They can run various reports that summarize or print out data.

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..