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

MORE INFORMATION

• 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

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.
2. 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.
3. Click to select the check box for the reference in the Available References list and click OK.

Typing the Code in 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

     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