The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
July 2006

How do you process an ADD NEW in your Combo Box?

During the past several months I've covered a variety of topics related to combo boxes. One of the more complex was how to get an Add New to show up at the top of a drop down list, and I described several ways to do this. One issue remains, however: how do you detect when the user has selected "Add New," and once it's selected, how do you respond?

In this issue
  • Tip of the Month – Copy a Form From Another Application
  • The Foundation
  • VBA Code Generation
  • Analyzing the Code
  • Conclusion and a look ahead
  • Trap of the Month – Beware of Embedded Apostrophes in SQL statements

  • The Foundation

    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.

    VBA Code Generation

    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
           strSQL = "Insert into tlkpCities(City)
           values ('" & strCity & "')"

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

        End If
    End If

    End Sub

    Analyzing the Code

    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

    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.

    Conclusion and a look ahead

    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.

    Trap of the Month – Beware of Embedded Apostrophes in 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:

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

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

    Tip of the Month – Copy a Form From Another Application

    If you have a form in one Access Application that you would like to have in another application, there's no need to recreate the wheel. The efficient move would be to copy the form to the new location where you desire it.

    There are several ways you can do this. The most straightforward is to open the application that has the form you want to copy. Right click on the form, and choose Copy. Open the application that you want to place the copied form in. Click on the forms tab and then right click in any white space where the form names are. From the pop-up menu choose Paste.

    Access will ask you for the name of your form; type in whatever name you'd like and presto, a new copy of your form is now available.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz

    Custom Software | © Copyright Custom Software All Rights Reserved | Westford | MA | 01886