by Jason Tyro - GUI Computing
In a strange experiment conducted within a underground bunker in a former communist country, scientists, who only ever strayed into the outside world under the cover darkness and whose surnames tended to end in 'olicov', genetically mated a TrueDB Grid control with a simple Listbox and produced the most terrifying thing mankind has seen since Ross Mack had a third eye removed from his forehead,…..yes it could only be MS FlexGrid !
MSFlexGrid is in fact a version of the VideoSoft VSFLEX control which is shipped with Visual Basic 5.0 so therefore it is absolutely FREE, which is strangely my second favorite word in the English language, my first being PAYRISE of course (editor… are you listening?).
OK, So why use MS FlexGrid and why on earth are you dribbling ?
Well, VB 5 comes with the APEX standard DBGrid control which is your only choice of the 2 if data binding is your funky thang, as MSFlexGrid can only bind in read only mode. However MSFlexGrid has more features in regard to cell merging, data pivoting, though the deciding factor maybe that MSFlexGrid is less than half the size of DBGrid, weighing in at a Mr Bean like 223K where as the DBGrid is a Mike Tyson 508K.
We have found, through extensive and rigorous testing during our tea breaks, that if you require a list box control with a bit of functionality behind it for formatting and sorting data etc. You can do a lot worse than use MSFlexGrid - for instance, you could use a ornamental copper saucepan.
Especially during the all important prototype phase of a project where programmers often find themselves hard hit for time due to the extended periods of development time spent down the local pub, talking with users and other time wasting tasks, using a control like MSFlexGrid can save time, rather than using something a little more complex which could well be canned in the future.
So, what are the alternatives ?
The full featured industry standard is of course APEX's True DBGrid Pro 5. This really is THE grid to have is you want more functionality than something… with lots of functionality. It has more binding than a S&M party. It really covers most issues in a comprehensive way but can be a trifle overpowering when complexity is not really required, a bit like hitting a nut with a Greyhound bus.
With True DBGrid Pro 5 there is a more coding involved which is cool if you require the additional power, but at the same time if you're binding data to a specific datasource Pro 5 is as easy as the other grids mentioned here, so it really is a case of determining the development priorities.
So show me how to use it…
The following application introduces MSFlexGrid and includes some functions to run it in a sane manner. Just open a new project in VB 5, add the MSFlexGrid and DAO 3.5 components.
Create a new form and place a MSFlexGrid on it and paste in the following code. You may need to change the path to the Northwind database and hey, Bob's your Uncle (or Auntie if he is a cross dresser).
'global declarations guff Const TOTALCOLUMNS = 2 'Zero based Dim gSort As Integer Dim gColumn As Integer Private Sub Command1_Click() ClearGrid End Sub Private Sub Form_Load() 'declarations Dim mywrk As Workspace Dim mydb As Database Dim myrs As Recordset Dim myArray As Variant Dim iCount As Integer 'database connect Set mywrk = DAO.Workspaces(0) Set mydb = mywrk.OpenDatabase("C:/Access/Sampapps/NWIND.MDB") 'open recordset based on table Set myrs = mydb.OpenRecordset("SELECT * FROM Customers") 'set array myArray = Array("Customer ID", "Company Name", "Contact Name") 'format MSFlexGrid 'This format creates 1 fixed row for headings etc. 'Row for data has no fixed columns MSFlexGrid1.Rows = 2 MSFlexGrid1.Cols = TOTALCOLUMNS + 1 'Non-zero based MSFlexGrid1.FixedRows = 1 MSFlexGrid1.FixedCols = 0 MSFlexGrid1.FocusRect = flexFocusNone MSFlexGrid1.SelectionMode = flexSelectionByRow 'intialise for sorting gSort = 1 gColumn = 0 'add headings to grid MSFlexGrid1.Row = 0 For iCount = 0 To (TOTALCOLUMNS) 'Zero based MSFlexGrid1.ColWidth(iCount) = 2000 MSFlexGrid1.Col = iCount MSFlexGrid1.Text = myArray(iCount) Next iCount 'add data from recordset into MSFlexGrid While Not myrs.EOF MSFlexGrid1.AddItem myrs.Fields(myArray(0)) & vbTab & myrs.Fields(myArray(1)) & vbTab & myrs.Fields(myArray(2)) myrs.MoveNext Wend 'remove 1st blank row from MSFlexGrid MSFlexGrid1.RemoveItem (1) 'highlight 1st row HighLightGridRow (1) End Sub Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single) 'MSFlexGrid has the strange feature of not being able to recognize 'when the heading of 1st fixed column is clicked on, it calls it Row 1, 'the Row below this also returns as Row 1. 'This bit of code below singles out the heading row which is required in 'this app for sorting the data. If y < MSFlexGrid1.RowHeight(MSFlexGrid1.Row) Then Dim WidthTotal As Long Dim I As Integer WidthTotal = 0 For I = 0 To TOTALCOLUMNS 'Zero based WidthTotal = WidthTotal + MSFlexGrid1.ColWidth(I) If x < WidthTotal Then MSFlexGrid1.Col = I Exit For End If Next I SortGrid End If End Sub Private Sub SortGrid() If MSFlexGrid1.Col = gColumn Then 'toggle sort If gSort = 2 Then gSort = gSort - 1 Else gSort = gSort + 1 End If Else 'new column selected sort ascending gSort = 1 End If 'set current grid column gColumn = MSFlexGrid1.Col 'sort grid with toggle variable MSFlexGrid1.Sort = gSort End Sub Private Sub HighLightGridRow(iRow As Integer) MSFlexGrid1.Col = 0 MSFlexGrid1.Row = iRow MSFlexGrid1.ColSel = TOTALCOLUMNS 'Zero Based MSFlexGrid1.RowSel = iRow End Sub
The finished application should look a little like this.