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