The developers at Microsoft have put in thousands of man-hours developing functions that are expansive enough to fit the needs of most users. In most cases, the functions available in Access more than satisfy user requirements. In fact, many users will never use a majority of the functions available, and will typically gravitate towards only those that fit their current needs.

On the other end of the spectrum, there are those users whose daily operations involve tasks not covered by the functions in Access. These tasks can involve a business-specific calculation or a complex expression that achieves a particular result. In most cases, these tasks are accomplished by building expressions. For example, suppose that your analysis routinely calls for the last day of the current week. Because no built-in function exists to help you determine the last day of the current week, you would use the following expression wherever you need this data:

Date() - WeekDay(Date()) + 7

The alternative to using such an expression is to build a custom function (sometimes referred to as a user-defined function). Custom functions are VBA procedures that expose your expressions to other objects in your database as a function, much like Access's built-in functions. This essentially means that instead of creating and using expressions in a query or form, you build your expressions into a VBA procedure, and then call it whenever you need it. Why bother with custom functions? Well, consider the following inherent advantages to converting your expressions into custom functions.

Creating Your First Custom Function

For your first custom function, you will build a function that will return the last day of the current week.

1. Start a new module by clicking the Create tab on the ribbon and selecting Module as demonstrated in Figure 10-1.

   

   Figure 10.1. Start a new module.

2. Create a new function by entering the following code:

   Function LastDayThisWeek()

   Note

   There is nothing special about the name LastDayThisWeek. It's simply a descriptive name that coincides with the purpose of the function. When creating your own custom function, it's good practice to give your functions simple names that are descriptive and easy to remember.

3. On the next line, assign the needed expression to the function, giving your custom function its utility.

   LastDayThisWeek = Date - Weekday(Date) + 7

   At this point, your module should look similar to the one shown in Figure 10-2.

   

   Figure 10.2. You have created your first custom function.

4. Save the module and close it.

To test your newly created custom function, create the query you see in Figure 10-3 and run it. In this query, you first determine the last day of the current week by using your newly created function, and then you use that value to calculate how many days are left in the current week.

Figure 10.3. This query uses your newly created function to determine how many days are left in the current week.

TRICKS OF THE TRADE: CREATING A CENTRAL REPOSITORY OF CUSTOM FUNCTIONS

You don't have to create a separate module for each custom function in your database; you can create one module to hold them all. In the sample database that comes with this book, you will see a module called "My_Custom_Functions." If you open it, you will see the seven separate custom functions shown in Figure 10-4. These functions can be used separately in various analyses.

This method of storing your custom functions makes finding and editing your functions easy. Figure 10-5 illustrates another advantage of this method. When you activate the Expression Builder, you can drill into all the modules you have created in your database. Having one module that contains all your custom functions provides you a complete list of your functions.

Figure 10.4. Creating one module that holds all your custom functions allows you to quickly find and edit any of your user-defined functions.

Figure 10.5. Creating one module that holds all your custom functions allows you to quickly find and edit any of your user-defined functions.

Creating a Custom Function that Accepts Arguments

Sometimes the operation performed by your custom function requires arguments that cannot be supplied internally by Access. In these situations, you will need to create a custom function that accepts arguments. To illustrate this concept, look at the query in Figure 10-6.

In this query, the Revenue field is being annualized (that is, the revenue value of each row is being translated to an annual rate for comparative purposes). The nature of this operation requires three arguments: the value being annualized, the number of periods already completed, and the number periods that make up an entire year. As you can see in this query, the value being annualized is revenue, the number of periods completed is 8, and the number periods that make up a year is 12.

Figure 10.6. This query is using an expression that annualizes a revenue value.

In order to convert this expression to a custom function, you have to allow the user to pass the required arguments. Walk through the following steps:

1. Go into the Visual Basic Editor by pressing Ctrl+Alt on the keyboard. From there, start a new module by selecting Insert

   

2. Create and name your new function by entering the following code:

   Function Annualized()

3. Inside the parentheses, declare a variable and type for each argument that will be passed to the function.

   Function Annualized(MyValue As Long, _
   PeriodsCompleted As Integer, PeriodsinYear As Integer)

4. On the next line, assign the needed expression to the function, giving your custom function its utility. Instead of using hard-coded values, you will use the values passed to the declared variables.

   Annualized = MyValue / PeriodsCompleted * PeriodsinYear

At this point, your module should look similar to the one shown in Figure 10-7.

Figure 10.7. This custom function accepts three variables and uses them in an expression.

To test your newly created Annualized function, create the query you see in Figure 10-8, and then run it. Note that you are using your newly created function in an Alias called "AnlzdRev."

Figure 10.8. This query uses your newly created function to get the annualized revenue for each record.

Tip

You can hard-code selected arguments in your custom function to limit the number of arguments that need to be passed. For instance, the following code demonstrates how you can change the procedure for the Annualized function to hard-code the number of periods in a year:

Function Annualized(MyValue As Long, PeriodsCompleted As Integer)
Annualized = MyValue / PeriodsCompleted * 12
End Function

As you can see, the number of periods in a year has been hard-coded to 12, so when using this function, you have to pass only two arguments. For example:

Annualized([Revenue], 8)

A WORD ABOUT USING CUSTOM FUNCTIONS

Up to this point, you have tested your custom functions using queries. Although you will most commonly use your custom functions in queries, it is important to note that you can use them anywhere you would use any one of Access's built-in functions. Here are a few examples of how you can utilize your custom functions.

In a query environment, you can use your custom functions in the same ways you would use built-in Access functions. Figure 10-9 demonstrates some of the ways you can use a custom function in a query.

Figure 10.9. Using custom functions in a query

Figure 10-10 illustrates how in a form, you can tie the Control Source for a text box to one of your custom functions. This same method works in Access reports. In this example, this form will automatically execute the FirstDayLastMonth function each time it is opened to provide a value to the assigned text box.

Figure 10.10. Using a custom function in a form

Figure 10-11 illustrates how your custom functions can be used in other VBA procedures. This procedure uses the FirstDayLastMonth function to find the first day of last month and then puts that date into a message box.

Figure 10.11. Using a custom function in another VBA procedure

An Access form is nothing more than a database object that can accept user input and display data using a collection of controls. Access forms are often thought of as part of the presentation layer of a database, primarily being used as the front-end of an application. While it is true that the primary purpose of forms is to act as an interface between Access and a user, this does not mean the user cannot be you (the designer of the database). In this section, you learn how Access forms can be leveraged on the back-end of a database as a data analysis tool that interacts with your analyses and further automates your analytical processes.

The Basics of Passing Data from a Form to a Query

The idea behind passing data from a form to a query is that instead of using parameters in a query to collect the data for your analysis, you collect the data through a form. To get a firm understanding of the basics of passing parameters from a form to a query, perform the following steps:

1. Start by creating a new form. Go to the Create tab on the ribbon and click the Form Design button as demonstrated in Figure 10-12.

2. Go up to the Design tab and select the Text Box control as demonstrated in Figure 10-13; then click anywhere on your form. At this point, you should have a form with one text box control.

   

   Figure 10.12. Start a new form in Design view.

   

   Figure 10.13. Add a text box control to your form.

3. Right-click the text box and select Properties. Click the All tab, and then give the newly created text box a distinctive name by entering txtParam as the Name property, as shown in Figure 10-14.

   

   Figure 10.14. Give your text box control a distinctive name.

   Note

   Each control on your form must have a valid name in the Name property. The Name property is a unique identifier that allows Access to reference a control in other parts of your database. Access automatically assigns generic names to newly created controls. However, you should always make it a point to give each of your controls you own descriptive name. This makes referencing and recognizing your controls much easier.

4. Go back to the Design tab and select the Command Button control, as shown in Figure 10-15, and click anywhere on your form. This will place a command button on your form.

   Note

   If the Command Button Wizard activates, click Cancel to close it. You will not need that wizard for this exercise.

   

   Figure 10.15. Add a command button control to your form.

5. Right-click the newly created command button and select Properties. Click the All tab, and adjust the Name property of your command button to read btnRunQuery. Then adjust the Caption property to read Run Query.

6. Next, while still in the command button's properties, click the Event tab and then select [Event Procedure] from the On Click event, as shown in Figure 10-16. Next, click the ellipsis button (the button next to the dropdown).

   

   Figure 10.16. Set the On Click event to run an [Event Procedure], and then click the ellipsis button.

7. At this point, you should be inside the VBA editor, where you will enter a DoCmd action that will run the query called "Chapter10_ExampleA." Enter the following code, just as you see in Figure 10-17.

   DoCmd.OpenQuery "Chapter10_ExampleA", acViewNormal

The DoCmd.OpenQuery method enables you to execute any saved query from code. This method is perfect for simple automation processes such as this.

1. Once you are done, save your form as "frmMain" and close it.

   

   Figure 10.17. Use the Docmd.OpenQuery method to execute the "Chapter10_ExampleA" query.

2. It's time to test. Open the newly created frmMain form and click the Run Query button. If the query runs successfully, you have set up your form correctly. Now you can prepare your query to accept parameters from this form!

3. Open the "Chapter12_Example_A" query in Design view. Enter [Forms]![frmMain].[txtParam] as the criteria for the Period field, as shown in Figure 10-18.

   

   Figure 10.18. This query filters on the Period field based on the value of the txtParam text box in the frmMain form.

4. Save and close the query.

Now you can open the frmMain form and enter a parameter for your query through a form! Enter 200704 in the text box, as shown in Figure 10-19, and then run the query. This returns all revenues earned in the 200704system period.

Note

You will notice that if you leave the text box blank, your query will not return any results. This is the same issue you encounter using parameter queries. One way to get around this problem is to combine your expression with a wildcard so that if the text box is blank, all records will be returned. In this scenario, for instance, you would change your expression to read:

Like [Forms]![frmMain].[txtParam] & "*"

Figure 10.19. Now you can pass your parameters to your query through a form.

Tip

You can reference any form control that has a value property, including combo boxes, list boxes, text boxes, and option groups.

UNDERSTANDING THE SYNTAX FOR REFERENCING A FORM

Let's take a moment to analyze the syntax used for referencing a form.

• Brackets ([ ]): Brackets are used to identify the name of an object. For instance, if you were referring to the CustomerMaster table, you would refer to it as [CustomerMaster]. If you were referring to a query called TopTen_Step1, you would refer to it as [TopTen_Step1]. This not only helps Access identify objects, but it will also make your code easier to read.

• The collection operator (!): The collection operator (sometimes referred to as the bang operator) is used to tell Access that the object with which you are working belongs to a particular collection of objects. For example, if you are working with a form called "Main," you would refer to it as [Forms]![Main] because the form [Main] belongs to the Forms collection.

• The dot operator (.): The dot operator points to a property belonging to an object. For example, while [CustomerMaster] refers to the CustomerMaster table, [CustomerMaster].[City] refers to the City field in the CustomerMaster table. Here is another example. [Forms]![Main] refers to the form "Main", while [Forms]![Main].[Fname] refers to a control called Fname located in "Main."

Enhancing Automation with Forms

Access forms can help you enhance your automated processes using little more than a few controls and some light VBA coding. The idea is to turn your forms into something more than just a tool to pass parameters; you can create a robust central control point for your analysis.

To help illustrate the power of incorporating Access forms into your analysis, open the frmMktRpts form in the sample database, shown in Figure 10-20. The purpose of this form is to control the execution of an analysis that involves creating market reports. The idea is to select a market, run the process that executes a query, and then sends the results to an Excel file in the C:OffTheGrid directory.

Figure 10.20. This form enables you to control the execution of an analytical process.

Open the form in Design view to see how this works. As you can see, there are three controls on this form.

• The txtPath text box. The txtPath text box uses the market value from the combo box to construct a file path. This allows you to dynamically create a separate path for each market. This path is constructed by concatenating two strings and a control reference.

  • C:OffTheGrid. This is the first part of the file path, pointing to the OffTheGrid directory in the C drive.

  • [cboLocations]. This is the name of the combo box where you select your market. This becomes the file name.

  • .xls. This string finishes the path by assigning the file extension that identifies the file as an Excel file.

If you open the MktExports macro, shown in Figure 10-21, you will notice that the Output File path is referencing this text box. This allows the macro to avoid using a hard-coded file path.

Figure 10.21. You will use the txtPath text box to dynamically feed your macro the Output File path for each market.

• The cboLocations combo box. This combo box helps do two things. First, this combo box feeds the txtPath text box a market to use in the construction of a file path. Second, it feeds the MarketExports query its parameter. If you open the MarketExports query, shown in Figure 10-22, you will notice that filter criteria for the Market field is referencing this combo box. This allows the query to avoid using a hard-coded market.

  

  Figure 10.22. You are using the cboLocations combo box as the filter criteria for the Market field.

• The btnRunMarket command button. Right-click this command button and then click Build Event. This will take you to the VBA editor shown in Figure 10-23. As you can see, this button simply runs the MktExports macro, and then throws up a message box announcing the location of your new file.

  

  Figure 10.23. When you click the command button, a DoCmd action will run the macro and then call a message box.

Now that you have a firm grasp of how this form works, you can enhance it even further. Instead of running one market at a time, wouldn't it be useful to run all markets at once? You can do this by using VBA to enumerate through all the markets in the combo box, running the MktExports as you go.

Enumerating Through a Combo Box

Open the frmMktRpts form and take a look at the combo box on the form. The entries, or rows, you see within the combo box are indexed—that is, each row has an index number starting from 0 and continuing to however many rows there are. For example, the first row is index number 0, the second row is index number 1, the third row is index number 2, and so on. The idea behind enumerating through a combo box is to capture one index at a time, and then change the value of the combo box to match the row value assigned to that index number.

1. Start by opening the frmMktRpts form in Design view and adding a second command button.

2. Adjust the Name property of your newly created command button to read btnRunAll, and then adjust the Caption property to read Run All.

   At this point, your form should look similar to Figure 10-24.

   

   Figure 10.24. Add a second command button called Run All to the form.

3. Right-click the button and select Build Event. Select Code Builder from the Choose Builder dialog box, and then click OK. This opens the VBA Editor. As you can see in Figure 10-25, this creates a separate subprocedure.

   

   Figure 10.25. Build an On Click event for the newly created btnRunAll command button.

4. Start the code by declaring an integer variable called IndexNum. This traps the index number of each entry of the combo box.

   Dim IndexNum As Integer

5. Initiate a For...Next loop with the IndexNum variable. This line of code ensures that the procedure runs for each index number in the combo box.

   For IndexNum = 0 To Me.cboLocations.ListCount - 1

   Note

   Why subtract 1 from the combo box's list count? You must do this to adjust for the fact that index numbers of a combo box start at 0. If there are 10 rows in a combo box, the ListCount property starts counting at 1, returning a count of 10 rows. However, the index numbers in the same combo box range from 0 to 9. Subtracting 1 from the list count removes the extra number and corrects the discrepancy.

6. Set the value of the combo box equal to the value of the row assigned to the current index number. After the new value has been set, run the predefined macro.

   Me.cboLocations.Value = Me.cboLocations.ItemData(IndexNum)
   DoCmd.RunMacro "MktExports"

7. Repeat the process for the next index number. The message will alert you when the procedure has completed its execution.

   Next IndexNum
   MsgBox "Your files can be found in the C:OffTheGrid directory."

   If you have done everything correctly, your procedure should look similar to Figure 10-26.

8. Save your form and test the newly created functionality by clicking the Run All button.

   

   Figure 10.26. This procedure enumerates through a combo box, running a macro for each entry.

Once the procedure has completed running, look under the C:OffTheGrid directory to see all the Excel files that where created (see Figure 10-27).

Figure 10.27. All of these Excel files were created with automation.

Needless to say, this example is just one of the hundreds of ways you can enhance your analytical processes using forms. The flexibility and functionality you gain by using a few controls and a handful of code is simply incredible. Even simple techniques such as passing parameters from a form to a query can open the doors to a completely new set of analytical functionality.

One of the benefits of using VBA is that you can perform much of your data processing in the background without the use of queries and macros. This can be beneficial in several ways, including that it can:

Anyone who routinely works with Access knows that there are several different ways to accomplish any given task. Processing data using VBA is no different. Indeed, the beauty of VBA is that its flexibility allows you to perform literally any action in countless ways. That being said, it should be obvious that it's impossible to cover every possible way to process data using VBA. Therefore, you will focus on using RunSQL statements. This technique gives you some fundamental controls over your processes through VBA and allows you to move more of your analyses behind the scenes.

DoCmd.RunSQL(SQLStatement, UseTransaction)

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM [tblJobCodes] WHERE [Job_Code]='PPL1'"
DoCmd.SetWarnings True

MySQL = "SELECT Market, Customer_Name, EffDate, TransCount "
MySQL = MySQL & "INTO MyResults "
MySQL = MySQL & "FROM MyTable "
MySQL = MySQL & "WHERE Market='" & [Forms]![frmMain].[cboMarket] & "'"
DoCmd.RunSQL MySQL

MySQL = "SELECT Market, Customer_Name, EffDate, TransCount "
MySQL = MySQL & "INTO MyResults "
MySQL = MySQL & "FROM MyTable "
MySQL = MySQL & "WHERE TransCount =" & [Forms]![frmMain].[cboCount] & ""
DoCmd.RunSQL MySQL

MySQL = "SELECT Market, Customer_Name, EffDate, TransCount "
MySQL = MySQL & "INTO MyResults "
MySQL = MySQL & "FROM MyTable "
MySQL = MySQL & "WHERE EffDate =#" & [Forms]![frmMain].[cboMarket] & "#"
DoCmd.RunSQL MySQL

Leveraging VBA (Visual Basic for Applications) in your analytical processes can help you automate redundant and recurring analyses, as well as process data without the need to create and maintain queries and macros. Although there are countless ways you can leverage VBA to improve your analytical process, in this chapter you covered three techniques: building custom functions, incorporating Access Forms in your analysis, and using Run SQL commands to run queries behind the scenes.

Custom functions are VBA procedures that expose your expressions to other objects in your database as a function, much like Access's built-in functions. This essentially means that instead of creating and using expressions in a query or form, you build your expressions into a VBA procedure, and then call it whenever you need it. The major advantages to building your own custom functions using VBA is that you have the flexibility to perform complex multi-layered procedures that involve looping, record-level testing, and If...Then...Else logical evaluations while ensuring that your expression is performed the same way every time, without the risk of a typing mistake.

Another way to use VBA to enhance analysis is to incorporate Access forms into your analytical processes. An Access form is nothing more than a database object that can accept user input and display data using a collection of controls. Access forms are often thought of as part of the presentation layer of a database, primarily being used as the front-end of an application. While it is true that the primary purpose of forms is to act as an interface between Access and a user, this does not mean the user cannot be you. Access forms can be leveraged on the back-end of a database as a data analysis tool that interacts with your analyses and further automates your analytical processes.

Finally, you can create and run RunSQL commands using VBA to process data behind the scenes, without the need for query objects or temporary tables. The advantages of using RunSQL commands are: