The Select Case statement in Visual Basic for Applications sometimes "competes" with the If...Then...Else statement, because the two can both be used for testing information. When we want to find out about more than one thing, condition, or what have you, we sometimes need to ask the equivalent of several questions. Which way we go, which type of code, is often our choice. All this emerges from the fact that computers ALWAYS need to do one thing at a time—they can’t make intuitive leaps, as humans can. The If...Then...Else asks "If" something is true, much as in the IF function in [...]
There are a few important and helpful things to watch out for in Access reports. They spring from the fact that, unlike most other database components, reports often have to stand on their own. So their formatting and layout have to be more complete and user-friendly up front. One thing to keep an eye on is the layout of field labels. If the report includes relatively few fields, the labels can often go in the page header, one per column. But if the report has more than six or eight fields, labels might go better right above or next to each [...]
Dealing with errors in VBA code, especially in Access, is a vital part of setting up procedures. If nothing else, an error-handler can give the user a better idea of what’s glitching, if something does. And having a message box come up to give the user even moderately clear information can be a huge help. It also helps the database admin person, and the VBA coder. There are normally three main steps to setting up an error handling routine. First, right up at the top of the procedure, we need a line to tell it what to do for errors. Some [...]
Calculated fields are a really big help in Access; they let us derive data from existing information. And they’re flexible, too. If one of the source items changes, the calculation updates immediately, just like an Excel formula. But some people aren’t aware you can add them to a query. And this is an especially cool thing, because every time we run a query, we get the latest info. One example where we might use this capability would be calculating a restock—how many items we need to add. We can open the database, check the table we draw data from, and design [...]
The idea of the split form in Access is rather like its cousin in Project. We set up a view with the “big picture” or large-scale view in one portion of the window (usually the top), and details in the bottom. Sometimes vice versa, but the thinking is to have both available for convenience. (I'll use the datasheet form shortly, as another example.) Creating a split form is very easy, as it’s listed within the regular forms. We select the table we want to work with, go to the Create tab->Forms->More Forms, and click Split Form. The result is to show [...]
The parameter query, while easy to set up, actually depends on a sort of quirk in the program, as we’ll see. But regardless, it’s a very powerful feature; it allows many variations on one question in a database. Ranges of dates, differences in spelling, and many others can fit a parameter query. A parameter query on a range of dates uses the “Between—And” keywords, and has the user enter a starting and ending date as indicated. (Any criteria of this sort go in the Criteria row in Query Design view.) The quirk is, when we want the program to ask the [...]
The action query in Access is a different thing from the normal query we create, called a “select” query. The latter simply selects data and shows the result. But an action query edits data, or even deletes it. And the key thing one needs to know about this is one cannot use Undo to reverse the effect. So though we can easily create action queries, we need to treat them carefully. Luckily, there is a way to test them first. We can use a food database for our example. Let’s say we need to increase prices on soft cheese (Category 2) [...]
Just as there can be subforms and subreports within an Access form or report, there can be a subquery within a query. As the term implies, a “query within a query” allows the main query to be more specific, or complex. It sometimes requires a little bit of Structured Query Language, or SQL. But this is not a problem. Access is a “shell” over SQL as Windows was a shell over DOS, and the two get along fine. A typical example would be setting up a subquery based on text information, to allow more flexibility with the use of number fields [...]
SkillForge SkillSheet Microsoft Access 2010/13/16 Keyboard Shortcuts Click here to download the PDF version. Frequently Used Shortcuts In order to… Press Select the active tab of the ribbon and activate KeyTips Alt or F10 (to move to a different tab, use KeyTips or the arrow keys) Open the Home tab Alt+H Open the Tell me box on the ribbon Alt+Q, then enter the search term Display the shortcut menu for the selected item Shift+F10 Move the focus to a different pane of the window F6 Open an existing database Ctrl+O or Ctrl+F12 Show or hide the Navigation Pane F11 Show [...]
The use of table relationships in Access allows tables to cooperate in the use of data, but table joins, while they look similar, serve a different purpose. The relationships, which allow coordination while organizing data, nevertheless don’t directly affect, say, the results of a query. Joins do. A table join in a query allows for a filter effect. The thinking is that a query is a question one asks of the database. But redundant answers are no use, and a waste of space. So when we pull data from more than one table, frequently the case, we don’t want repeat answers, [...]
One of the more abstruse points of procedure in Access involves the three so-called normal forms. What, exactly, are they? If you think “guidelines”, or “protocols”, you’d be in the ballpark. When building a database, especially a relational database, there are some “streamlinings” which allow it to function more efficiently. Knowing the how is important, but the why can be too. "Why" is what I’d like to go over here. The first of the normal forms is basically this: In any field, in any record of the database, there should be one and only one piece of information. The simplest examples [...]
The good news is, the import and export processes in Access—and in general—are two sides of the same coin. And there really isn’t any bad news, other than making sure the data are set up in a way that Access can understand—namely, something row-and-column-ish when importing. There’s even an External Data tab in the ribbon, so it’s just a matter of knowing what kind of data you’re bringing in, or what format the recipient needs if you’re exporting. For an import, other than making sure you know where the data are, simply go to the External Data tab, and look in [...]
When setting up reports in Access, we're trying to learn something which wasn’t in the original data. Something calculated, derived, or otherwise figured out from the existing information. It’s fairly easy to do, though there are a few steps. After determining the requirement—a sales tax or a shipping fee, and what calculation we need to find it—we create the field for the calculation. With the report open in Design view, clicking the Design tab under Report Design Tools gives us the Controls group. There, we click the Text Box tool and click or drag where we want the text box (which [...]
A fundamental aspect of Access databases is the setup of relationships between tables. It’s usually the nature of business databases to have them. So the actual creation of the relationship isn’t hard to do. But there are a couple of not-so-obvious things the user needs to know. Once the database is open, and the tables have been created, the first thing is to open the Relationships window from the Database Tools tab-->Relationships group. If there are no relationships already in place, the Show Table dialog box will open automatically. The user can then double-click the tables needed, or select them and [...]
The term “normalization” gets thrown about quite a bit in database circles, to try to explain part of data organization. But it's vague, to someone not acquainted with database-ese. The idea of making data “normal” is not too far from the meaning used by database designers. What's "normal?" When we create, say, a batch of file cards with names, addresses, etc., we tend to lay out the information similarly—consistently—so as to make it easier to follow on each. If each piece of information is in its own place, a “field” or space designated for each, we’re already close to the idea [...]
Working with Microsoft Access can be kind of intimidating when you start, especially because there seem to be so many details to absorb and keep track of. And even fairly experienced database people have to watch out for the little things. One which really drives some new Access users up the wall has to do with forms. Doing form layout is an art unto itself, and getting a good layout can take time. Even after you've got it mostly cleaned up, the program has one particular nitpick some people don't even notice. At first. When you work on a form in [...]
Judging Access vs Excel can take thought. Most people find Microsoft Excel fairly easy to learn—it has a fairly gentle learning curve, the fundamentals only take a short time to pick up, and the program is actually fairly versatile—it’s not just a ledger book on steroids. Microsoft Access, on the other hand, can be kind of intimidating. Some folks hear the word “database” and, to quote the Joker, “they start losing their minds!” The whole idea of a database program is scary because there’s more to learn, more to set up before you can use it, and more to manage even [...]
Often times the information you need isn’t stored as a field within a database. As a matter of fact, certain fields make more sense to calculate whenever they are needed instead of storing the value in a table. Microsoft Access 2013 makes this very easy. To create a calculated you will need a query in Design View. Select the Create tab and in the Queries group click Query Design button. The QBE (Query by Example) window opens and the Show Table dialog box is ready for you to add the table or tables you need. Once the table / tables are [...]
You find yourself working in an Access table filtering out some unnecessary data and it dawns on you this filtering is something you’ll end up doing frequently. It would make sense to store this process as a query in the database. Okay, so now I have to switch over to the Create tab and design a new query based on the filter I just applied. Or do I? You can actually save the filter you just applied as a query. The process is simple. Go to the File tab and choose Save As > Save Object As > Save As. Once [...]
https://www.youtube.com/watch?v=qAt6R-6rfUs In this Microsoft Access tutorial, you'll learn how to import data from a Microsoft Excel worksheet and create a table from it in an Microsoft Access database. To learn more about Microsoft Access, check out our Microsoft Access training courses!
If you've ever inherited a database someone else created in Microsoft Access you may have run across the dreaded "hidden-table" scenario. This is when the original developer of the database has hidden (probably in an effort to keep anyone from disturbing them) the source tables that contain your data from the Navigation Pane in Microsoft Access. You'll realize this has happened, typically, because you'll go to create a query and you'll see a tables or tables as being available to include in your query, but strangely, you don't see those same tables listed in the Navigation Pane in Access. To fix [...]
http://youtu.be/byPxcW1I05c In this free Microsoft Access tutorial, you'll see how to link tables in a Microsoft Access 2010 Query. You'll also learn the difference between inner joins, left outer joins and right outer joins. Learn more about Microsoft Access in our Microsoft Access Training courses.
In this Microsoft Access Tutorial, you'll see how to begin using VBA in Microsoft Access 2007. The basic concepts of objects, properties methods and events are explained and then you will see how to attach VBA code to the load event of a Microsoft Access Form and to the click event of a Command Button on a form. This tutorial is from our live, instructor-led online course: Microsoft Access 2007 VBA Training course. To learn more, visit our Microsoft Access Courses Page.
In this Microsoft Access Tutorial you'll see how to connect to a database using ADO Connection, Command and Recordset objects in VBA code, how to populate text boxes from fields in the data retrieved, and how to create command buttons that use ADO Recordset methods to allow the user to move throughout the data. This content is from our live, instructor-led online MIcrosoft Access 2007 VBA Training course. To learn more, visit our Microsoft Access Courses Page.
SkillForge is proud to announce the most comprehensive Microsoft Access Training schedules available anywhere! Offering Introduction to Advanced courses in Visual Basic for Applications (VBA) - SkillForge offers training for students wanting to learn more about the Microsoft Access application no matter what level of experience they have. Students can choose from: Microsoft Access 2010 Training Microsoft Access 2007 Training Microsoft Access 2003 Training And topics range from the basics of creating tables and queries to writing VBA code. And as a special bonus - all students enrolling in a Microsoft Access Training course will receive free either a self-paced, interactive [...]
Microsoft has recently published a very thorough set of MS Access 2010 resources to the TechNet site. Access has always been the part of the Office Suite that straddles the end-user/IT Professional fence. The resources on TechNet are definitely geared toward Access Developers rather than folks that might be using MS Access more casually. […]