Today's TechHelp tutorial from Access Learning Zone addresses a common issue with scroll wheels and continuous forms in Microsoft Access. Specifically, we'll discuss how to prevent the mouse scroll wheel from moving through records while you're in a notes field situated in the form footer.

Lindsay from Maplewood, Minnesota, one of our platinum members, brought up this concern. She has a continuous form for her customer list and added a notes field at the bottom where she can read the notes for each customer. The problem arises when she uses the scroll wheel within the notes field; it scrolls through the records in the continuous form as well. She asked if there's a way to stop this from happening.

To demonstrate, I'm using the TechHelp free template database, available for download on my website, along with a tutorial video explaining its setup. This template includes a customer list, but lacks a notes field in the footer. If you use the mouse scroll wheel, the records scroll up and down. Let me clarify, the scroll wheel is the small rotating mechanism between your mouse buttons, allowing you to scroll vertically.

Now, let's add a notes field to the customer form's footer. We'll copy the notes field from the existing customer form and paste it into the footer of our customer list form. These two forms are both based on the customer table, which ensures they are appropriately bound to the same data.

After placing the notes field in the footer and saving our form, you'll notice that clicking on a record shows the related notes in the footer. The issue emerges with the scroll wheel - scrolling in the notes field affects the record navigation in the continuous form, which is quite annoying.

Despite exhaustive online searches and trying various code snippets, I found no viable VBA solution. Some suggest using Windows API calls or other advanced programming techniques, but they often don't work consistently. There is an event in the form properties for the mouse wheel, but it hasn't resolved the problem.

If anyone can find a purely VBA solution without using ActiveX controls or external components, please share it in the comments. I even consulted ChatGPT, but its provided code was ineffective. As a workaround, I've implemented a solution that involves no programming: using a subform.

To demonstrate, we'll create a new customer form called CustomerNotesF containing only the notes field. Once it's designed, ensure it's bound to the customer table, so the field can display data correctly. After confirming the data appears as expected, we'll incorporate this form as a subform into our original customer list form.

It's essential to note Access will prompt you with a warning when embedding a subform in a continuous form. It's a standard alert which can be bypassed by resetting the form's default view properties back to continuous forms after inserting the subform. Once the subform is in place, remove any unnecessary labels, adjust the formatting, and ensure it's properly linked to the parent form by setting the link master and child fields to customer ID.

This setup ensures that scrolling inside the subform only affects the notes field, not the parent form records. You can further refine the appearance by disabling the subform's record selectors and navigation buttons and adjusting the scroll bars to improve usability.

To make further adjustments, such as automatically resizing the subform to fit perfectly within the main form, you can employ a few lines of VBA code. This enhancement will be covered in the extended cut for members.

So, there you have it - a practical solution to a common issue with continuous forms and scroll wheels in Access. For a complete video tutorial with step-by-step instructions, visit my website at the link below.

Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/ScrollWheelContinuousForms?key=Spotify

Today's TechHelp tutorial from Access Learning Zone addresses a common issue when using arrow keys to navigate through continuous forms in Microsoft Access. Normally, you can use these keys to move up, down, left, and right between different records. However, there might be instances when you do not want this behavior, such as when editing a long text box.

This question comes from Dylan in Lombard, Illinois, who is one of my platinum members. Dylan has been using the technique I demonstrated for navigating continuous forms using the keyboard in my extended video. It works well until he clicks into a multi-line text box in the footer meant for customer notes. Using the arrow keys there unintentionally moves him to the previous or next record, which is not the desired outcome. Dylan wants to know if there's a way to disable arrow key navigation when editing this particular field.

Yes, Dylan, it can be done. Let's revisit what my original video covered and then address how to resolve your issue.

In the previous video, I showed how to move between records using the keyboard rather than the mouse by creating "Previous" and "Next" buttons with shortcuts. However, to navigate using arrow keys, you need to use VBA programming. For those who haven't watched this video, I suggest doing so as it provides a great foundation for VBA programming essential for this tutorial.

First, you should know that you can use Tab or Shift+Tab to move between fields, or configure the arrow keys for navigation. To set this up in Access, you go to File Options, then Client Settings. Here, an option labeled Continuous Form Record Navigation Keys allows you to use the arrow keys. Although convenient, this setting affects all continuous forms in your database.

To achieve this behavior selectively, you need some VBA code. Start by opening your form in Design View. In the Form Properties, set Key Preview to Yes. This ensures that the form intercepts keystrokes before they reach the fields.

Next, focus on the Key Down event in the Form Properties under Events. This event will capture each keystroke and allow you to implement your logic. In the Key Down event, you can check for specific keys, such as the down or up arrows, using VBA constants.

For example, if the pressed key code corresponds to the down arrow, you use the command to go to the next record. Similarly, for the up arrow, the command will take you to the previous record. To handle potential errors when moving beyond the last record, you include simple error handling in your code.

Now, to address Dylan's specific issue, you need to check if the active control is the notes field and skip the key handling logic if true. This way, arrow keys won't move to another record while editing the notes field. You can accomplish this by adding a conditional statement at the beginning of the Key Down event code to exit the subroutine if the active control is the notes field.

And that's it! Save your changes, compile the code, and test it out. You should now be able to navigate using arrow keys without affecting other fields, specifically the notes field, where arrow keys are reserved for text navigation.

For additional resources on handling If Then logic or error handling, refer to the respective videos on my website. If you enjoy my tutorials and want to deepen your understanding of developer topics, explore more lessons available on my site.

For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/ArrowKeysTextBox?key=Spotify

Today's TechHelp tutorial from Access Learning Zone addresses the common "#Name?" error you might encounter when your date criteria don't work in Microsoft Access Calculated Fields. Riley from Lake Forest, Illinois, one of our platinum members, reached out with a question about this issue. He said, "I'm trying to make a calculated field on my customer form to sum up all of their orders in the past year using DSum, but I keep getting a '#Name?' error. What am I doing wrong?"

Riley, this is a frequent issue, so don't worry; it's a mistake many people make. Essentially, a "#Name?" error means Access has no idea what you're referring to. When you use a function like "Date," Access might get confused if it's not used correctly.

First, let's make sure you have everything set up properly. If you haven't already, you should familiarize yourself with the DSum function, as it allows you to sum values in another table or query, such as summing customer orders. Additionally, understanding concatenation is crucial when working with multiple criteria in DSum, Dlookup, or other D-functions. If you're unsure about these topics, refer to my previous tutorials on these subjects before proceeding.

We'll use the TechHelp free template, which you can download from my website. It includes customers and orders data. In our example, we'll add a field on the customer form to show the total of all paid orders within the last year. We'll use the DSum function for this task.

First, add a new field on the form and change its name to "OrderTotal." In the properties, set the control source to a calculated value. Start by summing all of the customer's orders without any additional criteria:

"CustomerID = " & [CustomerID].

Next, introduce criteria to only include paid orders. Add the condition:

" IsPaid = True"

Remember to include spaces appropriately. These spaces are critical for the function to work correctly.

Now, let's add the date criteria. You want to see orders from the past year. In Access, a value of 1 represents one day. To get the date one year ago, use:

"OrderDate >= Date() - 365".

Initially, this might result in a "#Name?" error because Access tries to interpret "Date" as a field name, not a function. To correct this, ensure you write "Date()" with parentheses.

Also, date values in Access need to be enclosed in # symbols:

"#" & Date() - 365 & "#".

If you still don't see the expected results, it's likely due to how Access compares date values. Adding the "#" symbols ensures Access treats the value as a date.

Finally, if the result is null and you want to display zero instead, wrap the DSum function in the NZ function:

NZ(DSum(...), 0).

This converts null values to zero, making the output more user-friendly.

These common errors—misusing the Date function and failing to enclose date values in # symbols—are easy to fix once you know what to look for. By ensuring the correct use of functions and proper formatting, you can avoid these mistakes and get accurate results.

For more expert-level lessons and detailed instructions on these topics without diving into programming, visit my website. This tutorial aims to help those ready to move beyond the basics but not yet into developer-level content.

Find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/DateNotWorking?key=Spotify

Today's TechHelp tutorial from Access Learning Zone covers how to display either a person's name, a company name, or both conditionally in your Microsoft Access database.

Marcellino from Hefsabud, Georgia, one of our silver members, has asked a great question. In his table, he stores fields for company name, first name, and last name. Sometimes, he has all three, but other times he only has a company name or a person's name. He wants to ensure that when printing statements, the company name shows up if it's available, and if not, the person's name is printed instead.

There are a couple of ways to achieve this. First, I'll show you a basic method that involves adjusting some property settings on your reports. Then, I'll share a more advanced solution that doesn't require programming but will involve a few functions. The advanced method yields better results, but the beginner method is sufficient if you're looking for something quick and simple.

Before we dive in, make sure you've completed my Access Beginner Level One course. It's free and available on my website and YouTube channel. Also, familiarize yourself with the Can Grow and Shrink functionality, which allows fields without data to disappear on your reports.

Let's start with the beginner method. I'll use my TechHelp free template, which you can download from my website. This template includes a customer form with personal information, such as first name, last name, and address. I'll add a company name field to the customer table.

First, open the table in design view and add the company name field below the first and last name fields. Switch to datasheet view and enter some sample data. You should have a mix of records: some with both company and personal names, some with only company names, and some with only personal names.

Next, open a blank report template where I've set up basic settings like margins. Duplicate this template and name it something intuitive like "Customer Report." In the design view of this report, set the record source to the customer table. This ensures the report pulls data from the correct table.

Add the necessary fields from the customer table to the report: first name, last name, company name, and address. For aesthetic purposes, remove the default labels, arrange the fields in an address block format, and resize them as needed.

Save the report and preview it. Initially, you'll notice that some records will display blank fields, depending on the data. To make these fields disappear when they are empty, enable the Can Shrink property for the text boxes and the detail section of the report. This makes sure that if there's no data in a field, it won't display extraneous blank space.

After adjusting the properties, save and preview the report again. Empty fields should now be hidden, making the report look cleaner.

While this method is straightforward, you might be looking to add more professional touches, such as a conditional statement like "Statement prepared for" followed by either the company name or the person's name. For that, we can use a query and some functions, which I'll cover in a future video.

This concludes today's tutorial. If you want step-by-step instructions and a complete walkthrough, visit my website for a full video tutorial.

Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/CompanyorPerson?key=Spotify

What is Microsoft Access & What Do You Use It For?

You've probably heard of Microsoft Word, Excel, and PowerPoint, but you might not know what Microsoft Access is. Access is a database application. It's designed for storing large amounts of data, and for making it easy to automate the things you do on a daily basis. You can track anything you want in Microsoft Access: Customers, Contacts, Orders, Products, Mailings, Finances, Collection, Scheduling... literally anything at all.

A lot of people switch to Access when they outgrow Excel. Excel isn't very good at storing large amounts of data or automating repetitive tasks. Access makes it super easy to relate different types of data together. For example, if you're tracking customers and the cars they own, you can relate them together with a CustomerID. This way you can pull up the customer's record and instantly see the cars they drive. Same with contacts, orders, and anything else related to that customer.

You can build a nice user-friendly interface in Microsoft Access very easily. This is great if you have other people using your database who may not be computer experts. You can do all of your reporting right in Access. No need for Microsoft Word. You can print out financial reports, order summaries, mailing labels. You can even write printed letters and send mass emails right from Access.

Now you might not have Access on your computer even if you have Microsoft Office. That's because it's not part of the Personal version of Office. You need Office Professional or the Microsoft 365 for Business. But it's not that expensive. The whole Office Suite less than $13 per month, which is a bargain.

The list of things you can do with Microsoft Access is too long to cover here. I've literally just scratched the surface. If you'd like to learn more about what Access can do and how it can help your business (even if it's just your side hustle) come to my web site and take my FREE 4-hour course that covers all of the basics.