Create a Windows Form. Then place the controls on the form shown in Figure 8.7, with the properties set forth in Table 8.5.

Table 8.5. Label, TextBox, and Command Button Controls Property Settings for the Calling Form

Object	Property	Setting
Label	Caption	Customer ID
Label	Caption	Company Name
Label	Caption	Contact
Label	Caption	Contact Title
Label	Caption	Address
Label	Caption	City
Label	Caption	Region
Label	Caption	Country
Label	Caption	Phone
Label	Caption	Fax
TextBox	Name	txtCustomerID
TextBox	Name	txtCompanyName
TextBox	Name	txtContact
TextBox	Name	txtContactTitle
TextBox	Name	txtAddress
TextBox	Name	txtCity
TextBox	Name	txtRegion
TextBox	Name	txtPostalCode
TextBox	Name	txtCountry
TextBox	Name	txtPhone
TextBox	Name	txtFax
Button	Name	btnSearch
	Caption	&Search

On btnSearch, add the code in Listing 8.17 to the Click event. This routine shows the power of creating custom properties. After instantiating an instance of the Search form-in this case, frmHowTo8_4b.vb-the four custom properties shown in Listing 8.17 are set before the form is displayed. This is powerful in letting you get the form set up exactly the way you want to before the user even sees it. After setting up the custom properties, the ShowDialog method is called off of frmSearch. By calling this method, code execution is halted until the form is closed or hidden. This same line of code compares the DialogResult property of the form to the value; if it matches, the code calls the LoadIndividual routine, passing the ResultValue custom property of frmSearch. Both the DialogResult and ResultValue properties are set in frmSearch and will be shown later in these steps.

Listing 8.17 frmHowTo8_4a.vb: Executing a SQL Server-Supplied Stored Procedure That Lists the Tables in the Database

Private Sub btnSearch_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles btnSearch.Click

        '-- Instantiate the search forms.
        Dim frmSearch As frmHowTo8_4b
        frmSearch = New frmHowTo8_4b()

        '-- Set the custom data properties on the search form.
        '   This is what makes it so data driven.

        frmSearch.DisplayName = "Customers"
        frmSearch.SearchRecordSource = "Customers"
        frmSearch.SearchField = "CompanyName"
        frmSearch.KeyField = "CustomerID"

        '-- Open the search form as dialog.
        '   Based on the DialogResult property, use the
        '   custom property ResultValue property and load
        '   the requested record.

        If frmSearch.ShowDialog() = DialogResult.OK Then

            LoadIndividual(frmSearch.ResultValue)

        End If

End Sub

Create the LoadIndividual routine by entering the code shown in Listing 8.18 into the form. Taking the strKeyValue passed from the results of the search, a data adapter is created and a DataSet is filled. Next, the individual data row is created. Last, each of the TextBox controls is loaded with the value from the column with the corresponding name. Notice the use of the Try…Catch…End Try to ignore controls that don't have a like column in the data row.

Listing 8.18 frmHowTo8_4a.vb: Loading an Individual Record into Text Boxes on the Form

Private Sub LoadIndividual(ByVal strKeyValue As String)

        Dim strSQL As String
        Dim strName As String
        Dim oCtl As Object

        Dim dsCustIndiv As New DataSet()
        Dim odaCustIndiv As OleDb.OleDbDataAdapter
        Dim drCustIndiv As DataRow

        Try
            '-- Load the individual record into the dataset
            strSQL = "Select * from Customers Where CustomerID = '" &
                             strKeyValue & "'"
            odaCustIndiv = New OleDb.OleDbDataAdapter(strSQL, _
                             BuildCnnStr("(local)", "Northwind"))

            '-- Fill the dataset
            odaCustIndiv.Fill(dsCustIndiv, "Customers")

            '-- Grab the individual data row
            drCustIndiv = dsCustIndiv.Tables("Customers").Rows(0)

        Catch oexpData As OleDb.OleDbException

            MessageBox.Show("Error loading individual data: " & _
           oexpData.Message)
            Exit Sub

        End Try

        '-- Run through the text boxes on the form.
        '-- If they match up with a field from the record, load them.

        For Each oCtl In Me.Controls

            If TypeOf oCtl Is TextBox Then

                strName = Mid(oCtl.Name, 4)

                '-- By trapping the exception this way, errors are ignored.
                Try
                    oCtl.text = drCustIndiv(strName).ToString
                Catch oexp As Exception
                End Try

            End If

        Next

End Sub

Create the next Windows Form and call it whatever name you referred to in the search form in step 2. Then place the controls shown in Figure 8.7 of the search form, with the properties set as in Table 8.6.

Table 8.6. Label, TextBox, DataGrid, and Command Button Controls Property Settings for the Calling Form

Object	Property	Setting
GroupBox	Name	GroupBox1
	Text	Click on a Letter
Button	Name	btnA
	Caption	A
Button	Name	btnB
	Caption	B
Button	Name	btnC
	Caption	C
...
Button	Name	btnZ
	Caption	Z
Button	Name	btnAll
	Caption	All
DataGrid	Name	dgSearch
Button	Name	btnAccept
	Caption	&Accept
Button	Name	btnCancel
	Caption	&Cancel

Create the custom properties discussed in the "Technique" section of this How-To and found in Listing 8.19. Each of the properties is self-explanatory.

Listing 8.19 frmHowTo8_4b.vb: Creating Custom Properties for the Search Form

Private mstrDisplayName As String
Private mstrRecordSource As String
Private mstrSearchField As String
Private moResultValue As Object
Private mstrKeyField As String

Public Property DisplayName() As String
    Get
        DisplayName = mstrDisplayName
    End Get
    Set(ByVal Value As String)
        mstrDisplayName = Value
    End Set
End Property

Public Property SearchRecordSource() As String
    Get
        SearchRecordSource = mstrRecordSource
    End Get
    Set(ByVal Value As String)
        mstrRecordSource = Value
    End Set
End Property

Public Property SearchField() As String
    Get
        SearchField = mstrSearchField
    End Get
    Set(ByVal Value As String)
        mstrSearchField = Value
    End Set
End Property

Public Property KeyField() As String
    Get
        KeyField = mstrKeyField
    End Get
    Set(ByVal Value As String)
        mstrKeyField = Value
    End Set
End Property

Public Property ResultValue() As Object
    Get
        ResultValue = moResultValue
    End Get
    Set(ByVal Value As Object)
        moResultValue = Value
    End Set
End Property

On the form, add the code in Listing 8.20 to the Load event. This routine ensures that the calling form set the DisplayName custom property; thus, this routine can assume that the others were set as well. If not, a message box is displayed. If so, the Text property of the form, which is displayed in the Title bar, is set to DisplayName.

Listing 8.20 frmHowTo8_4b.vb: Executing a SQL Server-Supplied Stored Procedure That Lists the Tables in the Database

Private Sub frmHowTo8_4b_Load(ByVal sender As Object, _
                    ByVal e As System.EventArgs) Handles MyBase.Load

        If Len(Me.DisplayName) = 0 Then

            MessageBox.Show("Form specific properties not set.")
            Me.Close()

        Else

            Me.Text = Me.Text & " " & Me.DisplayName

        End If

End Sub

For each of the command buttons that has a single letter, add the first subroutine displayed in Listing 8.21 to each of the Click events. For the btnAll Button control, add the second subroutine to the Click event. Each Button control will pass the letter it represents to the subroutine called SetData, discussed in the next step. The btnAll code simply passes the empty string.

Listing 8.21 frmHowTo8_4b.vb: Click Events for Each of the Letter Button Controls

Private Sub btnA_Click(ByVal sender As System.Object,
                ByVal e As System.EventArgs) Handles btnA.Click
        SetData("A")
End Sub
Private Sub btnAll_Click(ByVal sender As System.Object,
 ByVal e As System.EventArgs) Handles btnAll.Click
        SetData("")
End Sub

Add the subroutine in Listing 8.22 to the class module of the form. This routine takes the letter value passed in strFilterLetter as a parameter. A SQL Select string is created that takes the literal values Select, From, and Where and uses the custom properties KeyField, SearchField, and SearchRecordSource. The SearchField property is used with the Like clause, also using the strFilterLetter and the % (wildcard). Note that if "" is passed to strFilterLetter, all the records will be listed. Finally, odtSearch is filled and set as the data source for dgSearch, which is the DataGrid control.

Listing 8.22 frmHowTo8_4a.vb: Filling the Results Set Based on the Letter Button That Was Pressed

Sub SetData(ByVal strFilterLetter As String)

        Dim odaSearch As OleDb.OleDbDataAdapter
        Dim dtSearch As DataTable = New DataTable()

        odaSearch = New _
            OleDb.OleDbDataAdapter("Select " & Me.KeyField & ", " &
            Me.SearchField & " From " & Me.SearchRecordSource & " Where " &
            Me.SearchField & " Like '" & strFilterLetter & "%'",
                      (BuildCnnStr("(local)", "Northwind")))

        odaSearch.Fill(dtSearch)
        dgSearch.DataSource = dtSearch

    End Sub

On the buttons called btnAccept and btnCancel, add the code in Listing 8.23 to the appropriate Click event of each. The btnAccept_Click routine creates a DataTable object from the data grid's DataSource property. Then it derives the data row that is currently selected from that data table. The KeyField property is used to store the individual column value of drCurr into the ResultValue custom property. The DialogResult property is set to OK, and the form is hidden with the Hide method. By hiding the form, you can still read the properties of the form without the user seeing it.

In the btnCancel_Click routine, the DialogResult is set to No, and the form is closed. This action tells the calling form that the search was canceled.

Listing 8.23 frmHowTo8_4b.vb: Storing the Resulting Key Value to the ResultValue Custom Property and Setting the DialogResult

Private Sub btnAccept_Click(ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) Handles btnAccept.Click

        Dim dtFromGrid As DataTable
        Dim drCurr As DataRow

        Try

            '-- Using the DataRow and DataTable objects of the DataGrid control,
            '   get the selected result and assign it to the custom property
            '   ResultValue. Then set the DialogResult
           '     property to DialogResult.OK,
            '   and hide the form so that the calling form can still access it.

            dtFromGrid = CType(dgSearch.DataSource, DataTable)

            drCurr = dtFromGrid.Rows(Me.dgSearch.CurrentRowIndex())
            Me.ResultValue = drCurr(Me.KeyField).ToString
            Me.DialogResult = DialogResult.OK
            Me.Hide()

        Catch exp As Exception
            Me.DialogResult = DialogResult.No
            Me.Close()

        End Try

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) Handles btnCancel.Click

        Me.DialogResult = DialogResult.Cancel
        Me.Close()

End Sub