Tutorial 7 - Defining Unbound Columns in a Bound List

In this tutorial, you will learn how to use the UnboundColumnFetch event to display two fields (FirstName and LastName) together in one column. You will also learn how to use an SQL statement to create a join between two tables in a database. The project we set up for this tutorial will also be used in Tutorials 8 through 11.

Start a new project.
Place a Data control (Data1) and a True DBList control (TDBList1) on the form (Form1).

Set the DatabaseName (Data control) property of Data1 to TDBLDemo.mdb, and the RecordSource (Data control) property to the following SQL statement:

Example Title	Copy Code
SELECT * FROM Contacts INNER JOIN Customers ON Contacts.UserCode = Customers.UserCode

The Contacts table contains records of recent customer contacts, but in the table, the customers contacted are recorded by their internal UserCode only, making the table difficult to use by itself.The Customers table contains customer data such as UserCode, FirstName, LastName, and so forth. Therefore, we create a join so that we can view the recent contact information along with the corresponding customer data.

Set the Caption property of Data1 to Customer Contact.
Set the RowSource property of TDBList1 to Data1.

Configuring the list at design time

We shall configure the list using its context menus and property pages. For more details, see Design Time Interaction.
Right-click the list to display its context menu.
Choose Edit from the context menu.The list will enter its visual editing mode, enabling you to interactively change the list's row and column layout.
By default, the list contains two columns. We are going to create three more. Right-click anywhere in the list to display the visual editing menu. Choose the Append command to add a new column at the end of the list. Execute this command two more times to create two more columns. A total of five columns are now in the list.
Right-click again to display the visual editing menu. This time choose Properties… to display the Property Pages dialog box. Select the Columns property page by clicking the Columns tab. The tree node Columns(00) is selected.Double-click the selection or click the + to the left of Columns(00). The tree will expand to reveal the available properties for the first column, Column0, which you will configure as an unbound column. Select the Caption property, then type Customer Name into the text box on the right side of the page. Keep the default values for the other properties, including DataField. If the DataField property of a column is blank (that is, equal to an empty string), but its Caption property is not, True DBList considers it an unbound column.
Click the – to the left of Columns(00) to close this branch, then open the Columns(01) object. Click the DataField property to reveal a list of all the fields in the joined table. Choose CustType (the last item) from the list. The Caption property will default to the same name.
Repeat the previous step with the remaining three columns. Columns(02): DataField = ContactType, Column(03): DataField = Callback, Columns(04): DataField = ContactDate.
After configuring the five columns, click the OK button at the bottom of the Property Pages dialog box to accept the changes.
Note that you are still in the list's visual editing mode. Place the mouse cursor over the column dividers within the column header area.It will turn into a horizontal double-arrow cursor, indicating that column resizing can now occur. Drag the dividers (use the horizontal scroll bar to bring a column into view if necessary) so that the list looks like the one in the following figure.
Click Form1 anywhere outside TDBList1 to exit visual editing mode.You have now finished configuring the list.

Displaying data in the unbound column

In step 9, Column0 of the list was configured as an unbound column, so you must supply its data using the UnboundColumnFetch event.When the list needs to display data in an unbound column, it calls this event to get the necessary data.The following code shows how to display the combined FirstName and LastName fields in the unbound column. For more information on unbound columns, see Unbound Columns.

Declare RSClone as a Recordset (Data control) in the General section of Form1 so that the RSClone variable will be available in all procedures in Form1:

Example Title	Copy Code
Dim RSClone As DAO.Recordset

In the Form_Load (Visual Basic) event, set RSClone to be a clone of Data1.Recordset.The Data1.Refresh statement is necessary to make sure Data1 is initialized before cloning its Recordset (Data control).

Example Title	Copy Code
Private Sub Form_Load() Data1.Refresh DoEvents Set RSClone = Data1.Recordset.Clone End Sub

Finally, define data in the unbound column by combining the FirstName and LastName fields of the Recordset (Data control) in the list's UnboundColumnFetch event:

Example Title	Copy Code
Private Sub TDBList1_UnboundColumnFetch( _ Bookmark As Variant, _ ByVal Col As Integer, Value As Variant) RSClone.Bookmark = Bookmark Value = RSClone("FirstName") & " " & RSClone("LastName") End Sub

When the UnboundColumnFetch event is called, the Bookmark argument specifies which row of data is being requested by the list.Note that Bookmark does not usually refer to the current row, since the list displays more than one row at a time. Hence we use a clone (RSClone) to get data from the Recordset (Data control) so that we do not change the current row position of the Data control. In this example, we only have one unbound column, so we ignore the Col argument.

Run the program and observe the following:

The True DBList control displays data from the joined table according to the five columns configured at design time.
The first column displays the combined FirstName and LastName fields as defined in the UnboundColumnFetch event.
The CustType, ContactType and Callback columns display numeric values which are quite cryptic to users.You might also comment that the data presentation is not so appealing. In the next three tutorials (8, 9, and 10), we will illustrate techniques to improve both the display and the user interface.

To end the program, press the End button on the Visual Basic toolbar. You have successfully completed Tutorial 7.