Computer tutorials, tips, and tricks to help you learn Microsoft Excel, Access, Word, and lots more. Visit our full site at www.ComputerLearningZone.com.
If a table or form seems to have vanished, check what the Navigation Pane is showing before you rebuild anything. Bring the pane back with F11, clear the search, and check which objects are listed. If you still can't find it, the video shows where to look for hidden objects.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/5838.
"It don't work" may be one of the most frustrating sentences in all of technical support. Not because the person asking for help is doing anything wrong, necessarily, but because those three words could describe about a million different problems. A computer that will not turn on, a program that freezes, a button that does nothing, a report with bad data, and a printer that suddenly decides it has had enough are all very different situations.
You do not need to know the cause of a problem before asking for help. If you already knew the cause, you probably would not need help in the first place. But you do need to give the person helping you enough information to begin narrowing things down. Good troubleshooting starts with a clear description of what happened, not a guess about why it happened.
Years ago, I got a call from a customer about a computer at the front desk. Their description was simple: it did not work. After some questions, we got closer to the real issue. Pressing the power button did nothing. That was useful information. A computer that will not power on is a completely different animal from one that powers on but will not load Windows.
We checked whether the computer was plugged in. It was. We checked whether there was power in the room. There was a desk lamp on nearby. Eventually, we found the problem: the computer was plugged into a power strip, and someone had kicked the power switch off with their foot. The lamp was working because it was plugged directly into the wall.
Was that an obvious thing to check? Maybe, after you know what happened. But people do not know what they do not know. The person reporting the issue did not realize that the power strip itself was part of the equation. That is why a good support person asks basic questions, and why a good person asking for help should not be embarrassed to include basic details.
The same thing applies to terminology. Back when CD-ROMs were becoming common, support people frequently got calls about computers not reading disks. One technician gave a customer instructions for cleaning a dirty CD. The customer followed the instructions carefully, using water, and then asked how to get all the water out of it.
The customer had washed a floppy disk.
Funny now, yes. Not so funny for the floppy disk. But it is a perfect example of how one person can say "disk" and mean a CD-ROM while another person hears "disk" and thinks floppy disk. The technician assumed. The customer followed the instructions. Nobody was trying to be difficult. They were simply talking about two different things.
So if you are asking for help, be as specific as you can. If you are giving help, do not assume the other person uses the same words the same way you do. Ask enough questions to make sure you are both looking at the same problem.
Before posting a question, try a few sensible basics. Close the application and reopen it. If the computer is behaving strangely, do a real Windows restart. Closing the laptop lid and opening it again is not the same thing. Check cables, power, network connections, printer connections, and anything else relevant to the situation.
If you get an error message, search for the exact wording. Do not search for "Access error thingy" if Access gives you a specific message. Copy and paste the actual text into a search engine or support forum. Exact error text is tremendously more useful than a vague description.
Of course, use common sense. Restarting your desktop PC is reasonable. Restarting a production server because somebody on the internet said "try rebooting it" may not be the best career move. But for ordinary desktop problems, a restart often clears temporary glitches and rules out a lot of nonsense.
When asking for help with a database, remember that the person helping you probably knows Access, Excel, Windows, printers, or whatever technology is involved. They do not know your business, your company jargon, or what you named things in your database.
If you write, "My CKT431 isn't printing correctly," that may make perfect sense to you. You have probably been working with CKT431 for months. But to someone outside your business, it could be a report, a product, a form, a printer, a customer code, or a spacecraft part number. Give people a bridge into your world. Explain that it is a customer invoice report, a product label, a shipping form, or whatever it actually is.
Also, tell people what you are trying to accomplish, not just the complicated method you have already chosen. A lot of people come in with a form, a subform, several combo boxes, VBA code found online, and a plan involving three queries and a sacrificial chicken. After sorting through all of that, it turns out they just needed a calculated field.
Start with the destination. What should the user be able to do when you are finished? Once someone understands the goal, they may be able to suggest a much easier way to get there.
The heart of a good technical question is this: What did you expect to happen, and what actually happened?
"It doesn't work" could mean the button does nothing. It could mean Access freezes. It could mean the program crashes. It could mean a report opens but shows the wrong records. Those are not variations of the same problem. They are entirely different clues.
Try to describe observations instead of jumping immediately to a diagnosis. "My database is corrupted" is a conclusion. "When I open this form, Access displays this error message" is an observation. Give the observation first. The diagnosis can come later, once there is evidence to support it.
Whenever possible, show, don't tell. A good screenshot can answer questions that you did not even know to ask. Show the relevant form, report, error message, or portion of the screen where the problem occurs. Crop the image so the important details are readable, but leave enough surrounding context that someone can tell what they are looking at.
Please do not send a tiny screenshot of three monitors with an error box somewhere in the upper-left corner that requires a telescope to read. And if you are sharing code or an error message, copy and paste it as text instead of taking a blurry photograph of it. Screenshots are great for showing what the screen looks like. Text is better for code and exact messages.
Next, describe the steps that lead to the problem. What did you open? What did you click? What did you type? What happened immediately afterward? Does it happen every time, or only occasionally?
For example, this is helpful: "I open the customer form, select a customer, click Print, and the report opens blank. I expected it to show the selected customer's records." That gives someone a reproducible path to follow.
"The print thing is broken" does not give anyone much to work with.
Another important question is: Did it ever work? If it worked yesterday but not today, something changed. Maybe Windows updated. Maybe somebody installed software. Maybe a field was renamed. Maybe the printer changed. Maybe a file was moved to a different folder.
The last change is not automatically the cause, but it is often a useful clue. Likewise, if the same problem happens on another computer, that tells you something. If it happens only on one computer, that tells you something else. Troubleshooting is mostly a process of narrowing down possibilities, not randomly throwing fixes at the wall until one sticks.
And then there is the phrase, "I tried everything." No, you did not. Nobody has tried every possible setting, option, and combination in the universe. What you usually mean is, "I tried a bunch of things and I am frustrated." That is completely understandable. But the person helping you needs to know what you tried.
Tell them that you restarted Access, tested another computer, tried an older backup, searched for a particular error message, or checked the printer. More importantly, tell them what happened after each test. A failed test is still useful if it rules something out.
When testing, change one thing at a time whenever possible. If you change five settings and the problem disappears, you have no idea which change fixed it. Keep a quick note of what you changed and what happened. If you are working with an important database, make a backup first and test risky changes on a copy. You are trying to solve a problem, not create a sequel.
If you are following one of my lessons, tell me exactly which lesson it is. Better yet, include the title, URL, and timestamp where things stopped matching what you see on your screen. I have lessons on navigation panes and navigation forms, for example, and those are not the same thing. Pointing someone to the exact video and moment saves a lot of back-and-forth.
If you are building along with a course, test the same steps in the original sample database. If the sample works but your modified copy does not, you have learned something important. The mistake may have happened several lessons ago, but at least you now know it is somewhere in your version and not in the original instructions.
A good forum post also needs a useful subject line. "Help" and "Access Problem" are not useful titles. They do not tell knowledgeable people whether they can help, and they do not help future readers find the answer later.
Something like, "Customer report opens blank after selecting customer" is much better. It tells people what the symptom is before they even open the post. It also makes the thread useful to the next person who searches for the same issue six months from now.
You do not need to include your entire company history before getting to the question. Put the important information first: what you are trying to do, what you expected, what happened, how to reproduce it, and what you already tried. Extra background can go below that if it is relevant.
Likewise, do not paste several thousand lines of code and expect a volunteer to hunt through it looking for one bad line. Include the smallest relevant piece, along with enough context to understand what it is supposed to do. Too little information leaves people guessing. Too much unrelated information buries the answer. The goal is not a long question. The goal is a useful question.
Compare these two examples. The first says, "I followed your video and it doesn't work. I tried everything." There is not much anyone can do with that.
The second says, "I am following your lesson on printing a customer report. When I click Print, the report opens with no records. I expected it to show the selected customer. I tested the original sample database and it works there. I restarted Access, but my modified database still has the problem."
That question may not contain the answer, but it gives a support person something to work with. The goal is known. The symptom is known. The point where it happens is known. Some tests have already been performed. That is a huge difference.
Remember that most people answering questions in public forums are volunteers. They are giving their time because they enjoy helping others. Be patient and polite. If someone asks a follow-up question, they are usually not trying to make your life difficult. They are trying to get enough information to help you without guessing.
Public forums are also better than private email for most technical questions. Other people can contribute ideas. Someone may know a solution that the original responder did not think of. And once the answer is posted publicly, it can help the next person who runs into the same problem.
Sometimes, simply writing out a clear question solves the problem before you even post it. Programmers call this rubber duck debugging. You explain the issue step by step, perhaps to a rubber duck sitting on your desk, and suddenly realize what you forgot. I do not happen to keep a rubber duck around, but a rubber penguin works just fine.
And if you have been staring at the same code or error for three hours, take a break. Go do something else. Watch some Star Trek. Get some sleep. A fresh set of eyes, even if they are your own eyes the next morning, can make a ridiculous difference.
Finally, if you find the solution, come back and post it. Tell people what fixed the issue, even if you solved it yourself. Maybe it was a field name, a printer driver, a bad network connection, or a power strip switch under the desk. The final answer is often the most valuable part of the whole discussion for someone searching later.
For Access students, I also have an Access Troubleshooter that covers the basics along with Access-specific things to check, such as compiling your VBA project, using Compact and Repair, testing the original sample database, and checking exact error messages. You can also review the forum posting rules before submitting a question.
The next time you are tempted to say, "It don't work," stop for a moment and turn that into a question someone can actually answer. Explain what you are trying to do, what you expected, what actually happened, and what you have already tried. Include screenshots, exact error text, and clear reproduction steps when you can. Give the people trying to help you something useful to work with.
Watch the embedded video for more stories and examples of how a little extra information can turn a frustrating support request into a solvable problem.
Need to compare a budget with actual spending? Abs gives you the size of the difference, whether you're over or under. Keep the signed difference too if direction matters. Watch my Absolute Value video to see how to use this in your queries, forms, and reports.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/ABS.
You open your Access database and suddenly half your stuff appears to be gone. Maybe the tables are missing, maybe the reports disappeared, or maybe all you can see are a few forms. Before you start rebuilding anything, restoring backups, or reaching for the Tylenol, check the Navigation Pane. Most of the time, the objects are still there. Access is just showing you less than you think it is.
The Navigation Pane is the list along the left side of the Access window that contains your tables, queries, forms, reports, macros, and modules. Think of it as the directory for your database. It helps you find the tools that make up your application, but it is not the data itself. Your Customer table might hold your customer records, while a Customer form gives users a friendly screen to work with those records. Both are database objects, and both should normally appear in the Navigation Pane.
If the entire Navigation Pane has vanished, the first thing to try is F11. That keyboard shortcut toggles the pane on and off. Press it once to hide the pane and again to bring it back. On some laptops, you may need to hold down the Fn key while pressing F11 because the function keys are often assigned to volume, brightness, or other hardware controls.
You can also use the little shutter button near the top of the Navigation Pane to collapse or expand it. If you only see a skinny strip of icons, do not assume your objects have gone anywhere. The pane may simply be collapsed or too narrow to display the full list. Drag the right edge of the pane to make it wider. This is especially helpful when you have similarly named objects like CustomerTable, CustomerForm, CustomerReport, and CustomerQuery. Seeing only the first few letters is not much help.
The next thing to check is the Search Bar. This gets people all the time. You may have clicked in the search box yesterday, typed a few letters, and forgotten about it. Now Access is only showing objects whose names match that text.
For example, if you type Cust in the Navigation Pane search box, Access might show CustomerT, CustomerQ, CustomerF, and CustomerR. That can be very useful when you have a large database. But if you leave that search active and later go looking for OrderF, it may look like your order form disappeared. It did not disappear. It simply does not match the search.
Remember that the Navigation Pane search box searches object names, not the records stored in your tables. Typing Smith will not search through every customer's name and magically find Joe Smith. It is looking at the labels on the filing folders, not the paperwork inside those folders. To find a customer record, use a form, query, or another record-search feature in your database.
If your tables, forms, or reports seem to be missing, clear the Search Bar before doing anything dramatic. Then look at the category and group currently selected at the top of the Navigation Pane.
For beginners, I generally recommend using the Object Type category. It keeps tables together, queries together, forms together, and reports together. It is simple, predictable, and easy to understand. Under that category, you can choose to view all objects or just one group, such as Forms or Reports.
If you are viewing only the Forms group, then naturally you will not see your tables or reports. Access has not deleted them or transformed them into forms just to annoy you. You are simply looking at one section of the object list. Switch back to All Access Objects if you want to see everything.
Also look for collapsed group headings. If the Tables heading is collapsed, all of the tables under it are hidden from view until you click the heading to expand it. This is another one of those tiny interface details that can make a perfectly healthy database look like half of it vanished overnight.
Access offers other ways to organize the Navigation Pane too. You can arrange objects by created date, modified date, or tables and related views. The Modified Date view can occasionally be handy if you are trying to remember what you worked on yesterday. In most cases, though, Object Type is the easiest starting point.
There is one more important possibility: the object may be hidden. Access lets you hide tables, queries, forms, reports, and other objects in the Navigation Pane. This is often done to reduce clutter or keep users from casually opening objects that are intended for developers.
To check for hidden objects, right-click near the top of the Navigation Pane and open Navigation Options. Turn on Show Hidden Objects and click OK. Hidden objects should appear dimmed or grayed out in the list. That tells you they are still in the database, but Access normally keeps them out of sight.
You can hide an object by right-clicking it, opening its properties, and selecting the Hidden option. This is useful for keeping a Navigation Pane tidy, especially in a database shared with everyday users. However, do not confuse hiding with security. Hiding an object is not the same as protecting it. Anyone who knows enough about Access can turn on Show Hidden Objects and find it again.
In other words, hiding a payroll table does not turn it into Fort Knox. It is more like putting a folder behind the front panel of a filing cabinet. The folder is still there, and the cabinet is not locked.
Access also uses the word "navigation" for several completely different features, which can cause some confusion. The Navigation Pane is the object list on the left side of the Access window. A navigation form is a form you build for your users, often with buttons or tabs for Customers, Orders, Reports, and other areas of the database. And record navigation buttons are the little controls at the bottom of a form that move you to the first, previous, next, or last record.
So when someone says, "My navigation disappeared," ask which navigation they mean. Did the object list disappear? Did their main menu form close? Or are the record navigation buttons missing from a form? Same word, entirely different parts of Access. It is like troubleshooting a car because someone says the "controls" are broken. Are we talking about the steering wheel, the radio, or the windshield wipers?
As your database grows, custom groups can make the Navigation Pane more useful. Instead of organizing everything strictly by object type, you can create groups based on a person's job or department. You might have a Front Office group with customer forms and reports, a Sales group with order forms and invoices, or an Accounting group with billing and payment reports.
These custom groups are basically shortcuts to your existing database objects. Putting CustomerF into a Front Office group does not create another copy of the form. It simply gives you another convenient place to open the same form. You can even include the same object in more than one group if it is useful in more than one area.
Be careful when cleaning up custom groups. Removing an object from a custom group is not the same thing as deleting the actual database object. If you only want to remove a shortcut from one group, use the appropriate remove option. Do not delete something unless you really intend to remove it from the database. Deleting a table because you did not like where its shortcut appeared would be a pretty bad way to organize your workspace.
If you are building an Access application for other people, you may eventually choose to hide the Navigation Pane when the database opens and send users to a main menu form instead. That can make the database look cleaner and prevent users from wandering directly into tables and queries they should not be touching. Just make sure the menu system is built and tested first. Do not hide the map before you have given people a road to follow.
Startup settings can hide the Navigation Pane automatically, so if F11 does not seem to help in someone else's database, that may be part of the application's design. Be cautious about changing startup settings in a database you did not build or maintain. The developer may have intentionally configured it that way.
Finally, make sure you are in the correct database file. This sounds obvious until you have a current working copy, an old backup, a test copy, a practice copy, and three files with nearly identical names sitting in the same folder. It happens. You can spend a long time looking for yesterday's changes in last month's copy of the database.
So when something appears to be missing, keep calm and work through the easy checks first: show the Navigation Pane with F11, clear the Search Bar, check the category and group, expand any collapsed headings, turn on hidden objects, and verify that you opened the correct database file. Most Navigation Pane mysteries turn out to be display problems, not database disasters.
Watch the embedded video for a full walkthrough of these settings and a live demonstration of how hidden objects, searches, groups, and custom categories behave in Microsoft Access.
Need to tell odd and even numbers apart in an Access query? Check the remainder, not the last digit. CustomerID Mod 2 equals zero for even numbers. Wrap that test in IIf to display Even or Odd. Watch the video for the query setup.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/Mod.
Ready for a quick Microsoft Access report quiz? This one covers a deceptively important idea: what actually controls how many times a report's Detail section prints. If you have ever built mailing labels, invoices, packing slips, or any report that mysteriously prints too many or too few rows, this is the stuff you need to know.
Grab a piece of paper, keep score if you like, and do not peek at the answers too quickly. There are five questions, all focused on Access reports, record sources, form references, and printing a full sheet of labels.
Question 1: What primarily determines how many times a report's Detail section prints?
A. The number of text boxes in the Detail section B. The number of records returned by the report's Record Source C. The number of pages selected in Print Preview D. The number of controls on the form that opened it
Answer: B. The number of records returned by the report's Record Source.
In a normal Access report, each record returned by the report's Record Source gets one pass through the Detail section. If the report's query returns 13 records, the Detail section prints 13 times. If it returns 30 records, you get 30 detail positions. The number of text boxes and labels you put in the Detail section has nothing to do with the number of records Access processes.
This is the key idea behind many label-report tricks. Need 30 labels on a sheet? Give the report 30 records to process. Access does not care whether those records contain useful business data or are simply there to make the Detail section repeat.
Question 2: A report control uses an expression that refers to a control on an open form. What must be true when the report runs?
A. The form must remain open so Access can evaluate the reference B. The form must be saved as a report first C. The report and form must use the same Record Source D. The referenced form control must be unbound
Answer: A. The form must remain open so Access can evaluate the reference.
If a report control contains an expression that reaches out to a form, Access has to be able to find that form at the moment the report is generated. A reference such as =Forms!CustomerF!FirstName reads the value from an open form named CustomerF.
If CustomerF is closed, Access cannot resolve that reference. You will generally get an error or an ugly little #Name? instead of the value you expected. The usual solution is to open the report from the form and leave the form open while the report is previewed or printed.
Question 3: Why might a label report use a small table containing exactly 30 otherwise meaningless records?
A. To permanently store each customer's mailing address B. To make Access automatically create 30 new customers C. To supply 30 Detail-section records for one full sheet D. To force the report to use landscape orientation
Answer: C. To supply 30 Detail-section records for one full sheet.
This is a classic Access technique. You create a little helper table, sometimes called a numbers table, with 30 records. Those records can be nothing more than ID values from 1 through 30. The report uses that table as its Record Source, and Access prints the Detail section once for each record.
The labels themselves can contain calculated controls that read values from the currently open customer form. Since the report has 30 source records, Access produces 30 label positions, each showing the same customer's information. The table is not storing customer data. It is just giving Access a reason to repeat the Detail section 30 times. Think of it as a row generator. Not glamorous, but it gets the job done.
Question 4: Which Control Source expression correctly displays the FirstName control from an open form named CustomerF in a report text box?
A. [CustomerF].[FirstName] B. =[CustomerF]![FirstName] C. =Forms!CustomerF!FirstName D. =Reports!CustomerF!FirstName
Answer: C. =Forms!CustomerF!FirstName
The Forms collection tells Access that you want to retrieve something from an open form. The first exclamation point identifies the form, and the second identifies the control on that form. The equals sign is important too, because it tells Access this is an expression to evaluate, not literal text to print.
You will use expressions like this when the report's Record Source is not the customer table itself. For example, your label report might use the 30-record helper table as its source, while each label reaches over to the open customer form to display the customer's name and address.
Question 5: A full-sheet label report repeats values from the current customer form 30 times. Why is it a poor choice when three labels need different text?
A. Reports cannot contain calculated controls B. Changing the form value changes every repeated label that reads it C. Access reports can print only one copy of each record D. A report cannot use a table with 30 records
Answer: B. Changing the form value changes every repeated label that reads it.
If all 30 labels use expressions that point to the same form controls, they all evaluate the same values. Change the customer's FirstName on the form, and every label using that expression changes right along with it. That is perfect when you want a full sheet of identical labels. It is not so great when label 1 needs one message, label 2 needs another, and label 3 needs something completely different.
For individually editable labels, you need separate stored records for the individual labels, or some other process that creates separate data for each one. A report can certainly display calculated controls, and it can absolutely use a 30-record helper table. The problem is simply that all the repeated labels are reading the same source values.
So, how did you do? The big takeaway is that a report's Detail section is driven by its Record Source. Once you understand that, tricks like full-sheet label printing make a lot more sense, and Access stops looking quite so much like it is operating a wormhole behind your back.
If you want to see these report and label concepts in action, watch the embedded video. These topics are also covered in more depth in Microsoft Access Advanced Level 4.
Got a year in one field and a month in another? DateSerial puts the pieces together into a real date value. The order is year, month, day. If you only recorded the year and month, using day 1 is an assumption, not a recovered date. In this video, I show you how to build the dates, handle missing source values, and use an update query to save the results. Back up your database first.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/DateSerial.