VB3 How to Fill a List Box from a Snapshot Generically

ID: Q115712

3.00 WINDOWS

The information in this article applies to:

Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SUMMARY

This article shows by example how to fill a list box with a snapshot when the contents of the snapshot are unknown at design time. The example uses TAB characters to create a columnar display, and it adds a horizontal scroll bar so the user can view the entire record.

MORE INFORMATION

When handling result sets from database queries that produce variable result sets, you may find it useful to have a generic function display the contents in a list box.

The following example defines a Sub (Fill_List) that accepts a list-box control and snapshot as parameters and fills the list box with the contents of the snapshot. The function can be easily modified to accept a table or dynaset object as well.

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

   ARTICLE-ID :Q71067
   TITLE      :How to Set Tab Stops in a List Box in Visual Basic

Step-by-Step Instructions for Creating the Program

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add a command button (Command1) and a list box (List1) to the form. Set

   the caption property of Command1 to "Fill list".

3. Add the following code to the form's general declarations section:

   Option Explicit
   Declare Function SendMessage Lib "User" (ByVal hWnd As Integer, ByVal
   wMsg
   As
    Integer, ByVal wParam As Integer, lparam As Any) As Long
   Const WM_USER = &H400
   Const LB_SETTABSTOPS = WM_USER + 19
   Const LB_SETHORIZONTALEXTENT = WM_USER + 21

   Sub Fill_List (lb As ListBox, sn As Snapshot)
     Const NUMCHARS = 2  ' Amount of white space between columns.

     ' Temporary variables to preserve form font settings:
     Dim hold_fontname As String, hold_fontsize As Integer
     Dim hold_fontbold As Integer, hold_fontitalic As Integer
     Dim hold_fontstrikethru As Integer, hold_fontunderline  As Integer

     Dim whiteSpace As Integer, accumtabstops As Integer, dialogUnits As
   Integer
     Dim fieldVal As String, listline As String
     Dim avgWidth As Single

     Dim retval As Long, i As Integer
     Dim biggest_value() As Single
     Dim tabstops() As Integer

     ' Save form's font settings so we can use the form to calculate the
     ' TextWidth / Height of the strings to go into the list box.
     hold_fontname = Me.FontName
     hold_fontsize = Me.FontSize
     hold_fontbold = Me.FontBold
     hold_fontitalic = Me.FontItalic
     hold_fontstrikethru = Me.FontStrikethru
     hold_fontunderline = Me.FontUnderline

     ' Set form font settings to be identical to list box.
     Me.FontName = lb.FontName
     Me.FontSize = lb.FontSize
     Me.FontBold = lb.FontBold
     Me.FontItalic = lb.FontItalic
     Me.FontStrikethru = lb.FontStrikethru
     Me.FontUnderline = lb.FontUnderline

     ' Get the average character width of the current list box font
     ' (in pixels) using the form's TextWidth width method.
     avgWidth =
   Me.TextWidth("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")
     avgWidth = avgWidth / Screen.TwipsPerPixelX / 52

     ' Set the white space you want between columns.
     whiteSpace = avgWidth * NUMCHARS

     ReDim biggest_value(0 To sn.Fields.Count - 1)
     ReDim tabstops(1 To sn.Fields.Count)

     lb.Clear

     ' Loop through the field values for each record in the snapshot.
     ' Calculate the width required for that field value to fit in the list
     ' box. Also, build each line of the list box and add it to the list as
     ' you go.
     While Not sn.EOF
       For i = 0 To sn.Fields.Count - 1
         fieldVal = sn(i) & ""       ' Append "" in case of a null field.

         ' The LB_SETTABSTOP message requires coordinates in dialog units
         ' (roughly 4 *, the average character width in pixels).
         dialogUnits = ((Me.TextWidth(fieldVal) / Screen.TwipsPerPixelX +
   whiteSpace) \ avgWidth) * 4
         If dialogUnits > biggest_value(i) Then
           biggest_value(i) = dialogUnits
         End If

         listline = listline & sn(i) & Chr$(9)
       Next i

       lb.AddItem listline
       listline = ""
       sn.MoveNext
     Wend

     ' Fill the tabstops() array with the position of each tab stop.
     For i = 0 To sn.Fields.Count - 1
       accumtabstops = accumtabstops + biggest_value(i)
       tabstops(i + 1) = accumtabstops
     Next i

     ' Send LB_SETTABSTOP to the list box to set the position of each
       column.
     retval& = SendMessage(lb.hWnd, LB_SETTABSTOPS, i, tabstops(1))

     ' Set the horizontal extent just wider than the first tab stop.
     ' This will produce a horizontal scroll bar on the list box.
     ' This message requires coordinates in pixels, so we convert the tab
     ' stop coordinate back from dialog units to pixels.
     retval& = SendMessage(lb.hWnd, LB_SETHORIZONTALEXTENT, (tabstops(i) \ 
   4)
   *
   avgWidth, 0&)

     ' Restore form's original font property settings.
     Me.FontName = hold_fontname
     Me.FontSize = hold_fontsize
     Me.FontBold = hold_fontbold
     Me.FontItalic = hold_fontitalic
     Me.FontStrikethru = hold_fontstrikethru
     Me.FontUnderline = hold_fontunderline
   End Sub

4. Add the following code to the Command1_Click event:

   Sub Command1_Click ()
     Dim db As database
     Dim sn As Snapshot

     Set db = OpenDatabase("BIBLIO.MDB")
     Set sn = db.CreateSnapshot("select * from [title author publisher]")
     Fill_List list1, sn

     Set sn = Nothing
     db.Close
   End Sub

5. Run the program by pressing the F5 key. Now, the list box contains neat

   columns displaying the contents of the [Title Author Publisher] query.
   The scroll bar enables you to see the entire line. Alter the NUMCHARS
   constant to allow more white space between columns if you want.

KBCategory: KBSubcategory: APrgDataOther Additional reference words: 3.00

Keywords          : kbcode 
Version           : 3.00
Platform          : WINDOWS

Last Reviewed: May 23, 1998