Access 2007 Tutorial 5.4. Inserting into multiple fields using a Combo box and NotInList Event

When I teach my Access course, I often get students who feel that entering information
into the database should be easier than it is, even with forms. Well, we can make it
easier, but it usually requires some programming to do some work on behalf of the user. The
more work we do, the less work the user has to do.
Any kind of programming is very specific and requires a close attention to detail, such
as making sure that characters are the correct case. This Access database and the completed
code are available on my website, if you wish to simply copy and paste the code and try
it out.
In my Access course, students create a number of forms with combo boxes populated by the
relationships of the data from different tables. Once they have created the form, they can
add a new movie record. However, when they go to insert the director, if the director
doesn't already exist in the director table, they get a message stating "The Text you entered
isn't an item on the list".
To add a new director, we have to delete the director name from the combobox, then open
the Director form to create the new director, and then return back to our Movie, adding
that director to this movie.
Life would be much easier if we could be asked if we want to create a new record, instead
of having to retype this information and create that new record ourselves.
First, we will open our form in Design view, and select and right click on the Director
Combo Box. We can use the Build Event option or we can open the Property Sheet and click
on the Events tab, where we can see all the different events that we can use to trigger
our code. When we click on the ellipsis for the Not In List event, we get a choice between
using the Macro Builder, Expression Builder, or Code Builder. The Code Builder gives us
the most complexity and flexibility with the least amount of hand holding, and since we
want to actually write some code, that is what we will pick.
What opens is the Visual Basic Development Environment. Basically this is a text editor
with a bunch of nifty features that help us write code, such as autocomplete suggestions
and debugging tools. The code builder automatically created a little stub for the code we are
going to write, so we are going to write our code in between these supplied lines.
The word Sub indicates that this is a subroutine. The next bit is the name of the subroutine.
It contains the name of the Combo Box object Director_ID and the name of the event. After
the Subroutine name, we have the arguments, NewData and Response. These are the pieces
of information that will be passed to the subroutine and what kind of data types they
are. The second line indicates the end of the subroutine.
Just to make sure that we picked the right event, we will write a simple line that will
print a message to the screen. We are going to type message box, spelled msgbox, and then
in parenthesis, a pair of double quote marks, and in those quote marks, the word "test".
We will save our code and then return to Access, go to form view, and test our combo box. When
we type in a new director name, we got our message box, but then we still get the same
error message, but if we select a director that already exists, it works as it did before,
so this tells us that this is the event that we want.
Using the ALT and TAB keys on the keyboard, I am going to go back to the Visual Basic
editor.
The first thing we need to do is offer the user a choice as to whether they actually
want to create a new record. If the user suspects that this director is already in the database,
perhaps they simply misspelled the director's name and don't wish to insert a record yet.
We will use the message box function to provide this choice, changing the message from "test"
to "Do you want to insert this data into the Director table?". We will also add two more
arguments, the first being the word vbYesNo, which will cause the user to be prompted for
an answer, and another message, "Director not found!", which will appear in the titlebar
of the pop up window.
When we save our updated code, our current statement turns into a red line. This is because
we need to have a variable catch the result of the user clicking on Yes or No. So, before
our message box function, let's type "Answer =". Answer will be our variable that we will
use to hold the result of the question. Variables hold small pieces of information and they
can be changed as the program executes. vbYesNo on the other hand is a constant. The value
of a constant cannot change and vbYesNo is one of the many built-in constants, which
in this case, tells Message Box that we want a yes/no prompt as opposed to just a simple
box with an OK button.
Next we want an if statement that will cause one set of statements to execute if the user
selects yes, that they do want to insert a new record into the director table. We will
type the statement if Answer = vbYes then in the next line. Answer = vbYes is the condition.
If this condition is true, then all of the statements after the then will execute. If
the condition is false, the statements will be ignored. We can also add additional an
statement called an Else that instead executes a different set of statements if the condition
is false. Let's test our If-Else statements by inserting simple message box test statements,
one saying "Yes" before the Else and one saying "No" after the Else.
We obviously don't need to tell the user "No" when they clicked on No, but we should suppress
the "Not in list" message, as the user has decided not to add that name. We can suppress
the message by setting the Response variable to the constant acDataErrContinue. We want
to make sure we type this constant name exactly with the proper characters in uppercase.
Let's add the name of our new Director to our "Yes" message box. We will do this using
the NewData variable that was created from the arguments when our subroutine was called.
This NewData variable contains whatever was typed into our combo box. We can also add
the NewData variable to our yes/no prompt. When we include the NewData variable in our
prompt, we need to close the double quote before and after where we wish to place the
variable, along with "&" signs before and after the variable.
So now we know that works, let's try to tease out the first name and last name as separate
pieces of information from the NewData variable that contains the text typed into the combo
box. There is more than one way we can do this, but we can tease this information apart
easily using a very special kind of variable called an Array. Whereas a normal variable
just contains a single piece of information, arrays can continue many pieces of information.
We are going to call our array NameArray and use the split() function to split NewData
into pieces, with each piece delimited by the comma and a space. We can then access
only the piece we want, such as just the first name or the last name.
We will assign the output of the split to NameArray, so we will type that in first.
Then we will use an equal sign to assign a value. Then we will use the split function
with two arguments, so we will type in Split, left parenthesis, and inside the parenthesis,
NewData , ", " and then close the parenthesis. We want both a comma and a space in the double
quotes, because this was what appended between the last name and first name when we populated
the combo box.
When we split NewData into NameArray, we created a series of values in our array. Our array
will number each value, and we can use these numbers, called indexes, to retrieve each
value. In this case, our array indexes start at 0, so the Last Name will be at index 0
in the array and the First Name will be at index 1.
Let's assign the last name and first name to some variables. We will type in FirstName
= NameArray (1 ), and then repeat the process for Lastname, but instead of 1, using index
0. Now, let's use some simple MsgBox's to print out the lastname and firstname and make
sure everything works.
Now that we have extracted the first name and last name, we need to add it to our Director
table. This is going to require creating some SQL code. We could go into Query Design and
create a query that does what we want, appending or inserting a name into the Director table,
and then paste the generated SQL code into our script, but we are going to type in the
code and then test it in Query Design instead. First we need to assign our SQL statement
to a variable. Let's call this new variable SQLStmt.
SQL code approximates English, but it has a very specific grammar. We start with the
command, Insert, and specify into which table and fields we wish to insert the data. Then
we specify the values in the same order as the fields. Insert into Director(DirectorLastName,
DirectorFirstName) values ('Corman', 'Roger')
Let's copy this bit of SQL code and paste it into the SQL view of a blank query. I'll
do this by clicking on Create, Query Design, and then select SQL view. When I run my query
I found out that I had a typo. So I'll fix the typo, and we can see that it actually
works.
Now here is something weird that Access likes to do. If I return to design view in my query,
we can see how we would use the Query By Example grid to write this kind of query but, when
we return to SQL view, we can see that Access rewrote my query as a totally different kind
of statement. I'll paste my original statement below this so you can see the difference.
So just because Access will write SQL code for you, it doesn't mean that it is necessarily
good SQL code.
We want to insert our Lastname and Firstname variables into our SQL statement, so let's
do that, closing the quotes, using the & signs, inserting our variables, and reopening the
quotes. We can also tell Visual Basic when a statement continues on to another line,
using the underscore character. We will do this also with the other longer line, just
to make our code more readable.
Now we should see a SQL statement printed to the screen with the director we want to
add inserted into the statement.
Next, we will remove our diagnostic message boxes and add a statement that says: DoCmd.RunSQL
SQLStmt
This will take our SQLStmt variable and execute it against the database. However, we will
find that we still get a Not In List message even though, if we check the Director table,
we did add our new director. This is because we need to tell access that we added data
to this table and to reload the list of values in the Combo Box. We do this with the statement:
Response = acDataErrAdded
So, it works, and it looks like we are done, right? Well, not quite.
Let's say someone types in a director who only has a single name, like Madonna. This
is going to cause problems because we are splitting NewData on the comma, but as NewData
doesn't contain a comma, our NameArray array will only contain a single value. When we
reference the second value at index 1, we get an error message.
This is easily resolved by another if statement that detects how many values are stored in
the array. We can use the UBound function, short for Upper Bound, to find the largest
index in the array. If UBound(NameArray) = 1, we have one comma in NewData, so this is where
we will put our SQL statement.
If UBound(NameArray) = 0, then we are missing a comma in NewData, but we can append a comma
and space to NewData, and reassign the array to the split results. Because our if statement
that is doing all the work is after our if statement that is fixing our array, we simply
fix the array and insert the data in the table without pestering the user.
Another potential issue is if the user types in a director name with multiple commas, say
for example, Downey, Robert, Jr. with commas between each part of the name. We can check
for two or more commas by checking if UBound(NameArray) > 1. If this is true, then we will alert the
user with a message box warning the user to only use one comma. We will also want to set
Response = acDataErrContinue along with this warning, so that the user can change the Combo
Box contents without getting the Not In List message.
Once we have made sure our code works and we are happy with it, we can copy and paste
it into the Not in List event for the Actor combo box, as long as we change any references
to the word director to actor, so the proper table is being updated.
There are still some a couple more remaining issues.
When we close the database and reopen it, we find that none of our code still works.
This is because this kind of programming code has been used to create viruses, such as those
that spread through Microsoft Word or Outlook. So, in order for our code to work, we need
to Enable Active Content when we open our database.
Another issue is that our code only inserts new records. It does not update or fix old
records, so if we make a mistake, such as confusing Toshiro Mifune's last name with
his first name, we would still need to go to a specific form to fix our error.
Private Sub Director_ID_NotInList(NewData As String, Response As Integer)
Answer = MsgBox("Do you want to insert " & NewData & _
" into the director table?", vbYesNo, "Director not found!")
If Answer = vbYes Then NameArray = Split(NewData, ", ")
If UBound(NameArray) = 0 Then NameArray = Split(NewData & ", ", ", ")
End If If UBound(NameArray) = 1 Then
FirstName = NameArray(1) LastName = NameArray(0)
SQLStmt = "Insert into director(directorlastname, directorfirstname) " & _
" values ('" & LastName & "', '" & FirstName & "')"
DoCmd.RunSQL SQLStmt Response = acDataErrAdded
End If If UBound(NameArray) > 1 Then
MsgBox ("Please include only a single comma and space combination.")
Response = acDataErrContinue End If
Else Response = acDataErrContinue
End If End Sub