You can work along with this file:
In this video:
- On Not In List event
- Variables & Constants
- if-else statement
- Remaining Issues
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