Microsoft KB Archive/164922

= OFF97: How to Fill the Microsoft Visual Basic 5.0 FlexGrid Control =

Article ID: 164922

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Word 97 Standard Edition
 * Microsoft PowerPoint 97 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Office 97 Standard Edition

-



This article was previously published under Q164922



SUMMARY
This article includes a Visual Basic for Applications example macro that fills the Microsoft Visual Basic version 5.0 FlexGrid control with values from an external database using the DAO 3.5 Object Library reference.



MORE INFORMATION
Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

This Visual Basic for Applications example uses three routines for future expansion and portability. To use the procedures, place them in the UserForm module. These procedures are as follows:


 * The UserForm Initialize procedure sets the database values to use, such as the database path and name values, and sets the table and field values in the selected table.
 * The DBConnect procedure makes the actual connections to the database, table, and fields that you want to use.
 * The PopulateGridControl procedure populates the FlexGrid control.

Referencing the DAO 3.5 Object Library
For this example to work, you must add the DAO 3.5 Object Library reference.

To add the DAO 3.5 Object Library reference, use the following steps:


 * 1) In the Visual Basic Editor, click References on the Tools menu to display the References dialog box.

The References dialog box displays all object libraries and projects that are registered with the operating system.
 * 1) Scroll through the list for the library you want to reference. If the application is not listed, click Browse to search for object library (.olb and .tlb) files or executable (.exe and .dll) files. References with check boxes that are selected are used by the project; references with check boxes that aren't selected are not used.
 * 2) Click to select the check box for the reference in the Available References list and click OK.

Typing the Code in the UserForm
Type the following code in the General Declarations section of the UserForm: Public oDataBase As Database Public oWorkSpace As Workspace Public oRecordSet As Recordset Public strDB As String Public strDBTable As String Dim strDBField As String ' Array for DataBase field names.

Sample Macro
The following example macro uses the Microsoft Access 97 Northwind.mdb database, which is located in the default Office folder in the Samples folder: Private Sub UserForm_Initialize ' ********************************************     ' Form initialization. ' Enter all values here for Database, Table ' and Field to use. '     ' An array is used in this example to store ' all fields used to obtain information ' from the database. The number of fields used ' must correspond to the number of columns ' in the Flexgrid control. That is, if you ' use three fields, you must have three columns. ' ********************************************     ' DataBase To Use. strDB = Options.DefaultFilePath(wdProgramPath) & _ "\Samples\Northwind.mdb" ' Database Table To Use. strDBTable = "Customers" ' Database Field(s) To Use. ReDim strDBField(2) strDBField(0) = "CompanyName" strDBField(1) = "ContactName" strDBField(2) = "ContactTitle" DBConnect PopulateGridControl End Sub

Sub DBConnect ' ********************************************     ' Initializes Jet workspace and opens a database ' ********************************************     ' Establish database WorkSpace. Set oWorkSpace = CreateWorkspace(Name:="JetWorkspace", _     UserName:="admin", Password:="", UseType:=dbUseJet) ' Open the database. Set oDataBase = OpenDatabase(strDB) ' Set the record set to the specified table. Set oRecordSet = oDataBase.OpenRecordset(strDBTable) End Sub

Sub PopulateGridControl ' ********************************************     ' Populates the grid with data ' ********************************************     Dim rownum As Integer Dim icount As Integer ' Create number of columns to equal, at least, number of fields. If MSFlexGrid1.Cols < UBound(strDBField, 1) + 1 Then MSFlexGrid1.Cols = UBound(strDBField, 1) + 1 End If     ' Populate grid header with field names. For icount = 0 To UBound(strDBField, 1) MSFlexGrid1.TextMatrix(0, icount) = strDBField(icount) Next ' Fill grid with data from field(s) in the table. oRecordSet.MoveFirst Do Until oRecordSet.EOF rownum = rownum + 1 MSFlexGrid1.AddItem "" For icount = 0 To UBound(strDBField, 1) MSFlexGrid1.TextMatrix(rownum, icount) = _ oRecordSet.Fields(strDBField(icount)) Next oRecordSet.MoveNext ' Move to next record in table. Loop End Sub NOTE: To use the example, change the values to the full database path and name and change the values for the table and fields that you want to use.

For additional information, please see the following article in the Microsoft Knowledge Base:

164815How To Create And Display A Custom Dialog Box

Additional query words: wordcon word8 word97 8.00 8.0 vb vbe vba xlvbainfo

Keywords: kbhowto kbprogramming KB164922

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.