Join Curt Frye for an in-depth discussion in this video Performing a task when conditions are met using an If...Then statement, part of Access: VBA.
So far, most of this course's example files have contained subroutines or functions that run all of their code every time you call them. There will be times, though, when you want parts of your subroutines to run when a condition is true or false. In business, the canonical example is calculating sales commissions. Many companies have separate commission rates for different sales totals, so I'll use that example as a context for how you can use If-Then statements to execute code conditionally. For this movie we will use the IfThenCode code module. And that's here at the bottom of the Navigation pane, so I'll double-click it to open it.
In this code module I have a single subroutine, and in it I've defined two variables: Total and Commission. And then I get the total value from an input box asking for the amount of the sale. At the end of the subroutine there is a message box that will display the value of the commission. The simplest form of an If-Then statement is a one line If-Then. So for that I'll click above the MsgBox statement and create an If-Then statement that checks to see if the value is greater than or equal to 1000.
So I will type If curTotal, which again is the amount of the sale, >=1000. Then curCom, the commission, = curTotal * .03, and press the down arrow. So in other words, if the total is greater than or equal to 1000, you get a 3% commission, and that will appear in the message box.
So I'll press F5 and run the code once to show what happens when the condition is true, and again to show what happens when the condition is false. So I'll press F5, and the amount of the sale, let's make it 2000, which is above 1000, click OK, and the commission is $60. Now, if I press F5 to run the code again and enter the amount of the sale, and we'll make it 100, which is well below 1000, click OK, and Access displays a box that shows the commission is 0.
So that is the simplest way to create an If-Then statement. If you want to create an If-Then statement that makes multiple tests, then you can use the Else keyword. So for that, let's say that the total is greater than or equal to 1000. Then, and I'll press Enter, and then press Tab to move the code over. I pressed Enter twice to give myself a blank line. If the total is greater than or equal to 1000, then we'll make the commission 6%.
Press Enter. Then type "Else," then Return, and Tab again to move in. If it's below 1,000, we'll make the commission 5%. So that would be curCom = curTotal * 0.05. And then press Enter twice, Backspace twice to move back to the left margin, and then to close out the If statement by typing End If.
This code checks to see if the sale was a least $1000. If it is, you get a 6% commission. If not, you get 5%. So I'll press F5 to run the code, and I'm asked to enter the amount of the sale. I will make it 1000. So I should see a commission of 60. I do. Then I'll click OK. Now if I press F5 to run it again and type in 100, I should see a 5% commission, or $5. And when I click OK, that is indeed what appears.
You should be sure to put your Else statement on its own line and also to make sure that the first Then statement is on its own line as well. If you don't, Access treats those keywords as line labels, and it will generate all kinds of errors in your code. So just make sure that after you type Then you press Enter to put the line on a different code, and then when we have Else, make sure that's on its own line as well. If you want to make even more complex If-Then statements, you can use of the Else If command.
So let's say, for example, that you wanted to have three tiers of commissions: 8%, 6%, and 5%. For the first level, let's say that the sales total is greater than 10,000, and if that's the case, you want to give an 8% commission. So we'll edit those values so that now if the total sale is greater than or equal to 10,000, you get an 8% commission. Now we will edit the Else so it says Else If. And that is one term else, ElseIf, then a space. And now we can define the conditions, and that is if the total is greater than or equal to 1000, then the commission equals the total, curTotal * 0.06.
So in other words, if the total sales is greater than 10,000, 8% commission; if it's greater than or equal to 1000, then a 6% commission. Now press Enter twice. Now, you can type the final Else statement, so that's Else. And again, that needs to be on its own line, or Access treats it as a keyword. Then we can leave the curCom = curTotal * .05. So greater than 10,000, 8% commission; greater than or equal to 1000, 6%; anything else, 5%.
So let's check all three of those conditions. So I'll press F5 to run it. Now let's enter the amount of the sale, and I'll make it 20,000. So when I click OK, I should see a commission of 1600, and I do. Now, I'll press F5 again, and let's make it 1000, which meets the criteria for the next level, just barely. Say I have a $1000 sale. That should be a 6% commission, so click OK and I get 60. Then the final condition has a commission of 5%.
So if I press F5 and enter 100, I should see $5, and I do. If-Then statements let you execute code conditionally based on the result of the expression you set in the opening If statement. I found that a three-part test, such as the If-Then, Else If, Else, is about all I can handle before I start to get confused. If I want to execute code based on more than three possible conditions, then I usually create a case statement, which I cover in the next movie.
- Working with macros and managing macro security settings
- Creating, exporting, and deleting code modules
- Running a VBA procedure
- Debugging VBA code
- Defining variables, constants, and calculations
- Displaying a calculated result in a message box
- Repeating tasks with loops
- Creating conditional statements
- Manipulating database objects, such as forms, tables, and queries
- Adding, editing, and deleting records
- Looking up values in a table
- Requerying and repainting forms