Happy Thanksgiving! I wish you peace, joy, and a chance to boldly go... Relax and sleep off that turkey!

 

There's no video today or tomorrow because it's Thanksgiving here in the States - and even intergalactic explorers need a day to recalibrate their warp cores.

I'm taking today, tomorrow, and the weekend off to spend time with my family - my amazing fiance and our two puppies, who are already plotting a turkey heist. Whether you're celebrating Thanksgiving, Friendsgiving, or just a quiet day with your computer, I hope it's filled with laughter, good food, and moments that make a Klingon smile.

Whatever you're thankful for, and however you celebrate, I wish you peace, joy, and a chance to boldly go, relax, and sleep off all that turkey. Live long and prosper!

For more please visit https://599cd.com/HappyThanksgiving?key=Blogger

How to Trap Form Errors to Give More User-Friendly Notifications in Microsoft Access

 

Today's TechHelp tutorial from Access Learning Zone focuses on how to trap form error messages in Microsoft Access to provide more user-friendly notifications.

Imagine your users encountering a message like, "The changes you requested to the table were not successful because they would create duplicate values in the index primary key relationship." That's confusing, right? Or sometimes, they might just see an error code like "Error 322," which doesn't help them understand the problem. Instead, we can provide clearer messages, such as, "Error: someone else already has this phone number assigned to them," making it easier for users to know what went wrong.

Elias from Hawthorne, New Jersey, asked how to customize these error messages in forms to make them less intimidating. I'm happy to share that there are many ways to achieve this. For example, you could use a BeforeUpdate event to check each field, but that method can be cumbersome. Instead, we'll use the form's OnError event to identify errors and display appropriate messages.

Before getting started, note that this is a developer-level tutorial, so some basic knowledge of VBA is required. If you're new to VBA, I have introductory videos on my website and YouTube channel which can help you get up to speed.

Let's work with a customer form where each customer should have a unique phone number. First, we'll need to index the phone number field to prevent duplicates. Open the customer table in Design View, find the phone number field, and set it to "Indexed: Yes (No Duplicates)." Save and close the table. If there are already duplicate values, you'll need to resolve those first. If you're unfamiliar with indexing, I have more resources on this topic available for you to watch.

Now, we'll handle potential errors by trapping them in the form's OnError event. Open the form's properties, go to the Events tab, and select OnError. This event runs whenever an error occurs in the form. We'll use the code builder to write a simple message box that displays when an error is trapped.

Initially, let's just show a message saying "Hi" to confirm the error trapping is working. After saving and testing, you'll see the "Hi" message on error occurrence while still seeing the default error. To customize this, we'll set the response to acDataErrContinue, which tells Access to suppress its standard error message.

Next, we'll add functionality to display the specific error number using a message box. Error 3022 indicates a duplicate index value. Knowing this, we can create a nuance in our message depending on the error type. For error 3022, we'll provide a generic duplicate value message. 

To make the error message more specific, we can check which control is active when the error occurs. Using screen.activecontrol.name, we can identify the field causing the error and give a tailored message like "Error: Someone else already has this phone number."

This approach makes it easier for users because they receive clear, relevant messages without seeing technical error codes. You can expand this by handling more error numbers and different fields, offering a comprehensive error-handling strategy. 

If you want to dive deeper into error handling and other advanced form features, my Access Developer 41 class covers these topics extensively. The class also explores additional functionalities like zooming in and out of forms.

For those interested, the complete video tutorial with step-by-step instructions is available 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/TrapFormErrors?key=Blogger

How to Edit Linked Excel Data Directly in Microsoft Access

 

Today's TechHelp tutorial from Access Learning Zone covers editing linked Excel data. Imagine you have an Excel spreadsheet linked to your Access database, but you can't edit it. We'll explore a workaround to make it editable.

Keith from Germantown, one of my platinum members, uses Excel for data entry and managing spreadsheets at his office. He handles reporting and financial statements, frequently importing data into Access. However, he noticed linking an Excel spreadsheet as a table in Access makes it non-editable and wondered if there's a way to avoid constantly importing data while still being able to edit the linked spreadsheets.

Keith, you can indeed edit linked Excel data with a trick. Normally, the default method of linking to an Excel spreadsheet mentions you can't edit the data. However, there's a workaround that allows you to edit and add records to the spreadsheet, though you still can't delete records.

Before we proceed, note that this tutorial is for expert-level users and doesn't require programming. If you aren't familiar with importing data from Excel or linking to an Excel spreadsheet read-only, I recommend watching my introductory videos on these topics available on my website and YouTube channel.

Let's start. I've got a simple spreadsheet with student grades. To link it to our Access database, use the TechHelp free template, which you can download from my website. Link to the sheet by navigating to external data, new data source from file, and then choosing Excel. Select to link it, and open the spreadsheet. The wizard will guide you through the steps: make sure the sheet contains column headings, name the linked table, and finish linking.

Once linked, open the table. Initially, you can't make any changes to the data. Here's the fix—it only works in Access 2021 and later versions. Right-click on the table and select the linked table manager. Find your sheet, right-click, and choose to edit the connection string. Modify the IMEX value from one to zero and save it.

Now, when you open the table, you'll see the option to add a new row and make changes to existing records. You can't delete records, but you can add and edit them. Be aware that multiple users editing simultaneously might cause issues. For single-use edits, this method works well. Changes will reflect back in the original sheet, though the formatting might not be perfect.

If you want more information, Allen Browne's website has an excellent article on this topic, including editing the connection string directly in a query. He provides detailed explanations and a video. I'll link to his page below.

For more advanced techniques, including Excel Automation with Access VBA, check out my other tutorials. I cover importing, linking, and more in my Access Expert level 20 course.

That's it for today's TechHelp tutorial. Visit my website for the complete video with step-by-step instructions on everything discussed. Live long and prosper, my friends.

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

Holiday Sale Alert

Holiday Sale Alert: Save 50% on my Microsoft Access, Excel, Word video tutorials, seminars, and templates. Plus get 1 extra month free with any annual membership. Don't miss out!

https://599cd.com/HolidaySale?key=Blogger

Stop Scroll Wheel from Moving Records in Continuous Forms While in Notes Field in Microsoft Access

 

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=Blogger

Handling Navigation Arrow Keys in Long Text Boxes on Microsoft Access Continuous Forms

 

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=Blogger

Fixing DSum Date #Name? Errors in Microsoft Access Calculated Fields

 

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=Blogger

Conditional Display of Company or Person Name in Microsoft Access

 

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=Blogger

Abolish DST: Why Daylight Saving Time is an Outdated Nuisance that Must Go!

 

It's time for my semi-annual repost of this video to hopefully combat the dumb practice of changing our clocks twice a year. And to make things worse this year, I had to change clocks twice in as many weeks. I took a little trip to Europe last week. I arrived on the 24th of October and had to change clocks on the 27th, and then I flew home on November 1st and had to change clocks again on the third. Talk about punishment.

This year I'm going to go so far as to say let's get rid of time zones too; they're stupid. I had to figure out the time zone differences between Florida and Germany and Greece, and it's just all crazy. Everybody should be on UTC time. That way, it's 9:00 a.m. for everybody on the planet at the same time; you just have to figure out what your local sun-up and sun-down times are. But I'll be happy with just getting rid of the clock changes.  Oh, and switching everybody to the ISO date format.

What do you think? Do you like changing the clocks? Do you want to stick with just daylight saving time or standard time? Tell me your thoughts below.

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