Joseph C. Stockman

Cheat Sheet / Updated 03-27-2016

Access 2013 makes managing your data easy, combining a visual interface with the power of a relational database. Discover how to organize data into tables, design forms and reports for editing and presenting information, and create queries for selecting and combining information. The next steps are writing macros and Visual Basic for Applications (VBA) scripts to make your database smart, and using SharePoint to publish data to the web.

Article / Updated 03-26-2016

If you’re a user of Access 2000 through 2010, you may be familiar with Access Data Project (ADP) files — Access data files that provide access to SQL Server databases. Access 2013 drops support for the ADP format, and Microsoft recommends one of the following alternatives: Continue using the same version of Access. ADPs continue to work in earlier versions of Access. They won’t work, however, with newer versions of SQL Server such as SQL Server 2012 and SQL Server Azure. If you upgrade (or your organization upgrades) to versions of SQL Server newer than your current version of Access knows about, your ADPs will no longer function properly. Convert to an Access Custom Web App. In Access 2013, you can import your tables into a new Access app, and Access automatically creates forms for your application. You can extend the functionality of the base forms that Access creates for you so that other people can use your application on the web. Although some of the functionality that you use in ADPs may no longer be available, expect Microsoft to continue to focus improvements in the product on this application type. Convert to a linked desktop database. Access 2013 continues to support creating desktop databases in .accdb file format. You can convert your application to the .accdb format — including all your existing forms and reports — and leave the data in SQL Server. Then you can link to the SQL Server database by using linked tables, and your application will continue to operate. Create a hybrid application. If your application is large, and you don’t want to convert everything at the same time, you can import your data into an Access app and link to the SQL Server database from an .accdb file. This method allows you to migrate gradually, adding forms and functionality to your Access app over time app while maintaining your client application as an .accdb file with tables linked to the SQL Server database behind the Access app. Upgrade to the .NET Framework. Your application may be complex enough for you to consider moving to a more robust development platform, such as the .NET Framework. SQL Server is designed to make it easy for you to use the database infrastructure you’ve already created and extend the functionality of your application without having to significantly rewrite your code. When you’re working with data from SQL Server (or another data source), the data is coming from somewhere other than Access. After you establish the connection to the server and the database, you can build the rest of your Access objects — forms, reports, macros, and modules — to create a robust front end to an SQL Server database.

Article / Updated 03-26-2016

Want to make a brand new form in Access 2013? Here’s how — in less than ten steps — you can create and adjust your Access form to your liking: Create the form by using a wizard, adding an Application Part, or making a blank form. Decide among these options based on what you want your form to look like. You may want to run a few wizards and add a few Application Parts forms to see whether any of them looks like a good starting point for the form you want to create. You can always close the wizards without saving if they aren’t useful. Open the form in Layout view, and drag the fields around where you want them. Open the form in Design view by right-clicking its name in the Navigation Pane and choosing Design View from the contextual menu. If your form is already open in Layout view, click the bottom of the View button in the View group on the Design or Home tab of the Ribbon and then choose Design View from the drop-down menu. You see your form in Design view, as described in the next section. Make a change (add a control, change an existing control, turn the background purple, or whatever). Read on to find out how to make all kinds of specific changes. To see how your form looks with the change, switch to Form view by clicking the View button in the View group on the Design or Home tab of the Ribbon. When either the Home tab or the Design tab is selected on the Ribbon, the View button shows a tiny form; it defaults to Form view. Clicking the View button when it’s in tiny-form-mode tells Access to display your form in Form view — including a record from the table or query that the form is based on — so you can see whether you made the form better or worse. Switch back to Design view by clicking the bottom part of the View button and choosing Design View from the drop-down menu. When you’re in Form view, the View button defaults to Layout view, so you need to specifically choose Design view. Repeat Steps 4–6 until your form is gorgeous and works perfectly. Be smart: Press Ctrl+S every few minutes to save your work, even before you’re completely finished. Close the form’s window. It doesn’t matter whether you’re in Design view, Layout view, Form view, or Print Preview. If you haven’t saved the form recently, click the Yes button when Access asks whether you want to do so now.

Article / Updated 03-26-2016

Almost every Access user winds up with blank pages in a report; the blank pages appear in Print Preview. What causes these extra pages, anyway — and how do you get rid of them? Access knows the width of your paper and how much space to leave for the left and right margins because these sizes are specified in the report’s property sheet. Access adds the width of your report to the left and right margins to come up with the total width of the printed report. If the total is wider than your paper, Access splits the report into vertical bands and prints the left and right halves of the report on separate pieces of paper, so you can tape them together to create a very wide report. If the report is just a little bit too wide to fit across one piece of paper, all the text of the report is printed on the left half, leaving the right half blank. These blank right halves are the blank pages that Access prints. If the right part of the report has no controls in it, Access alerts you to this fact with this message: The section width is greater than the page width, and there are no items in the additional space, so some pages may be blank. To get rid of the blank pages, follow these steps: Click the File tab on the Ribbon, and choose Options-->Client Settings to display the Access Options dialog box. Scroll down to the Printing section. Subtract the left and right margin settings from the width of your paper to get the maximum width of the report. Standard U.S. paper is 8-1/2 inches wide. If the left and right margins are too wide, make them smaller in this dialog box and then use the new values in your calculation. If your paper is 8-1/2 inches wide, and you have half-inch left and right margins, your report can’t be more than 7-1/2 inches wide. You can change the margins if you want to use different defaults. Click OK to exit the Access Options dialog box. With the report open in Design view, note the report’s width — the location along the ruler of the right edge of the grid area. Alternatively, look at the Width property of the report in the property sheet. (Double-click the gray box in the top-left corner of the report in Design view, where the rulers meet, to display the property sheet for the form.) This property is on the Format tab of the property sheet. If the report is too wide to fit on the page, drag the right edge of the report leftward. If the edge won’t move, a control extends to the right of where you want the page to end. Move or shrink any control that extends too far to the right, and move the right edge of the report to the left. Alternatively, change the Width property of the report. If you can’t find the control that’s in the way, use your mouse to select the apparently empty area of the report grid. An orange border appears, showing what has been selected — frequently (in our experience) a horizontal line. Another possible reason for blank pages is an incorrect setting for the Force New Page property of one of the sections of the report.

Article / Updated 03-26-2016

If you want to filter multiple fields in Access 2013, the flexible Filter by Form feature is what you need. Although you can apply the filters to the various fields by using several different techniques, the Filter by Form feature lets you define all your filters for the table at the same time and then see the results. You can also select multiple criteria for a single field by using the Filter by Form feature. To filter by form, click the Advanced button in the Sort & Filter group on the Home tab of the Ribbon, and choose Filter by Form from the drop-down menu. Access displays a form that looks like a single row of the table you’re filtering. Use this form to specify the criteria you want to use to filter your data. Filtering by form in Access 2013. When you filter by form, you can use multiple criteria, and you also get to choose how the data filters through the criteria you set up. Do you want a record to meet all the criteria before it shows up onscreen, for example, or is meeting just one criterion enough to display the record in the filtered datasheet? Use the following two operators to tell your criteria how they should act together: And: The criteria act together hand in glove; a record has to pass all criteria to display in the filtered datasheet. Or: A record has to pass only one criterion to display in the filtered datasheet. You may use more than two criteria with the Or and And operators. The way that you put criteria in the form defines how multiple criteria act together. Use the Look For and Or tabs at the bottom of the form, as follows: Criteria on a single tab act as though they’re joined by the And operator. Criteria on separate tabs act as though they’re joined by the Or operator. To take advantage of all this versatility, follow these steps to filter a datasheet by form: Click the Advanced button in the Sort & Filter group on the Home tab of the Ribbon, and choose Filter by Form from the drop-down menu. Access displays the Filter by Form window, which looks like an empty datasheet. Move the cursor to a field for which you have a criterion. If you want to see only addresses in Pennsylvania, for example, move the cursor to the State field. A down arrow appears in the field. Click the down arrow to see the entries in the field. You may want to type the first letter or digit of your criterion to move to that point in the drop-down menu. Choose the value that you want the filtered records to match. Access displays the text that the filter is looking for inside quotation marks. If you aren’t seeking to match the entire field but are looking for a match in part of the field, type LIKE “*value that you’re looking for*” (including the quotation marks). You’d type LIKE “*new*” in the City field to find all records with new in the city name, for example. The asterisks are wildcards that stand for anything else that may appear in the cell. If you have a criterion for another field that needs to be applied at the same time as the criterion you set in Step 4, repeat Steps 2–4 for the additional field. Setting up criteria to work together illustrates the usefulness of the And operator. If you want to find addresses in San Francisco, set the State field to CA and the City field to San Francisco. If you have a completely different set of rules to filter records by, click the Or tab in the bottom-left section of the Filter by Form window. Access displays a blank Filter by Form tab. When you set criteria on more than one tab, a record has to meet all the criteria on only one tab to appear in the filtered datasheet. Create the criteria on the second tab in the same way that you created those on the first — that is, click the field, and choose the value that you want to match. If, in addition to all the addresses in San Francisco, you want to see all the addresses in Boston, set the State field on the Or tab to MA and the City field to Boston. When you use an Or tab, another Or tab appears, allowing you to add as many sets of Or criteria as you need. Click the Filter button in the Sort & Filter group on the Home tab of the Ribbon to see the filtered table.

Article / Updated 03-26-2016

You can create an unbound form that appears when you open an Access 2013 database. Follow these steps to make your new form and the macro that will open the form automatically: Create a new form by clicking the Form Design button in the Forms group on the Create tab of the Ribbon. Access opens a form in Design view. Save the blank form by clicking the Save button on the Quick Access toolbar or by pressing Ctrl+S. In the Save As dialog box, type a name for the form, and click OK. Call the form something like Main Menu. Leave the form open; you make buttons for it later. Now you’re ready to make the AutoExec macro that opens the form automatically. Create a new macro. A blank macro appears. Add an OpenForm action to the macro. Set the Form Name argument to the name of the form you just created (Main Menu). To do so, click the Form Name argument, click its down arrow, and choose the form from the drop-down menu that appears. Close the macro, click the Yes button to save it, and name it AutoExec. You have to name your macro AutoExec if you want the macro to run automatically each time you open the database. Create another macro by clicking the Macro button in the Macros & Code group on the Create tab of the Ribbon. Your main-menu form needs a macro to contain the submacros your buttons will run. You could make all your buttons by using the Command Buttons Wizard, which stores its submacros as embedded macros, but if you want to make your own submacros for your buttons, you can store them in the macro you create here. Click the Save button or press Ctrl+S to save the new macro. Type a name for the macro, and click OK. You don’t have to give the macro the same name as the main-menu form — but you’ll find yourself less confused if you do! If you took the advice in Step 3, name the macro Main Menu or Main Menu Form. Click the tab for the Main Menu form so that you can start adding buttons. Now you’re ready to return to your main-menu form (the one you created back in Step 1 — remember?) and add command buttons. The form is ready and appears when you open the database; all it needs is buttons!

Article / Updated 03-26-2016

Just seeing the name of a built-in function in the third column of the Access 2013 Expression Builder doesn't tell you much. You don't know what the function does or how you use it, but you can get instant information by clicking the Help button. Follow these steps to access an Access help window: In the left column of Expression Builder, if the Functions folder has a plus sign (+) next to it, click that sign to expand the list. Click the Built-In Functions folder in the first column. The category names appear in the center column. Click a category name in the middle column to see functions within that category listed in the third column, or click in the middle column to see all functions in the third column. The functions for that category appear in the third column. In the third column, click the name of the function that you want to find out more about. Click the Help button in the top-right corner of Expression Builder. The help window for that function opens. If you don't see specific help for the function, type the function name in the Access Help search box. Functions are listed by type in the help system, so if you need to find a function in the help system, you'll be able to find it quicker if you know that if it's a Financial function, for example. To see how this works, select the Financial category of functions in the center column, click the PV function in the third column, and then click the Help button. The help page that opens not only describes what the PV function does, but also describes the syntax required for using the function. The syntax of a function describes what information you need to pass (provide) to the function for the function to do its calculation and return a result. The syntax for a function usually looks something like the following: functionName(arg1,arg2,[arg3]) functionName is the name of the function, and arg1, arg2, and arg3 represent arguments that the function accepts. The number of arguments that a function accepts varies. Some functions take no arguments; others take many. If a function accepts two or more arguments, the arguments must be separated by commas. Any argument name in square brackets is optional, meaning that you can omit the entire argument. A function name is always followed by parentheses — even if the function accepts no arguments. Now(), Sqr(81), and PV(apr,TotPmts,Income) are all examples of valid function syntax. Note as well that when typing an argument, you can use a literal value (like the name "Smith" or the number 10), a field name, or an expression as an argument. The following three expressions all pass literal values to their functions: Sqr(100) PV(.035,120,250) UCase("howdy") The next three expressions all pass data from fields to the function (provided that Hypot, Apr, Months, Amount, and Company are the names of fields in the current query): Sqr([Hypot]) PV([Apr],[Months],[Amount]) UCase([Company]) The next example uses expressions as arguments: Sqr(227*[Hypot]) PV([Apr]/12,[Months]*12,-1*[Amount]) UCase([First Name] & " " & [Last Name]) These examples may look weird, but there's method to the madness. The ability to pass literal data, field names, and/or expressions to functions gives you a lot of flexibility.

Article / Updated 03-26-2016

When an Access 2013 database form is open, you can use VBA code to change the contents and even the appearance of the form, from the big picture down to the individual controls. Suppose that you have a form that includes a control for choosing a payment method. When the user chooses a payment method, you want to enable or disable other controls on the form based on the selected payment method. Alternatively, you may want to autofill some other controls on the form or even make some controls visible or invisible, depending on which payment method the user selected. Within VBA, use the following syntax to change a control’s property: ControlName.PropertyName = Value ControlName is the complete name of a control on an open form, PropertyName is the name of the property that you want to change, and Value is the new value for the property. A dot separates the control name from the property name. The complete name means that the name has to contain both the name of the form and the name of the control. In a class module, however, you can use the keyword Me to stand for the form name. The keyword Me means “the form to which this class module is attached.”

Article / Updated 03-26-2016

Access 2013 uses the same file format as Access 2007 and Access 2010 for storing its databases, but previous versions of Access use a different format. You can tell what version a database is by opening it in Access and looking at the title bar. The title bar may display (Access 2000 file format) or (Access 2002-2003 file format) for an old format, or nothing or (Access 2007 - 2013) for the new format. To convert a database from an older file format to the Access 2013/2010/2007 format, follow these steps: Open the database. Close any open objects. Click the File tab of the Ribbon to enter Backstage View, and choose Save As. In the Save As dialog box, below the Save Database As heading, select Access Database (*.accdb). Click the Save As button. Browse to the folder where you want to store the new version of your database. Enter a name for the database. Access creates a new database containing all the objects in the old database, stored in the new format, with the extension .accdb. Additionally, a message warns you that this new database can’t be opened in Access 2003 or earlier versions. Click OK.

Article / Updated 03-26-2016

Some people would rather use the keyboard than the mouse, and Access 2013 has plenty of keyboard shortcuts for those people. The following keyboard shortcuts are especially useful in Access 2013. Some keystrokes work anywhere in Access 2013; others work only in specific views, as noted. Key Combination Action F1 Displays the Help window Ctrl+F1 Hides or displays the Ribbon F5 Goes to the record with the record number you type F6 Moves the focus to another area of the window F7 Checks the spelling in the selected object F11 Hides or displays the Navigation Pane Del Deletes the selected object Alt+Enter In Design view, displays the properties of the selected object Ctrl+C Copies the selected text or objects to the clipboard Ctrl+F Finds text (with the option to replace it) in the open table, query, or form Ctrl+N Starts a new database Ctrl+O Opens a database Ctrl+P Prints the selected object Ctrl+S Saves the selected object Ctrl+V Pastes the contents of the clipboard to the active window Ctrl+X Deletes the selected text or object and saves it in the clipboard Ctrl+Z Undoes the last action that can be undone (our all-time favorite!) Ctrl+; Types today’s date Ctrl+” Duplicates the entry from the same field in the previous record Esc Cancels what you’re typing.