Programming Microsoft Word - 11 - Enhancing the User Experience (autocomplete)

What are we going to do in this tutorial?

In the previous tutorial, you enhanced a program to write out input from the program (details of parties to a contract) in a Microsoft Word document. Now that the program can work with data including output thereof, it's time to enhance the user experience.

There are two items that can use an upgrade. First item is the lookup form for accessing the database (in the program, there are two lookup forms; one for Individuals and one for Companies). Scanning through the data records one by one is not very handy. The user could use a datagrid view in this form in which data can be sorted and searched. Much easier if there are hundreds of records available.

Second item is the main form. Wouldn't it be easy for the user to insert an auto search function? When the user types a name of an Individual or Company in the name box, the program could auto search the database already and present options in a dropdown list. Users would love it.

Let's get to work with the first item on the wish list.

Step 1

Open the project you worked on in the previous tutorial and double click the 'frmNPLookup.vb' file in the Solution Explorer.

Resize the form to 812, 700 (see picture below) and make the textboxes a bit larger (the picture shows 700, 600 → change this to 812, 700):

Do the same with the 'BasefmLookup' form (resize it to 812, 700). Doing so will ensure that the buttons on the frmNPLookup form - that are derived from this base form - will be visible after resizing the frmNPLookup form.

Switch back to the design view of the NPLookup form. Now, make sure that the Toolbox is visible at the left side of the Visual Studio IDE. Look for the 'DataGridView', click and hold it and drag it onto the lookup form just below the textboxes.

A popup menu appears with some settings for the DataGrid View.

Deselect all boxes shown (unless you have wish that the user can use the stated functionality). Next step is to choose the data source by means of this menu. Pull down the dropdown menu and select 'TbIndividualsBindingSource'.

Next, edit the columns by clicking the relevant link in the same popup window.

You can delete the ID column because this data is not interesting for our user.

In respect of the other columns, you want to show your users a normal name for each column without the 'NP' prefix. Change the name of each column by selecting each Column in this menu and changing the name property.

Finally, select the DataGridView box on the look up form and change its name from 'DataGridView1' to 'DataGridViewNP' in the Properties window.

Run the program and look up the details of an Individual. The look up form pops up with the new look, including a DataGridView window. Play around with it and add another party by typing new data in the textboxes, adding it to the database with the '+' button above. Notice that you can sort the data grid view alphabetically by clicking a header and that you can select data in the data grid view. Any selection chosen is automatically also shown in the text boxes.

One more thing to add here.

Step 2

Upon loading the lookup form, the user is presented with the data grid view as an unsorted list of data. Let's aid the user and have the data in the first column (Name) automatically sorted when the user loads the look up form.

Your program is going to do this by inserting code that is executed when the look up form is loaded. Click the look up form in the design view (but not a button, textbox or other item on the form. If done correct, Visual Studio brings you to the code for the form's Load event. There is already a line of code there, basically loading the data from the database into the program.

Add a new line of code below the existing code (but before the 'End Sub'):

DataGridViewNP.Sort(DataGridViewNP.Columns(0), ListSortDirection.Ascending)

This leads to an error message. The error message can be removed by inserting at the very top of the code (before 'Public Class frmNPLookup'):

Imports System.ComponentModel

That's it. Run the program and notice that when bringing up the look up form for Individuals, the data in the Name column of the data grid view is neatly alphabetically sorted.

Repeat all steps for the lookup form for Companies but chose the datasource for Companies, not Individuals, and name the DataGridView → 'DataGridViewRP'

Step 3

You have upgraded the Lookup form considerably. The user can look up specific data from the datagrid view, which is automatically sorted alphabetically by name . But an even better enhancement is possible.

The program has all data from the database available. The user would be very thrilled if your program actually understands that the user is inserting details of a party that it already contained in the database. What I am basically suggesting is an auto suggest mode for the 'name' textbox on the main form (form1). If the user types a name, the program could look into the available data and check whether that name exists in the database. If so, the program could suggest the party's name to the user and, upon the user selecting this suggested name, retrieve all of that party's details and directly fill out the remaining textboxes on form1. This saves your user the steps of opening the lookup form and finding the relevant party.

You are going to program this functionality on form1. In essence, your code will enable the following logic. The code on form 1 will have access to the information from the database, which database is already linked to the program. From this information, only the 'Names' are retrieved and put into a list. This list is used to populate an Autocomplete feature of textboxes. Once an item from the autocomplete list is selected, the program will look up the other elements belonging to that name (like address, city, etc) and populates the other textboxes.

Let's get started.

Double click on the Form1.vb file in the Solution Explorer.

In the tabpage 'Individuals', select the textbox (txtNPName). Scroll down in the Properties window and set AutoCompleteMode to 'Suggest' and AutoCompleteSource to 'CustomSource'.

The data to fill the source for 'AutoCompleteSource' will be taken from the database already linked to the program. In the Toolbox, scroll upwards and find the datacomponent 'TbIndividualsTableAdapter'. This element is in the Toolbox because the program uses this for the Individuals lookup form. Click and drag this element onto the form. The element will appear at the bottom of the form (with a '1' added to its name).

From this data source, the program will build a list of names. The code is as follows:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load

    End Sub

Upon loading the Form1, the code calls the procedure 'UpdateDataTable()'. This makes the data available for use for the AutoComplete feature.

Insert code for the procedure 'UpdateDataTable()' as follows:

Private Sub UpdateDataTable()

        Dim dt As DataTable = TbIndividualsTableAdapter1.GetData

        For Each dr As DataRow In dt.Rows

    End Sub

Analysis of the code: the code defines a datatable that is immediately filled with all data via the TbIndividualsTableAdapter1. This adapter has an extension 'GetData' that returns all data. If you want to see where this extension comes from, double click on 'DbPartiesDataSet' in the Solution Explorer. In the TbIndividuals block that appears, you can see the 'GetData' element (as 'Fill,GetData()). Basically, this is a command to look into the attached database and return certain elements of the database. Note that you deliberately ask to return all data of the database (names, addresses etc) and not just the names. It will become clear later on why.

Back to the analysis. Now that there is datatable 'dt' filled with data, the subsequent 'For … Next' loop takes each row in the datatable and searches for the column 'NPName' in that row. The result of that search is added to the AutoComplete source. In the end, the AutoComplete source has a list of all names contained in the datatable. If you run the program, the autocomplete function should already be working.

Step 4

Ok, you got Autocomplete working. But as indicated above, this feature will only be very powerful if - upon selecting one of the suggested names - the program automatically fills out the remaining textboxes on form1 with the appropriate data. Before delving into the code, you need to build a custom messagebox. It is possible that there are multiple parties in the data with the same name. When the user selects a name that could belong to multiple parties, we want the program to pop up a messagebox, showing all relevant parties with all details (address, city etc). This enables the user to select the correct party and get on with the program.

Because you need the messagebox twice (also for the Companies part of the program), it makes sense to start with a 'base form'. The base form can serve to build both the messagebox for Individuals and the messagebox for Companies.

Add a form to the project with the name 'BaseMessageBox'. Add two buttons to the form ('btnCancel' and 'btnOK') (see picture).

Select the OK button. In the Properties window, set the 'DialogResult' property to 'OK' (for the Cancel button, this property will be 'Cancel'). Alternatively, you can set the buttons' behaviour when using this form as a Dialog (more on this later) programmatically. In that case, leave the buttons' properties alone and insert the following code for both buttons:

Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        Me.DialogResult = DialogResult.Cancel
    End Sub

    Private Sub btnOK_Click(sender As Object, e As EventArgs) Handles bntOK.Click
        Me.DialogResult = DialogResult.OK
    End Sub

Dialogresult is a function that can be used to retrieve the result that the user choose when clicking a button on a form. This function comes into play when a form is not shown with the 'Show' command, but with 'ShowDialog'. Basically, the code sees which button the user chose on the form that was shown in the 'ShowDialog' command and returns this for use in the main code from which the showdialog form was used. The data on this showdialog form remains available for the main form until the showdialog form is disposed of. The user does not notice anything from this. After clicking a button on the showdialog form, this form dissappears and he goes back to the main form. The operation of a showdialog form is called a modal form; no other form can be accessed until the modal form is closed. It is an easy method of getting user input and passing this to another form. Let's proceed.

Select both buttons on the form and make sure that the 'Anchor' in the Properties window is set to 'Bottom, Right'.

The basemessagebox form needs something else. Insert the following code just below 'Public Class BaseMessageBox'

Protected fndrows As Object
    Protected cnt As Integer

This code is written in the basemessagebox because both the messagebox for Individuals and the messagebox for Companies will use these variables. Good coding practice requires code to be written only once if possible, so adhere to this principle as much as possible.

Let's move on to building the messagebox for Individuals. Add a new form to the project with the name 'MsgBoxAutocompleteNP'.

Make sure that the 'Size' in the Properties window is equal to the basemessagebox form. That avoids awkward positions of the buttons placed on the basemessagebox form.

Double click this form so you get to the code. Ignore the 'Load' procedure that has been created. You will fill out code later on in this procedure, but for now, insert the following code just below the line 'Public Class MsgBoxAutocompleteNP':

Inherits BaseMessageBox

This simple line ensures that the elements and code of the basemessagebox just created, will be part of the MsgBoxAutocompleteNP form. Visual Studio reminds you that you have to build the project first before the effects are shown. Build the project, close the program and go to the Design view of the MsgBoxAutocompleteNP form (double click this form in the Solution Explorer). Now, the buttons 'OK' and 'Cancel' from the basemessagebox are shown. You are reusing forms (and code)!

The messagebox needs a few extra items. In the Design view, add a listview item (name: 'lvParties') and two labels (a label 1 and a label with some text shown) (see picture).

In the picture above, the listview element shows columns. Add those columns by selecting the listview element on the MsgBoxAutocompeteNP form and clicking the small arrow on the right.

That arrow gives access to a menu. Fill in the listview item with the columns as shown and give them appropriate names (a name for Visual Studio and a name that the user sees (in the listview item)).

With the listview item selected, go to the Properties window and make sure that the 'FullRowSelect' property is set to 'True' and that the 'MultiSelect' property is set to 'False'.

Now for the coding part. First, add a public property to the messagebox form (go to the code editor for the MsgBoxAutocompleteNP form):

Public ReadOnly Property selecteditem As Integer
            Return lvParties.SelectedIndices(0)
        End Get
End Property

This property is used to return the number of the selected index in the listview. The property is set to read only because we do not have to set a value, only retrieve a value. Later on, you will write code to use this number in the main form's code to look up the relevant party details in the 'parties' collection in the main form, so that all textboxes on the main form can be completed. Perhaps this sounds a bit cryptic. Don't worry, you will see where this is heading to.

Next, insert the following code:

    Public Sub New(ByVal foundrows As Object, ByVal count As Integer)

        fndrows = foundrows
        cnt = count

    End Sub

This is a so-called 'constructor'. When this form is created ('initialised', like with the Show or Showdialog command) the constructor makes sure that certain elements of the form are set. In this case, the constructor requires you as coder to feed the command creating this form with two variables: foundrows and count. As you may remember, the variables fndrows and cnt were introduced in the basemessagebox form, so these two variables are already present in the MsgBoxAutocompleteNP form that inherits from this base form. The two variables supplied in the constructor are used to set the variables fndrows and cnt in the form. In the main form, you will insert these variables in the command to show the messagebox form. The 'foundrows' input will consist of the details of all parties that share the same name. More on that later.

Next, insert the following code that will run when the form is loaded.

    Private Sub MsgBoxAutocomplete_Load(sender As Object, e As EventArgs) Handles MyBase.Load


        Label1.Text = "Multiple instances of " & fndrows(0)("NPName").ToString & " found! "

        Dim i As Integer
        Dim lvi As ListViewItem
        For i = 0 To cnt

            lvi = New ListViewItem(fndrows(i)("NPName").ToString)
        Next i
    End Sub

Analysis of the code: the listview item on the form is cleared. This line is inserted to avoid certain behaviour (we'll get back to this shortly). The label on top of the form is set to display a text, indicating that a certain name was found multiple times in the data. The particular name is retrieve by selecting the data that is contained in the first row (0) of the fndrows object (a datatable) (retrieved by: 'fndrows(0)(“NPName”).ToString').

The next block of code is a 'For i = 0 to ….. Next i' loop. This loop fills the listview with the details of all parties found that have the same name. This enables your user to pick the right party by looking at other properties of the parties instead of the name alone (which name is equal for all parties shown).

This concludes the code on our form. Let's put that code in action by coding the main form.

Step 5

The only thing to make the autocomplete function work as intended is to add appropriate code for the main form (form1). Add the following code to the form1:

Private Sub txtNPName_KeyDown(sender As Object, e As KeyEventArgs) Handles txtNPName.KeyDown
        If e.KeyCode = 13 Then
            Dim dt As DataTable = TbIndividualsTableAdapter1.GetData

            Dim strValue As String
            Dim foundRow() As DataRow

            strValue = "NPName = '" & txtNPName.Text & "'"

            foundRow = dt.Select(strValue)

            If foundRow.Length > 0 Then

                If foundRow.Length = 1 Then

                    txtNPPlaceOfBirth.Text = foundRow(0)("NPPlaceOfBirth").ToString
                    txtNPAddress.Text = foundRow(0)("NPAddress").ToString
                    txtNPCity.Text = foundRow(0)("NPCity").ToString
                    txtNPCountry.Text = foundRow(0)("NPCountry").ToString

                ElseIf foundRow.Length > 1 Then

                    Dim count As Integer = foundRow.Length - 1
                    Dim frmMsgBox As New MsgBoxAutocompleteNP(foundRow, count)

                    Dim res As DialogResult = frmMsgBox.ShowDialog()

                    If res = Windows.Forms.DialogResult.OK Then
                            Dim i As Object = frmMsgBox.selecteditem

                            txtNPName.Text = foundRow(i)("NPName").ToString
                            txtNPPlaceOfBirth.Text = foundRow(i)("NPPlaceOfBirth").ToString
                            txtNPAddress.Text = foundRow(i)("NPAddress").ToString
                            txtNPCity.Text = foundRow(i)("NPCity").ToString
                            txtNPCountry.Text = foundRow(i)("NPCountry").ToString


                        Catch ex As Exception
                            MsgBox("Nothing selected!")
                            GoTo Line1
                        End Try

                        Exit Sub

                    ElseIf res = Windows.Forms.DialogResult.Cancel Then
                        Exit Sub

                    End If

                End If

        End If

        End If

    End Sub

Analysis of the code:

1. the code runs upon a 'keydown' event. That basically means that the code is run when the user selects an item from the dropdown list supplied by the autocomplete function. It is important to add the code 'If e.KeyCode = 13 Then' because that ties the code running to this specific event only.

2. When the code runs, the 'dt' variable is loaded with all data from the data base (for Individuals). Notice the 'GetData' command of the table adapter that retrieves all this data.

3. The next few lines serve to lookup all rows in the datatable 'dt' that have a name in it that matches the name the user clicked from the list shown by autocomplete (basically, the name that is now in the appropriate textbox on the form). The code checks whether the result is not 0 (otherwise, this could lead to a program error). Second, the code checks whether the result is more than 1, because the messagebox routine you built only makes sense if there is more than one party with the same name. The program basically decides whether the result of the query is 1 (if so, it proceeds with the normal code) or more than 1 (if so, it will go through the messagebox routine).

3. If the result of the query returns only one row, the details of that party (the data in the row found in the datatable 'dt') are inserted in the appropriate textboxes.

4. If the result of the query returns multiple rows (meaning, more parties that have the same name), code is set up to show the messagebox created, take the user's input and use that input to get the details of the chosen parties into the textboxes on the main form.

5. The code needs a 'count' variable for use with the index of the rows found (the 'foundRow' variable contains the rows that were returned by the earlier query). The count is set to the number of rows found but minus 1 because the index starts at 0 instead of 1.

6. Please note that the MsgBoxAutocompleteNP is first initiated ('Dim frmMsgBox As New ….'), taking in two variables (the foundRow variable and the count variable). Only then, the messagebox is show through the ShowDialog command. The command to show(dialog) the messagebox is construed to return a result 'res'. This result (that - in the messagebox form - can be 'OK' or 'Cancel') is used again in the main form to run particular code.

7. If the result from the messagebox is 'OK', the code on the main form will 'Try' to get the number of the selected item on the messagebox's listview (lvParties). If that does not return an error, the code simply uses that number to retrieve from the 'foundRow' variable the details of that particular party and use it to populate the appropriate textboxes on the main form. When that's done, the messagebox form is disposed of.

8. If the code returns an error, the user has probably not selected a party in the messagebox. The code solves this by showing a (separate) messagebox to the user that he hasn't selected anything and subsequently jumping back to the code that show(dialog)s the messagebox. This 'GoTo' method is not the preferred method of coding, but in this case it is a suitable solution.

9. Lastly, if the result from the messagebox is 'Cancel', the messagebox form is disposed of.

This complex code scattered over multiple forms creates the logic that makes the autocomplete function work. A lot of work but very satisfactory for your user. He can insert loads of data through the lookup form created in an earlier tutorial and subsequently retrieve that info in a jiffy using the autocomplete feature.

Create the same code and forms for the Companies tabpage on the main form (the autocomplete feature of the txtRPName textbox including a messagebox that inherits the base messagebox). Remember that you will need to add the TbCompaniesTableAdapter to the main form just like the TbIndividualsTableAdapter and that the columns in the database for Companies are RPName, RPSeat, RPAddress, RPCity and RPCountry. Adjust the code accordingly and create two variants of the UpdateDataTable() function (one for Individuals and one for Companies). Remember that the original UpdateDataTable function (to be renamed to UpdateDataTableNP) is used in several locations so update accordingly and insert the new UpdateDataTableRP sub at the appropriate spots (similar to this function for Individuals). An excellent opportunity to revisit the above mentioned parts of this tutorial.


You enhanced the existing code considerably with a datagrid view on the lookup forms, ordered alphabetically by name, and an autocomplete feature in the main form. You now have everything you need to build a feature rich application to automate Word documents.

The next tutorials will focus more on getting details of working with Word documents right, like switching from singular to plural in a Word document by code. Those tutorials will have the form of separate Visual Studio projects instead of one large project that gets built further with each tutorial.

Computing A-Z | Programming | Software

QR Code
QR Code programming_microsoft_word_-_11_-_enhancing_the_user_experience_autocomplete (generated for current page)

Advertise with Anonymous Ads