Building Self-Populating Controls

One type of control that's of interest is a combo box that can be placed on a form and will automatically load its data. It can be filtered or unfiltered , but this is a reasonable place to describe how to build them.

Add the code shown in Listing 9.20 to MyFormControls.vb .

Listing 9.20. The SelfLoadingCombo Class

 Public Class MySelfLoadingCombo     Inherits MyCombo     Private _TableName As String = ""     Private _DisplayName As String = ""     Private _ValueName As String = "" Public Property TableName() As String     Get         Return _TableName     End Get     Set(ByVal Value As String)         _TableName = Value     End Set End Property Public Property DisplayName() As String     Get         Return _DisplayName     End Get     Set(ByVal Value As String)         _DisplayName = Value     End Set End Property Public Property ValueName() As String     Get         Return _ValueName     End Get     Set(ByVal Value As String)         _ValueName = Value     End Set End Property Public Sub New()     MyBase.New()     Enabled = True     Tag = "SELFLOADING" End Sub Public Sub LoadData() If TableName <> "" Then     Dim DataAccess As New DataAccessManager.DataAccessFileManager     DataAccess.ReadFile()     Dim ConnStr As String     ConnStr = DataAccess.ConnString     DataAccess = Nothing     Dim SqlCmd As String = "SELECT " + ValueName + "," + DisplayName _                          + " FROM " + TableName + " ORDER BY " + DisplayName     Dim sda As New SqlClient.SqlDataAdapter(SqlCmd, ConnStr)     Dim dt As New DataTable     sda.Fill(dt)     DataSource = dt     DisplayMember = DisplayName     ValueMember = ValueName     sda = Nothing End If End Sub End Class 

Recompile the MyControls project, then open the toolbox, right-click and select Add/Remove controls, and add the control to the toolbox.

However, this doesn't run the LoadData method. And simply adding a call to the method in the New() constructor doesn't do a thing. However, you can use that little TAG entry ( Tag = "SELFLOADING" ) to add some code to your form template that makes sure the combo is loaded (see Listing 9.21).

Listing 9.21. Load Event Code for a Form Containing Self-Loading Controls

 Dim Ctrl As Control For Each Ctrl In Controls     If Ctrl.Tag.ToUpper = "SELFLOADING" Then         Dim o As Object         o = Ctrl         o.LoadData()     End If Next 

How does this tie in with filtering? It allows us to build a combo box that's always desirable when dealing with search screens. We can build a list on the fly of the unique values of any column in a searchable table, by making a small change in the base class:

 Dim SqlCmd As String = "SELECT DISTINCT " + DisplayName + "," + DisplayName _                      + " FROM " + TableName + " ORDER BY " + DisplayName 

Give this class a different name , and you can build a search form that lets users pick from only the values that have been entered into a free-form field in a table.