Let's start with the combo box that we've been using showing cities in Massachusetts:

As you can see, the first choice in the list is the option to Add New (which we added to the list via a union query and then forced to the top) – see March 06 for details. Note that for the purpose of this technique we're using a lookup table of cites: tlkpCities.

Know that I've simplified things by using a sorting character to bring "Add New" to the top and that in this example I'm not using a key field, but rather just the name of the city.

Consider for a moment that this combo box sits on a form that collects addresses. If the user chooses a city, then that city will be stored in the address table; however if the user chooses "Add New," then we want to capture the name of the new city and store that name in our lookup table. How do we make this process as easy as possible for our user?

There are several ways, but the easiest is to use some simple Visual Basic for Applications (VBA) code. This will allow us to dive into the design of the combo box and react to the event of a user making a specific selection (Add New, in this case). We’re going “under the hood”, but don’t be intimidated; even if you've never worked with VBA code, this will be an easy example to follow.

To create the code, do the following:

1. Open the form in design view
2. Right click on the combo box
3. Choose Properties
4. Then click on the Event Tab

At that point you'll have the following in front of you

This shows all the events (things that could happen) of the combo box. These events are things that could be processed (or reacted to) with VBA code. The one we’re interested in is the Before Update event - the event that occurs just before the data in the form is updated with the Combo Box value.

To begin creating the code, click on the button with three dots (ellipsis for you English Majors) to the right of the Before Update space.

When you do that you'll be presented with following options:

Choose "Code Builder" (we'll cover the other options at a future time) and then click OK. You'll go to the VBA window where you'll see the following code:

Private Sub cboCity_AfterUpdate()

End Sub

This is the shell (or the required first and last lines) of VBA code that will dictate what will happen after the user selects "Add New" from our combo box.

We’ll place the following code between the first and last lines (bolded for emphasis) to dictate what should happen if the user selects Add New:

Private Sub cboCity_AfterUpdate()

Dim strCity As String
Dim strSQL As String
If Me.cboCity = "(Add New)" Then
   strCity = InputBox ("Please enter the 
         new city name.")

     If Len(strCity) = 0 Then
       'They have hit cancel so we do nothing
        Else
       strSQL = "Insert into tlkpCities(City)
       values ('" & strCity & "')"

        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True

        Me.cboCity.Requery
    End If
End If
End Sub

Let's go through it line by line. Our first order of business is to create and define some variables to allow us to respond to what the user has entered. To see the basics of generating code and the use of variables, refer back to the December 2005 Wizard. So we start with:

Dim strCity As String
Dim strSQL As String

You'll see how these variables are used as we move through the code. Note that I'm using a naming convention here as well as throughout the rest of the code; to see more about why, check the July 2004 issue – Naming Conventions.

The next chunk of code contains two “If” statements matched with a required “End If” statements. In this example, you’ll see one If/End If set nested within another If/End If set. Our first actual line of code line is:

If Me.cboCity = "(Add New)" Then

This line, starting with an If statement, matches the last End If statement in this section of code. It lays out the action between here and the closing End If only in the case that the user has selected the Add New value from the combo box. If the user hasn't selected Add New, the rest of the code in this section is skipped over so basically nothing happens. The user continues on their way, unaware that this code exists behind the scenes.

Let's take a look at our next line, which is executed if the user has selected Add New:

strCity = InputBox("Please enter the new city name.")

This code presents our user with an Input Box asking them to tell us the name of the new city, and we're putting their entry into a variable called strCity.

Our next few lines say:

If Len(strCity) = 0 Then
'They have hit cancel so we do nothing
Else

This code executes if the user either enters nothing into the input box (in which case the length of the text string would be 0) or hits the cancel button. In either case, the code will execute between the If statement and the Else statement. Since the only line of code between those two lines is a comment (comments are preceded with an apostrophe), nothing happens.

The next line is the most important of the code because it sets us up to insert a value into our lookup table:

strSQL = "Insert into tlkpCities(City) values ('" & strCity & "')"

This is an SQL statement (the code behind queries) that says “take whatever they have put into the input box and insert it into the City lookup table”. SQL is a complex language that runs against Access databases as well as most databases in use today. It's a complex subject – for an introduction to SQL code see the June 2005 issue – What's Really Happening with Queries.

The SQL above is fairly simple and you can use it to put a single value into a table. "Insert into tlkpCities" says I want to put something into the table called tlkpCities. The City in parentheses indicates that the field that I want to add is City. If you were adding more than a single field to a table you would include them one after the other separated by commas. This is followed by the word "values” and a string of text between parentheses. This text indicates to Access what you want to put into the field Cities. Within the parentheses, you’ll see a single quote followed immediately with a double quote. This is the only truly tricky bit. Because the field City is a text field (rather than a date or number field), SQL requires that it be in quotes. (Note: if the value was a number or a date, the single quotes would not be required). Then we have an ampersand, a concatenation device that allows adding things together in our SQL statement, followed by stCity.

Remember up above when we said : strCity = InputBox("Please enter the new city name.") Well here we're taking whatever it is that they told us (our variable strCity) and adding it to the SQL statement. This is followed by an ampersand and then a double quote, a single quote, a closing parenthesis and then a double quote to match the double quote just after the equal sign.

This little complex bit is continuation of the string (the ampersand followed by the double quote) a terminating apostrophe for the name of our city, a closing parenthesis for our SQL string and finally a closing double quote.

This process to add new values to the table gets fussy along the way so you may have to spend a bit of time debugging. There may be shortcuts; see this month's tip for an alternate approach.

The next three lines cause Access to take action

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

The first line turns off the warnings so that the user is not bothered with dialog boxes from the system.

The DoCmd.RunSQL strSQL says “take the SQL statement and actually run it”. This is the line that actually populates the table with the new value.

The third line is housekeeping. In normal circumstances, if Access has a warning for the user we want the user to know about it so we make sure to turn warning capabilities back on.

From there the code closes with two End If statements that balance out the earlier two If statements.

This month we used VBA code and a SQL statement to add a new value to a simple lookup table. The process may look a bit complex, but if you break it down step by step it becomes much easier.

What happens though when we want to allow the user to add a new value to a table that has many fields, rather than just one – both a city and a Zip code for example? This is a common occurrence and we'll tackle it in a future issue.

We'll also be looking at a completely different way of adding data to a table with code. In a future issue we'll explore how to do what we did here do using DAO record sets rather than SQL statements.

When adding values to a table using a SQL statement (as described above), be aware that problems may be caused by embedded apostrophes in a string. City names typically do not have apostrophes in them, so it's usually not a problem and using the SQL approach works fine. However, apostrophes may be a more a common occurrence in fields like last name. For example, if you're adding names to a table, before too long you'll hit an O'Leary or an O'Toole.

There are at least two ways around this problem. You can use a technique other than SQL to manipulate the data (I'll be covering that in an upcoming issue), or compensate for the apostrophe by doubling it up in your VBA code.

For example let's say that you want to add a value that might have an apostrophe to a table. To do this, add a line after you've filled your variable with the name. For example:

Follow
strLastName = me.cboLastName
with
replace (strLastName, "'","''")

This replace function will double up the single apostrophe and allow the SQL to continue unimpeded.