Let's say that you are building a form that is designed to track orders from customers. Further, let's suppose that your business has 5,000 customers.

Typically when you build a form, your goal is to make life as easy and efficient as possible. If your form is designed to show details about a customer, then you might be tempted to have a combo box that lists all the customers so that the user just starts typing the customer name and once found, the form populates with information about the customer.

This approach actually works; unfortunately it's a slow process. There are a couple of problems, neither of which is immediately apparent. The first is that if the form is tied into underlying records about each customer, then the form has to load all of those records before being able to react to the user. This is a separate problem that we will cover in a future Wizard.

A more subtle problem is associated with the combo box. Most likely, the combo box is attached to a query (or SQL statement) that returns every customer in the database. With this approach, you just start typing and, once you see the customer name, you click on it or just hit return if the typing arrives there by itself. The problem is that the combo box has to retrieve 5000 records before the form is ready for the user to do anything.

A much better approach is to use a bit of code that limits the combo box to just a handful of records.

This can be accomplished by the addition of one text box and just a bit of code. Let's say that your original query behind the combo box is "Select CustomerKey, CustomerName from tblCustomer ORDER BY CustomerName".

The text box should be labeled something on the order of "Please enter the first few letters of the customer's name." Then have an after-update event for the text box (I'll call it "txtFindCustomer". The code you'll want is as follows:

Private Sub txtFind_AfterUpdate()
    Dim strFind As String
    strFind = Nz(Me.txtFind, "")
    If Len(strFind) = 0 Then Exit Sub
    Me.cboCustomer.RowSource = "Select CustomerKey, CustomerName from " & _
    " tblCustomer where CustomerName like '" & strFind & "*' " & _
    " order by CustomerName"
    Me.cboCustomer.SetFocus
    Me.cboCustomer.Dropdown
End Sub

Let's take the code line by line.

    Dim strFind As String

This line sets up a variable to hold onto the text that the user puts into our find box.

    strFind = Nz(Me.txtFind, "")

This line loads the user entry into our variable. Note that it has two features. First it is using the Nz function. This function handles the case when there is nothing there. Since we have said that our variable is a string, if the user has wiped out the previous entry and hit return, the search box will contain a null value. The Nz function will replace that value with the string "" (essentially a string with nothing in it). You could also accomplish exactly the same thing with:

    strFind = strFind & ""

It's strictly a matter of style.

    If Len(strFind) = 0 Then Exit Sub

This line essentially says that if the length of the string to find = 0, essentially "", then exit the routine. Since the user has entered a blank, we don't want to continue.

    strFind = Replace(strFind, "'", "''")

This solves a tricky and complicated problem of apostrophes in text manipulation. See this month's trap for a full explanation of why this is necessary.

The next line is where all the action takes place.

    Me.cboCustomer.RowSource = "Select CustomerKey, CustomerName from " & _
    " tblCustomer where CustomerName like '" & strFind & "*'" & _
" order by CustomerName"

The customer combo box has a row source, in our case it was a SQL statement. What we are doing here is replacing whatever is there with a new SQL statement. The beginning and ending are the same as the original:

CustomerKey, CustomerName from
And
order by CustomerName

It's the middle part, the where clause, which is the bit of magic:

where CustomerName like '" & strFind & "*'"

What we've done is taken the input from our user and put into our SQL statement. The word like is used with the asterisk to create a wild card search.

If our user had entered Mc into our search box it would bring back all records starting with "Mc".

The last two lines work in tandem:

    Me.cboCustomer.SetFocus
    Me.cboCustomer.Dropdown

The set focus is necessary in order to do our dropdown. The dropdown statement opens up the dropdown box to show a number of records that meet the criteria (typically 8, unless you've changed it). The beauty of doing it this way is that the user gets immediate feedback and figures out how things work with very little effort or training.

Once you are satisfied that the find box and combo box are working the way you want them to, the next step is to remove any existing SQL statement in the Combo Box. The purpose here is to speed up form loading. Rather than retrieving 5000 records from the customer table when the form opens, it returns 0 records. It will only return records when the user has indicated what they are looking for.

Using this approach, a filter for a combo box, rather than a combo box containing all records has two major benefits:

1. Your form will load faster.
2. Your user will have a much easier time finding one of a few records rather than one of 5,000.

As a final benefit, your user is not limited by how many characters they enter into the search box. This way, they can refine their search so that they get what they want with reduced effort on their side. Wins all around!

When manipulating text, as we did in this month's Wizard, you have to be aware of the dreaded apostrophe trap. This comes up frequently when you go after names like O'Leary, O'Malley, or O'Generic Irish name.

The problem is that when VBA is manipulating text that is enclosed in double quotes, as soon as it hits an apostrophe, it interprets the single quote as if it were a double quote and terminates the string. When your VBA gets to the terminating double quote, it doesn't know what to do and throws up an "I'm unhappy" error message.

The way to resolve this problem is to just double any apostrophes. You do that with a simple replace statement. For instance, this month we were manipulating a variable called strFind, and we had a line of code:

strFind = Replace(strFind, "'", "''")

With this statement, the code engine will turn any existing single quotes within our variable, strFind, into a single quote within the manipulated string and things will work the way they should.

As a general rule, it is always a good idea to test any combo box or search box with a name or phrase that contains an apostrophe to make sure it can handle things gracefully.