Microsoft Access Quick Queries #20: Avoiding Lookup Fields, Dlookup, and Database Maintenance Tips

 

Today's TechHelp tutorial from Access Learning Zone focuses on addressing various questions that don't require an entire video on their own. This is episode 20 of our Quick Queries series, where I compile your frequently asked questions into one comprehensive session. Let's get started.

A common inquiry I receive is about my aversion to the lookup wizard and lookup fields in tables. The main reason is that using lookup fields in a table isn't considered proper normalization or good database design. For instance, if you have a customer table and want to store titles, suffixes, or prefixes, such lists should reside in separate tables, not as lookup fields within the customer table. This approach ensures better database design and facilitates easier manipulation and programming of data later.

Some people worry that using lookup functions in queries, such as Dlookup, could corrupt the database. While it's unlikely to cause corruption, it will significantly slow down performance. Each Dlookup call in a query must process a lookup for each record, which can be inefficient. Instead, I recommend using joins to link tables, which is much faster and more efficient.

Database corruption in Access is typically due to issues like improper shutdowns, large file sizes approaching the two-gigabyte limit, and network problems. Some bugs in recent Access versions can also cause corruption, which underscores the importance of regular backups. Using lookup functions is fine for small-scale lookups, like those on forms, but avoid putting them in queries to maintain speed.

Regarding the use of temporary variables (temp vars), it is best practice to clear them at the end of their use within a function. While temp vars persist for the entire database session and modern computers have ample memory, tidying up by removing or clearing temp vars can prevent unnecessary memory usage, especially in extensive databases.

Another handy tip from our forum moderator, Kevin, is about reverting to the older Access theme. If you prefer the look and feel of the 2013-2022 theme, you can change to it by navigating to File -> Options -> Client Settings, and selecting the checkbox to use that theme.

Eve asked about automatically importing multiple CSV or text files when they are added to a folder. You can set this up by having your database run a loop to check the folder or by adding a button that triggers the import process. This can be similar to how you would import images into a database.

For those who struggle with moving objects in Access forms without using a mouse, Matt Hall shared a useful tip. You can use arrow keys for moving objects, control-arrow keys for finer movement, shift-arrow keys to resize, and control-shift-arrow keys for precise resizing. These shortcuts offer more precision than dragging with a mouse.

Another common issue involves date formatting in forms. Even if you have set the date format in the table correctly, it might not reflect on the form because the form's settings override the table's settings. Ensure you check the format settings in both the table and the form.

Larry brought up a question about command button names not matching those in my videos. Every new control added to a form receives a sequential number, which is arbitrary. To avoid confusion, give meaningful names to controls as soon as they are created, and be cautious when renaming them to avoid breaking code.

Many users sometimes lose the little box or rulers in their form design view. To restore them, go to the Arrange tab, select Size & Space, and click the Ruler icon.

Adam contributed code to convert a form into a report, which can be useful if you need such functionality.

Finally, Donald asked about verifying required fields before committing forms to the database. While setting fields as required ensures they must be filled, it's often better to allow blank fields rather than risk invalid data entry. For complex validation involving multiple fields, consider table-level validation rules. 

Kenneth wondered why not all note fields use rich text. While rich text can be useful for formatting, it can complicate data export processes. Use it only when necessary.

James suggested using filter properties to make A-Z jump buttons more efficient by filtering rather than jumping to specific records. This method could be particularly beneficial when dealing with large datasets.

That's it for today's Quick Queries. For more detailed tutorials and 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/QQ20?key=Blogger

Student Grades in Microsoft Access: Proper Data Entry, Spreadsheet-Like View (Crosstab)

 

Today's TechHelp tutorial from Access Learning Zone addresses a frequently asked question: how to store students and their grades in a Microsoft Access database and present this data in a spreadsheet-like format using a cross-tab query. This query came from Candace in Beiberton, Oregon, who is transitioning from Excel to Access for managing student grades. Candace, and many others, have been accustomed to the Excel format with students as rows and assignments as columns.

The big difference between Excel and Access lies in data storage. In a relational database like Access, data isn't stored in a flat format. Instead, you use tables, each representing different entities. For student grade management, this means you need to set up multiple tables. I'll guide you through this process and show how to create a cross-tab query for displaying data in a familiar format.

This tutorial is at an expert level. Though advanced, you don't need programming knowledge. Knowing terms like many-to-many relationships, junction tables, subforms, and relational combo boxes will be helpful. I recommend watching my other free videos for a detailed explanation of these concepts.

In moving data from Excel to Access, you need three tables: one for students, one for assignments, and a junction table to link them. Here's how to set it up:

1. Create a Student table for storing student information such as student ID, name, and other details.

2. Create an Assignment table to store details about quizzes, tests, projects, etc.

3. Create a junction table, StudentAssignment, to link students and assignments and store their grades.

To illustrate, let's set up the database. We'll use the TechHelp free template, available for download on my website. We start from scratch but sometimes use pre-set templates for ease.

First, create the Student table:

- Go to Create, then Table Design, and set up the table with fields like student ID and student name. Save it as the Student table.

- Populate the table with sample data by copying from an existing Excel list.

Next, create the Assignment table:

- Similar to the Student table, use Create and Table Design. Add fields like assignment ID and assignment name. Save it as the Assignment table.

- You can transpose a horizontal list from Excel to vertical if needed and copy it into Access.

Now, create the StudentAssignment table:

- Again, use Table Design for creating fields like student assignment ID, student ID, assignment ID, and grade. Save this table.

Entering data in Access can be more complex than Excel, but sample data helps in building the database. You'll link students and assignments through the junction table, and grades will be stored there.

Next, we create a data entry form with a main form for student information and a subform listing assignments and grades. We will also make a reverse form where the assignment is the main form with a list of students.

For the main student form:

- Copy a blank single form from your template and bind it to the Student table. Set the form properties accordingly.

For the subform:

- This will be a continuous form bound to the StudentAssignment table. It lists each student's grades for assignments. Remove unnecessary fields and set up the combo boxes to display names instead of IDs.

Finally, to avoid displaying raw IDs, use combo boxes for user-friendly data entry. I'll show how to create these combo boxes and bind them to the appropriate fields.

This completes today's tutorial. For a detailed, step-by-step guide, visit my website. Until then, live long and prosper, my friends.

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

Customizing the Quick Access Toolbar for a Single Database in Microsoft Access

 

Today's TechHelp tutorial from Access Learning Zone is geared towards beginners. We'll be discussing how to customize the Quick Access Toolbar (sometimes referred to as the Quick Launch Toolbar) for a single database in Microsoft Access. This topic comes from a question by Heather from Levittown, Pennsylvania, one of my Palette members. Heather wants to know how to set up the Quick Access Toolbar for different databases, each requiring unique buttons.

There are two ways you can configure the toolbar: for Access in general on your system, affecting every database, or for each individual database. If you haven't seen my initial video on setting up the Quick Access Toolbar, I recommend watching it first. In that video, I cover what it is and how to create a macro that runs and opens a form. I'll cover that briefly again today.

Let's start in my TechHelp free template database, which you can download from my website. I've already customized the Quick Access Toolbar to include buttons for actions like save, undo, redo, design view, running a query, datasheet view, form view, print preview, and opening the Visual Basic Editor. One button even opens the code for the form you're currently on.

Next, there is a customized button for my specific database. When clicked, it opens the main menu using a macro named "open main menu." On the Quick Access Toolbar, you can create buttons that execute macros to open forms, which is one of the few ways I use macros. Another frequent use is the auto exec macro.

Let's assume you have a specific form, like a customer form. In most of my databases, I have a main menu form named "main menu F." I typically copy the "open main menu" macro into each database, which covers almost all of my databases. However, not every database will have a customer form, so let's create a macro to open it.

First, make a copy of an existing macro. Rename it "open customer F" and modify it to open the customer form. Change the form name in the macro to "customer F," save, and close it. Running this macro will open the customer form.

Now, add this macro to the Quick Access Toolbar. Go to more commands, and you'll notice options for the toolbar in all documents and then for specific databases. In the specific database section, add the new macro. You can modify the button with a custom icon and name, like "Open Customers." Once added, you'll have a button that opens the customer form exclusively in this database.

If you open another database, the customized button won't appear because it's specific to the initial database. This ensures a tailored Quick Access Toolbar for each database you work with.

If you enjoy learning with me, check out my four-hour Access Beginner 1 class. It's comprehensive but fun and goes by quickly. There's also a condensed 30-minute version for those short on time. That's all for today's TechHelp tutorial. I hope you found it helpful. You can 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/QATForOneDb?key=Blogger

Passing a Form as an Object Variable to a Global Subroutine in Microsoft Access VBA

 

Today, I will show you how to pass a form as a variable to a subroutine or function in Microsoft Access. This allows you to send the actual form as an object to your code and modify it based on the received form.

Clara from Pureland, Texas, a platinum member, sent in a question. She asked if it's possible to create a global function to set the form's background color to gray when a record is not active. This is smart because it avoids duplicating code across multiple forms.

The goal is to use a global subroutine to prevent repetitive code. If all you need is to check if 'is active' is true and set the background accordingly, then the same code can be utilized across different forms.

First, you need to understand how to pass a form as a variable. Although you can technically use a function for this, a subroutine is sufficient because it does not return a value. Functions typically do return values. Both subroutines and functions are called procedures. However, for simplicity, I often refer to them as functions.

This topic came up in the forums as well. Richard Van Wagner had a similar query, trying to use "me" in a public global function. However, "me" only works within forms and reports, not globally. Adam, Kevin, and Sammy provided helpful inputs, and we concluded that passing the form as a variable is the way to go.

This tutorial is for those with some VBA experience. If you're new to VBA, I recommend watching a beginner tutorial first. The key concept here is using the "on current" event to trigger actions when you navigate through records or when the form opens.

First, let's work on this in the customer form. Open the customer form in design view. You want the code to run when you move from record to record and when the form opens. Insert it in the "on current" event.

In the "on current" event, check if the 'is active' field is not active. If it's not, set the form's background color to gray. Forms themselves do not have background colors, but their sections do. Specifically, the detail section's background color can be set to gray using RGB values. For instance, RGB(100, 100, 100) gives a medium gray. If the field is active, set it to another color, like blue.

After testing and verifying that the code works for one form, the next step is to make it work globally. Cut out the existing code from the form module and paste it into a global module. Name the subroutine, for example, "public sub change background." Ensure you move the "end sub" to the correct place.

During compilation, the code won't recognize "is active" or "me" because they are specific to the form. You need to tell the subroutine which form you want to apply changes to by passing the form as a variable.

Instead of using screen.active form, you should pass the form as a variable. Define the subroutine to receive a form variable, making it an object of type form. Use object variable references for fields and sections within that form accordingly.

After modifying the subroutine to use the form variable, compile it to check for errors. Then, in the form's "on current" event, call the subroutine with the "me" keyword, which refers to the current form.

Finally, test the updated customer form to ensure the changes apply correctly. The same subroutine can now be used for other forms. For instance, you can modify it to work with an "is paid" field in the orders form, and adjust colors as needed for different forms.

To explore additional concepts, such as restoring the default design color of each form or different field names, stay tuned for the next part of this tutorial. Members can watch it immediately.

For a complete video tutorial with step-by-step instructions, visit my website using the link below.

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

Generate Sum of Last Orders by Customer in Microsoft Access

 

Today's TechHelp tutorial from Access Learning Zone addresses the concept of calculating the total of the last orders placed by each customer. I'm Richard Rost, your instructor, and today we're focusing on a query that can help you make future sales predictions by using the most recent order information of regular customers. Instead of relying on last month's total sales, which might include sporadic walk-ins, we're isolating data for those who consistently purchase each month.

One of our platinum members, Grant from O'Lake, Kansas, posed an interesting question: "How do I compile a total using only the most recent order from each customer?" This approach can impressively assist in estimating upcoming sales. I can certainly relate to Grant's needs, as I also analyze my members' activities similarly to forecast my own sales and filter out random transactions.

Let's break down the process involved. It's slightly advanced but doesn't require VBA, although I will share some VBA methods in the members' extended cut, which can sometimes simplify the task.

Firstly, ensure you're familiar with order entry and invoicing systems. I recommend checking my previous videos on building these systems if you're not already. This tutorial also requires an understanding of aggregate queries, so reviewing my DSum-related content is beneficial, too, as it shows how to display totals using DSum efficiently.

We'll construct a query listing each customer's maximum order ID and corresponding order totals. We'll then use DSum to calculate a complete total for the main menu. The completed task can be seen on my website and YouTube channel.

In our scenario with the TechHelp free template, every customer has associated orders that detail their purchases. Our goal is to sum all the most recent orders. Observing the table structure, you'll see that totals aren't stored directly in tables but determined through extended price calculations in order detail queries.

I'll adjust a few orders as examples. After manipulating the data to create scenarios where the date is a point of conflict, we'll proceed to build essential queries. It's crucial to adapt this to your own filtering preferences, like distinguishing between regular customers or specific payment statuses.

Our initial task is to extract a list of customers alongside their maximum order date. This involves creating a query, setting it as an aggregate query, and employing the max function to filter dates effectively. Using a descriptive name for this query will aid in maintaining clarity.

Next, we dive deeper by joining this result with the order summary to identify the corresponding order totals. Sometimes, having multiple identical dates for different orders might complicate things; hence we refine our query further, focusing on maximum order IDs per customer. Secure these detailed results with a second query, which helps resolve discrepancies without overwhelming one single query.

Finally, let's incorporate DSum to aggregate our query results for a synthesized total, showcasing each customer's latest purchase total. This sum can be effortlessly displayed on your main menu. Decomposing tasks into incremental steps is key; resist the urge to overcomplicate individual queries.

An alternative approach involves using a record set loop, looping through customers to obtain and accumulate their maximum order totals. This will be further explained in the extended cut available to members.

To explore additional examples of queries, joins, and functions, consider my Access Expert lessons. The program spans multiple levels and offers members enriching content that bridges non-programming practices with advanced Access functionalities.

In summary, this method aids in understanding and implementing a practical query system for better sales forecasting. For a detailed video guide on all the topics mentioned, visit my website through the link below. Live long and prosper, my friends.

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

Align Text Boxes with Margins in Microsoft Access Forms: Dates and Currency

 

Today's TechHelp tutorial from Access Learning Zone focuses on a fundamental concept for beginners: aligning text boxes for numbers, dates, and currency so they appear neat and tidy. It's straightforward to align the text boxes themselves, but there's often extra space inside that can disrupt the alignment. The goal is to ensure everything looks well-organized.

Brandon from Tustin, California, one of my platinum members, reached out with a query about this. He has a form with a column of vertical text boxes, some formatted as currency and others as dates. The issue is that currency values leave additional space to the right, allowing for negative number parentheses, whereas date values do not. This misalignment makes the column appear disorganized. Brandon wants to know how to add space to the right of date values to achieve a neatly aligned column.

This can be easily addressed by setting a margin inside the text boxes for date or number values that require alignment adjustment. For currency data types, the space is already provided for negative parentheses. By adding a margin, rather than padding (which is different), we can resolve this discrepancy.

In my TechHelp free template, available on my website, I have a customer form with three aligned number fields. Personally, I prefer left alignment, but I understand the preference for right alignment. Let's take a moment to adjust these values for left alignment by accessing the format options.

If you're unfamiliar with the process, I usually keep my ribbon collapsed when recording videos for a cleaner view. You can double-click to expand it and maintain it open. Let's select the text boxes and apply right alignment. I'll save, close, and reopen to verify they look correct.

Now, observe the text boxes closely. The currency fields have additional space for potential negative parentheses, while others do not. To add a margin, enter design view. Select the text boxes by drawing a box that touches them, without encircling them completely. Access the property sheet, located at the bottom of the right-click options, and open it if necessary.

With the text boxes selected, navigate to the Format tab and locate margins (distinct from padding). We'll start by adding a small right margin, around 0.1, to see how it looks. Save, close, and reopen the form to check the result. If it's excessive, return to design view and adjust the margin to slightly smaller values, such as 0.09 or 0.08. Consider 'Twips' - a measurement unit - that may cause numbers to appear slightly altered. Adjust accordingly until the alignment is visually satisfactory.

These margin adjustments apply equally to left, right, and top margins. If this interests you, I cover various form formatting techniques in my beginner series. You can start with Microsoft Access Beginner Level 1, a four-hour free course introducing the basics. Beyond that, I offer beginner, expert, advanced, and developer lessons on my website.

That's today's TechHelp tutorial. I hope you found it instructive. Live long and prosper, my friends. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website.

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

Detecting Duplicate Product Purchases by a Customer in Your Microsoft Access Databases

 

Today's TechHelp tutorial from Access Learning Zone is focused on a scenario where you need to detect if a customer has made a duplicate purchase of the same product - a situation that can often arise with memberships. If you're managing memberships, it's crucial to ensure that your customers don't inadvertently sign up for the same membership more than once, leading to double billing. This topic arose from a question posed by Phoebe in Roswell, Georgia, who wanted a way to identify if a customer accidentally signed up for the same membership more than once. 

In my own experience, I've had similar issues with memberships like Silver, Gold, and Platinum. Sometimes, a customer may join on a certain day and forget, only to sign up again later. While my website isn't programmed to prevent this, my database flags any duplicate memberships, enabling me to address any potential billing errors promptly.

Today, I'll guide you through a method to set up a routine that checks for duplicate purchases using Access queries. While this is an expert-level task, you don't need to know VBA; instead, you'll need familiarity with aggregate queries. If you're new to these, I recommend watching my introductory videos on summary queries and Access query criteria available on my website and YouTube channel.

In previous tutorials, I've demonstrated how to prevent duplicate entries on an order by employing composite keys. However, today's method involves using an aggregate query, which offers flexibility for exceptions and generates a report showcasing duplicates.

We'll start by inputting some data into a sample database. This free TechHelp template, available on my website, includes customers, orders, and products. Let's illustrate with an example: suppose a customer orders the same product, like a 'Silver Membership', twice. Using text fields or product IDs, we set up scenarios to test for duplicates.

Next, we'll design a query that brings together customer, order, and order detail information. The goal is to list each customer along with their product purchases. By implementing sorting options, you can easily identify duplicate entries.

The key task is to group this data by customer and product, enabling us to count occurrences of each product for each customer. This approach will highlight those individuals who have purchased the same item more than once. Applying the appropriate criteria will refine the list to show only duplicates.

Once you've adjusted the query to count occurrences, you can eliminate single entries. The query results will then only display customers who bought multiple quantities of the same product. This way, you can manage exceptions while keeping an eye on potential double billing issues.

I encourage you to explore further Access lessons on my website, where I cover a range of topics from beginner to advanced levels, including queries, form design, and database management.

For those wishing to follow this tutorial step-by-step, a complete video with in-depth instructions is available on my website. Live long and prosper, my friends.

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

Microsoft Access v Microsoft Excel: Which is Better? When to Use Each.

 

Access versus Excel. It's a question I get asked all the time: which is better, what do I use, and when.

Today's question comes from Mark from Akron, Ohio, one of my Gold members, but I get asked something similar to this all the time. Mark says, "I just started a small business and I can't decide whether I want to use Excel or Access to manage my customers, contacts, orders, inventory, and so on. I don't want to use off-the-shelf accounting software like QuickBooks because I do have some unique record-keeping needs for my business. I've been using Excel for years, but I hear Access is a lot more powerful. What do you recommend?"

Well, first Mark, let me start off by saying that I love Access and Excel. Over the past couple of years, I've spent most of my time teaching Access lessons simply because that's more of my niche market. There's a lot less competition for good Access training materials. 

With Excel, there's a lot of good people that are putting out books and tutorials like MrExcel. He's got a lot of great stuff. So there's a lot more competition there. Access, not so much. So that's why I've been focusing my business. So I love them both, and I'm going to give you an unbiased accounting of which one is better to use and when.

Access and Excel really complement each other well. Let's talk about the primary role of each application. Access is great for data storage and management. When you've got a large volume of data that you need to store and you need to be able to manage it, make additions, do update queries, deletes, and stuff like that on a large scale, and you want to automate stuff, that's Access's primary function.

Access is also very good at creating a robust user interface. You want to create menus and buttons for non-computer users to be able to go in and easily do what they have to do. That's what you want Access for. Also, if you're dealing with multiple simultaneous users, if you've got 5, 10, or 20 people in your office that need to work from the same customer table, again, that's a job for Access.

Excel is much better with smaller amounts of data, and it's really great at data analysis and visualization. In other words, if you want to take this year's sales and do analysis on them, what-if scenarios, pivot tables, you want to see trends and things like that in your data, that's what Excel is good at. 

Excel is much better with things like advanced charting. It's got better conditional formatting. We'll talk about these things as we go along today in the video.

Now, each application also has its primary disadvantages. Access, for example, has a steeper learning curve. Like my buddy Alex says, it has a higher barrier to entry. It does take a little more training to get up to speed for building Access databases. It's not quite as easy to use as Excel. Excel is a lot more intuitive. You can just sit down and start typing information in. With Access, you have to set up a structure first.

Also with Access, there's a longer development time. If you're going to build a database to run your business, it's going to take longer than just sitting down and plugging information into a spreadsheet. But that disadvantage is actually a benefit in the long run because you'll be a lot more productive with a properly built Access database.

Excel's problems? Essentially, a lack of structure. Anyone can just type in anything, anywhere they want on the sheet. There's no rules. Yes, Excel does have some basic data validation, I know that, but by and large, you can pretty much get away with whatever you want. That's okay for you, but if you've got 15 people working off an Excel sheet, it becomes a problem.

Another primary problem for Excel is that it's not relational. Each sheet stands on its own. So if you've got customers and orders and products, they're not related together at all. You have separate information in separate sheets, and it can be hard to try and track information down. That's what Access does really well.

So let's talk about how both are alike. Both can store large amounts of data. The maximum size of a single file is 2 gigabytes, and that's a lot of information. That's, I believe, like a million rows in Excel, and I forget how many columns. Access, a single table can only be 2 gigabytes which again is pretty big, but in Access, you can chain them together. You can have multiple tables stored in multiple files, so your size is pretty much irrelevant.

Both can search, sort, and filter data, although searching, sorting, and filtering very, very large spreadsheets can get cumbersome sometimes. Both do have data analysis tools, although Excel's are much more robust. Both can create basic charts. There is some simple charting in Access. It's getting better. Excel is by far a better option for charting, though.

Both have sophisticated functions for Max, Min, and lookups and all that kind of stuff. Both can perform advanced calculations. Both can be secured with a password. If all you care about for security is putting a simple password on your file, you can do that in both Access and Excel. Both support macros and the full Visual Basic for Applications programming language. And both can access data from outside sources, although I will argue that Access does it better.

So that's what they both have in common. Now let's take a look at where each is better than the other. Now these are in no particular order, and the one in bold is the one that I suggest is a benefit.

Access does have a longer learning curve, whereas Excel is relatively easy to learn. It's a lot more intuitive to sit down and just start typing data into Excel, although that can sometimes be a downside because there's less structure. Access does have a longer setup development time, whereas with Excel, you can just start working right away. You can just start sitting down and typing in your customers, your products, and so on.

With Access, it's easier to maintain data integrity with validation rules, formatting, things like input masks, and so on, indexing with no duplicates. Excel does have some basic validation rules, but these are harder to enforce.

Collaboration. Access is better for multiple simultaneous users. With Excel, it's not quite so easy to work in teams. Yes, you can share a file on the network, but you really shouldn't edit it at the same time. Excel Online does have some multiple simultaneous user capabilities. So does Google Sheets, in fact. You can see each other editing a spreadsheet at the same time. But again, Access does this much, much better.

Excel is much better at analyzing small amounts of data, whereas Access does lack rich data analysis tools. With Access, you can build a high-quality, long-term application. I've been running my business off the same database, granted with some updates, since 2002, and it's now 2020, so that's 18 years. I'm using basically the same core database.

With Excel, your sheets can get unmanageable as your data needs grow. I've got 40-some thousand customers in my database. An Excel sheet with 40,000 rows is tough to work with. With Access, you can create a custom interface for your users with menus for different groups. You can have an admin menu, you can have a menu for your accounting people, your secretary can have his or her own screen, and so on. So you can custom tailor your Access application for whoever is using it. 

You can lock them out of certain areas and give them rights to certain other things, and so on. With Excel, it is possible to create some kind of a menu interface, but it's nowhere near as easy. And it's harder to manage workflow if you're using just a bunch of different Excel sheets.

Data entry and lookup can be made simple. Your developer can set things up, and then your users can just use them. Whereas with Excel, everyone who's using it pretty much has to know how to use Excel.

With Access, simple charting is available, but it's nowhere near as good as with Excel. Excel has much more robust charting options available. In fact, I will often take data out of Access, export what I need, and then import it into Excel, and I'll run my charting in Excel.

With Access, you can do some simple like sales charts. It's got bar charts, line charts, and some simple stuff, but Excel has things like trend lines and stuff that Access just doesn't have. Plus, the formatting options are a whole lot better in Excel charts. I tried to do a pie chart in Access the other day using the new metro charts, and you can't even change the colors of the pie slices yet. They're working on it. They're adding more stuff to it, but right now, Excel is way better with charting.

With Access, your data must be structured. Each field has to have rules. This is a date. This is a yes/no value. This is text, and so on. And I think that's a benefit if you're storing information in a database. With Excel, you can literally put data anywhere you want on the sheet. Now, that might be a benefit to you as well depending on what kind of information you're storing. So this one can go either way.

If you just want to use Excel for a collection of loose notes, and I've done that before. Like I used to do my personal checkbook in an Excel sheet because I could make notes and put stuff wherever I wanted, and that's fine. But eventually, it got big enough where I moved it over to Access.

Alright, I've touched on this one before. Access is better at managing large amounts of data, searching, sorting, and filtering, whereas large sheets can be very cumbersome to work with in Excel. If you've got an Excel sheet that's more than a couple hundred rows, I think that's starting to get unmanageable.

We've talked about the longer setup and development time, whereas with Excel, you can start working immediately. Data in Access is relational. Customers are related to orders, orders are related to products, and so on. In Excel, you don't have that; everything is stored in a flat file spreadsheet. So if you want a list of what products your customers in New York have purchased in the last year, it's so much easier to generate that in Access than it is with Excel data.

In Access, records can have unique auto number IDs. Access automatically maintains numbering your customers and numbering your products, whereas Excel doesn't do that. Access has indexing, so it speeds up searches and sorts. Excel doesn't have indexing. So if you want to take a list of 10,000 customers and sort them by any particular field, last name, first name, whatever, that's a lot faster in Access.

Access has referential integrity, and that means that you can have situations in Excel where you put in an order that doesn't have a customer. You can't do that in Access. You can set up referential integrity so that if an order doesn't have a customer associated with it, you can't enter it in the system.

This is what I'm guilty of a lot. In Access, calculations are bound to fields in queries and are easier to enforce. So when you put calculations in a query in Access, that record is the one that is always calculated on. In Excel, you can have a situation where you put in a formula, and you got the wrong reference in there. So it's referring to a different row. And I do that all the time. I copy and paste stuff, and I forgot the auto-fill, and I'm having my calculations pointing to different records, different rows. That can't happen in Access.

In Access, you can set up parameter queries where the user can type in. Let's say you're looking for a list of customers from a particular state, they can type in the state when you run the query like New York or Florida, and boom, there's your list. You can't easily do that in Excel.

A big benefit of Access is action queries. There's update queries, delete queries, append queries that can change data in huge batches when you import new orders or something like that. With Excel, you can do something similar. You can do mass updates and stuff, but it requires either a macro or some scripting or some VB programming.

I've kind of already talked about this. In Access, complex queries can be saved for novice users to run. So you can set up a query, again, that shows all the products between two dates from customers from a particular state for a particular sales rep. You can put all that in one query and save it, and then Joe the secretary needs to run that report to give it to someone, he just clicks one button and boom, there's the report.

Excel doesn't have something like that available. You have to know how to use Excel and its filtering options to get the data that you want. And it's even harder if it's in multiple spreadsheets.

Now I know a lot of these pros have been on the Access side so far. I'm kind of picking out different features that Access has, but Excel does really have some areas in which it shines. For example, what-if analysis doesn't exist in Access. Data modeling, so much better in Excel. Pivot tables and pivot charts, they used to have pivot tables in Access 2010, but they removed them in 2013. 

Because again, pivot tables, it's something better to just take the data that you want to pivot, take it out of Access, just export it and then import it into Excel quickly, and you can run your pivot table then.

You can set up user-level security in Access. Now they don't have user-level security built into Access like they used to have back in like Access 2000, 2003. They removed that. But like I show in my security seminar, it is still possible to set up your own user-level security and lock down your database. It's not built into Access, but there's a way to do it if you really want to take care to make sure that your database is secured.

With Excel, there's simple protection, there's sheet-level protection, and you can put a password on the entire workbook, but security isn't as easy to implement or as robust to implement in Excel as it is in Access. 

Now, once you have that security set up, you can audit your user activity. You can see who's doing what, who's deleting records, adding records, and so on, whereas with Excel, that's just not possible.

Access is much better for printing out reports. Any kind of report you can make, you can print it. Mailing labels, different kinds of correspondence, all kinds of stuff. You can print out anything. I can do pretty much anything in Access that you can do in Word, for the most part. With Excel, you can format your sheets to look good when you print them, but it's nowhere near as powerful as Access. Excel is much better with conditional formatting. You've got icons, data bars, color scales.That's where you go from one color to another one, you know, like hot red to light green. Okay. Access has real simple conditional formatting with basic colors. It's useful, but it's not as good as Excel. So if you're looking for data visualization, Excel is where you want to be.

I've actually got this next one backwards. I didn't bold it correctly. Let me fix it real quick. There we go. It's a benefit for Access that only the developer in your office needs a paid copy of Access. If you've got 20 people in your office, only one person, the person who's making the database, who's making design changes, needs a paid copy of Office. Everybody else can run the database using the free runtime edition that you can download from Microsoft's website.

Whereas with Excel, everybody has to have a paid copy of Microsoft Office to use Excel, the desktop Excel. However, to flip that over, you can give everyone a free copy of Excel online if they log on to office.com and set up a Microsoft account. They can use Excel online, which has 80% of the features that people are going to need from Excel right in the free version. Whereas Access has no such free version.

But, you know, Access with a Microsoft 365 subscription is like $12 to $13 a month. It's not expensive, and you only have to pay that for one person in your office or whoever is going to be developing. 

Okay, so in summary, to run down these real quick, when to use each. Use Access when you want a more robust user interface, you don't mind taking some more time to learn how to build it, and you've got a little more time for application development. You don't mind spending time doing that. 

You've got to store large volumes of data, thousands and thousands and thousands of records. Definitely use Access. You need relationships between your tables. All right, definitely use Access for that. You have employees who aren't computer people who don't really even know the basics of Excel, and you don't want to bother training them. You can build them a bulletproof application in Access they can use to do their work.

If you need multiple simultaneous users, then Access is pretty much your only choice. If you want to be able to import and export or modify large amounts of data automatically with action queries, again, Access. And if you want a variety of different printable reports and labels, Access is your man. 

Now use Excel if you don't want to take the time to learn Access. All right, it's got a real small learning curve. You can just sit down and start entering your data. If you've got smaller sets of data to store, feel free to use Excel. I don't take everything that I have and put it into a database right away. If I've got something that I know is going to only be 20, 30 rows of data, sure, I'll throw it in a spreadsheet. 

There's no sense taking the time to set up a table and do all that stuff and put it in Access if it's just a small set of data. I keep my list of domain names that I own, for example, in a spreadsheet. I've got like 20 different domains, and I want to make sure that I've got the domain name and the password and when it renews and all that stuff. And that's just in a spreadsheet. I don't need to build a database for that. 

If you need the adaptability to put information anywhere on the sheet, use Excel. If you want to be able to put notes in the corner and this and that over there, Excel is just fine for that. Performing any kind of statistical analysis, what-if scenarios data modeling, use Excel. 

I will often export information from Access into Excel to do that or for data visualization. Better charting, pivot tables, better conditional formatting. 

Okay, so after going through all of that, what's the final verdict on which is better. Which one should I use? Drum roll please. Cue the drum roll. Oh, I don't have a drum roll. Okay. Forget that. All right. So which should I use? Use both. 

Of course, they complement each other. They work well together. Excel is quicker and easier to set up initially, but a well-built Access database will definitely save you time and money in the long run. It'll increase your productivity to have a good Access database. Managing large, complex spreadsheets is cumbersome, especially if you need relational data.

Right? If you've only got a couple hundred customers, feel free to keep them in your Excel spreadsheet. As soon as you get up to a couple thousand, you might want to really consider moving them over to Access, especially if you're dealing with situations where each customer might have multiple addresses, multiple phone numbers. Now we're talking relational databases. 

I will often start out entering my data in Excel. This gives me a way to get up and running quickly. Then when my data grows to over a few hundred rows, I didn't mean to rhyme that, it's time to start moving over to Access. After you've built your Access database, continue to export your data to Excel for rich charting, pivot tables, what-if analysis, and so on. Each application has its place and they complement each other nicely. 

So that is my verdict on which is better and when to use each, Excel versus Access. They each have their own benefits, they have their own drawbacks, and they make a great team for your office data management.

Want to learn more about Access and Excel and how to get them to work together? Well, in the extended cut, I'm going to show you a few tricks. 

First, I'm going to show you how to import or link to Excel spreadsheets from Access. That's handy if you've got someone using an Excel sheet somewhere on your network, maybe doing data entry for you. I'll show you how to link to that sheet so you can see what information is in there, and you can even pull it into your database. Then with an append query, you can run a little append query and have it automatically brought into your customer table with one click, well, double click. 

Then I'm going to show you how to go the other way. So if you've got someone on your network that is just using Excel and they want to have access to, let's say, your customer list or your itinerary or whatever that you've got stored in Access, you can have it so that they can see that information inside of Excel. And there's a trick to have it so it automatically refreshes when you open up that spreadsheet file, and I'll show you how to do that in the Extended Cut for Members.

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

How to Convert Text to Title Case for Movies, Books, and Songs in Microsoft Access

 

Today's TechHelp tutorial from Access Learning Zone covers the topic of converting text to title case, where the appropriate words in titles like movies, books, and songs are capitalized. Landon from Mount Prospect, Illinois, asked how to change all the text in his database so that the first letter of each word is capitalized, as seen in titles. The text in his Microsoft Access database consists of inconsistent cases, and he seeks an efficient method to standardize them without manual edits.

To address Landon's query, I have previously discussed changing the case of letters and words. In my Change Case video, we explored converting text to uppercase, lowercase, and proper case using UCase, LCase, and a custom proper case function. Proper case converts the first letter of each word to a capital letter while making the remaining letters lowercase. However, for titles, not every word needs to be capitalized—articles and short conjunctions like "the," "of," "a," and others typically remain in lowercase unless they appear at the beginning of the title.

We're going to modify the proper case function to accommodate these special rules. This tutorial assumes a developer-level understanding, including familiarity with Dlookup, NZ function, for-next loops, and arrays. You should also review videos on the split function since we'll be using it to manage arrays.

First, create a "movie" table in Access to store the list of titles, ensuring none of them use field names like "name" or "date," which are reserved words. Populate this table with movie titles, either in lowercase or varying cases. Tools like ChatGPT can generate random titles for practice.

Once you have your list, create a query to convert these titles to proper case using the StringConvert function with a parameter of 3 for VB proper case. Note that while VB proper case works in VBA, it needs numerical representation in queries.

To enhance this function, we'll create our own VBA function called title case. This function will convert the entire string to proper case first and then determine which words should revert to lowercase based on a predefined list. Instead of hardcoding this list into VBA, we'll store it in a separate "small word" table in Access. This approach allows users to easily update the list as needed.

Next, split the input string into an array of words using the split function, so each word can be processed individually. Loop through the array, checking each word against our list of small words. If a match is found and the word is not the first in the title, it is converted back to lowercase.

By the end of this part of the tutorial, you'll have the groundwork for your title case function, which can handle converting text to title case, considering specific capitalization rules. Tomorrow, we'll continue refining this function, focusing on rebuilding and returning the string. 

You can 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/TitleCase?key=Blogger

How to Perform Natural Sorting in Microsoft Access for Mixed Alphanumeric Lists

 

Today's TechHelp tutorial from Access Learning Zone is about natural sorting. I'm Richard Rost, and I'll guide you through this concept. Natural sorting arranges data in a way that feels intuitive, especially when dealing with text fields containing both numbers and letters.

Clara from Bowie, Maryland, a platinum member, posed today's question. She receives unit number lists combining numbers and letters, such as 1a, 1d, 2f, 571, and 01d. When sorted in her database, these entries are arranged alphanumerically, causing mismatches like 100 appearing before 2. Clara wants to sort them numerically first and then by letters.

Computers sort data alphanumerically by default, focusing on each column in sequence. This means numbers appear before letters. Therefore, an entry like 105 might be mistakenly grouped with entries starting with 1. To achieve a numeric sort while ignoring the letters, we can use the Val function, which converts a text string into its numeric value.

For our demonstration, we'll create a table in a free template from my website. Starting with an ID field, we'll include a field labeled 'my field' as short text, accommodating both numbers and letters. We'll populate this table with sample data: 1, 2, 3, 1a, 2c, 100a, 205f, and 1001g.

When we sort this data, it's ordered alphanumerically. To implement natural sorting, we'll create a query with a calculated field. This field will utilize the Val function to convert text strings into numeric values by extracting the leading numeric characters.

In this calculated field, we'll define a new field 'x' that applies the Val function to 'my_field.' Running the query, you'll see the numeric parts extracted correctly. The Val function starts from the left of the string and stops at the first non-numeric character or period, ensuring it extracts the correct numeric values.

However, Val has some rules. It parses until encountering a non-numeric character or period. Symbols like dollar signs and commas are considered non-numeric. So a string like $14 will return zero since no numeric characters are found before the symbol.

With our new 'x' column in place, sorting becomes straightforward. We'll sort by this calculated column 'x' in ascending order. Additionally, by including 'my_field' in the sort, we ensure that entries with identical numeric values are correctly arranged by their letters.

Finally, save the query. This approach achieves the desired natural sorting, displaying entries in a numerically logical order with letters correctly sequenced thereafter.

This method ensures your data is sorted in a more intuitive, user-friendly manner. For a complete video tutorial with detailed, 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/NaturalSorting?key=Blogger

How to Restart Your Microsoft Access Database at Regular Intervals (Hourly, Daily, etc.)

 

Today's tutorial from Access Learning Zone involves how to restart Microsoft Access efficiently. If you have an Access database that runs continuously on a timer—perhaps like a server on your network—occasionally, it needs to be restarted to avoid issues. Today's question comes from Brianna in Rockville Center, New York, who has a setup like this and encounters various problems after a few days unless she manually restarts the database.

I have experienced a similar issue with my own Access database, which operates around the clock, sending emails and updating information. After a few days of continuous operation, it started to lock up or crash, leading me to reboot it manually. I discovered that restarting the database once an hour cleared up these issues.

This happens because no software is perfect. Access has its share of quirks, such as memory leaks. If you declare variables and do not clear them properly, over time, these small memory issues can accumulate, requiring a restart to clear them out. Think of it like cleaning a chalkboard: after writing and erasing multiple times, it eventually needs a thorough cleaning.

Before we dive deeper, note that this tutorial is intended for those with some VBA programming experience. If you're new to VBA, I recommend checking out some fundamental videos on my website and YouTube channel first.

Let's proceed with the task. I created a folder named "My DB" on my desktop, containing my Access database file. Since Access cannot launch another instance of itself directly, we'll utilize a Windows batch file to accomplish this.

First, create a text document named restartDB.txt. We need to locate the full path to msaccess.exe, which is the executable file for Microsoft Access. You can find this by searching for Access in your Start menu, right-clicking the app, and selecting 'Open file location.' Right-click the shortcut and go to 'Properties' to copy the path.

In the text document, use the "start" command followed by the path to msaccess.exe. Then, add the full path and file name of your database. Save and rename the text file to restartDB.bat. Double-clicking this batch file will launch your Access database.

Now we need the database to run the batch file periodically. In the Access database, we'll set up a form with a countdown timer to simulate its operations. Create or use an existing form and add a countdown field.

In the form's properties, set the Form Load event to initialize the countdown and start the timer interval. The Timer event will decrement the countdown and, upon reaching zero, it will run the batch file to restart the database. This involves shelling out to the batch file and then using the DoCmd.Quit command to close Access.

A few enhancements include adding a delay in the batch file for smoother operations and adjusting the code to restart the database once every hour rather than at shorter intervals. We'll also include a feature to pause the countdown timer if needed. 

Part two of this lesson will cover these additional details.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.

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

A Function to Determine if There are Records in a Table Before Doing Stuff in Microsoft Access

 

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today we're going to talk about determining if there are records in a table or query before you do stuff with them. What kind of stuff? I'll open a form, open a report. You can basically check to make sure there's going to be data before you get an error with the open form or the open report. This is a developer-level video, and I've actually labeled it extra nerdy because we're going to talk about some extra nerdy theory today. So, uh, yeah, let's get to it. 

Today's video comes from a conversation we were having in the forums on my website, and Kevin, one of my moderators (love Kevin), posted this. He says, "I wrote a function to display a generic message if there are no records in a table," which also would work for a query. And he basically wrote it with a DCount. It just checks to see if there are records in whatever you're trying to open, whether it's a, you know, a form or report or whatever, and it'll just give you a message, "There's no records to display." This comes up more with reports than anything else, but it could also happen with forms.

Let's say you've got a contact report. It's based on your contact table, right, all your contact information. And if you've got a button, let's say that opens up this contact report, and there's no data in it, maybe you're doing like, you know, accounts receivable or whatever, and there's just no records. Well, if you make a button, let's make this button open that report. So I'll change it to DoCmd.OpenReport ContactR, acPreview. Okay, and it opens fine now, but if there's no records in it, let's go to contacts and just delete all the records. If I open it now, I get a blank record, right, or blank report, which isn't very friendly.

Now you can use On No Data. This is one of the things I pointed out to Kevin with reports. At least you can come in here, there's an event called On No Data, right, No Data right here. If no data, you could say, you know, MessageBox "No records," and say Cancel = True, but watch what happens if you do this. It says, "No records," and then you get "The OpenReport action was canceled." This happens in several different places. This is just one example. This guy here throws an error. Now sure, you could put On Error Resume Next above it, and that fixes the problem. But a better solution, and the one that Kevin is suggesting, is "Hey, let's just count the number of records that are in there before we do this," right, and let the user know that "Hey, there's nothing in this record set."

So in my Global module, let me open this up. Kevin wrote a function. We'll call it Public Function GetCountData, and then you send in a data source name, right. That could be a table or a query as a string, and this will return a Long, right, the count of the records in that data source. I'm paraphrasing Kevin. He wrote a more complicated one where you can, you know, have it checked based on different tables and stuff. This is the quick version. And then inside here, we'll say Dim L as Long L = DCount("*", dataSourceName). We're going to count all the records from the data source name. Now if you want to customize your message in here, you can. You can say, If L = 0 Then MessageBox "There are no records in" & dataSourceName. And you can do whatever other stuff you want in here. End If. Right, and then we'll just return the value as the function value. Right, GetCountData = L.

And now in our forms and such, before we try to even open that, we can just say, If GetCountData("contactT") = 0 Then Exit Sub, before it even tries to open the report. You come out here, hit the button. "There's no records in contactT." Okay, and if there is something in there, then it works, and there's your report.

Okay, now as we always do in the forums, someone else always has to chime in, and that someone else is me. This one here, you don't want to look at that one, right? I said, "If your goal is to see whether or not there are any records, you should use the DLookup instead of DCount. DCount is great if you want the count of all of the records, but DCount and DSum, they have to read through all of the records to determine how many there are. So if you got 50,000 records, it's got to say, "Okay, I got 50,000 records here. It's got to count through them to figure that out." Whereas with DLookup, it just pulls the first record, which is much, much faster. It's faster if you got huge tables; if you got little teeny tiny tables, you can't tell the difference, right?

So then I put together this one. It says AreThereRecords, right? You got to send it a field name with this version of it because this one up here, I tried to use a star, but you can't use the star with DLookup. So ignore this one, folks. All right, you send it the field name and the data source, and then it does a DLookup of the field name in the data source, and then if it returns a zero, right, because you're using NZ here, which will make it zero if it's null if it can't return a record, and that just returns true or false.

And then a few minutes later, I said to myself, "Well, what if the function could look up the first field name in the table?" All right, assuming of course, yeah, it should be - it's best if it's an ID, but it doesn't have to be; it could be any field. And so then I put together this guy. And let me copy this, and we'll put it over in the VB Editor. We'll go through it. Let's go back to Global Module and down here, paste that one in. All right, I'm going to call it AreThereRecords. You send it a data source name again. Okay, this one returns a true or false, Are There Records.

Now this one's going to open up a record set, which basically is a pointer to a table or query. Okay, and it's going to say FieldName as String = rs.Fields(0).Name. What does that mean? Well, every record set has a collection of fields, right, CustomerID, FirstName, LastName, Address. Those are all fields. So Field(0), the zero item of the Fields collection, is the first record. Give me its name, okay, which would be, you know, CustomerID or whatever the first field happens to be; it doesn't really matter. So then AreThereRecords is going to be Not IsNull. We're going to try to DLookup that field name from the data source. So DLookup CustomerID from the customer table. Okay, is that null? That'll be true or false, and then we're just not that. So if it returns a record, AreThereRecords will end up true, right. If it's null, this comes back false. Because IsNull will be true, and then this will be false, and then AreThereRecords will be false. Okay, close the record set, clean it up, and now you can use AreThereRecords. I didn't put a message box in mine. If you want to put a message box in yours, that's fine too.

Right, so what I did was, where's the thing, let's go over here. So I said, "If AreThereRecords("contactT") Then," we're going to open it up. Otherwise, MessageBox "No records." And if I choose, I like to put my stuff out here because usually I find that this is going to be different for every case, but it's up to you, right. So now, if I hit the button - oops hang on. Oh, contactR, duh, that's my fault. I'm looking in contactR instead of contactT, the data source. Okay, that was my bad. Hit it, and there it is. Now, if I delete the records, let's try it again. There we go, no records found.

So there's two great functions for you. Go back to where they were, right-click, Definition. There's Kevin's, there's mine. I'll put these in the Code Vault for you Gold Members. If you like this kind of super nerdy stuff, come check out the forums on my website. They're absolutely free. Also, I've got lots and lots of developer lessons where I talk about all kinds of nerdy stuff like this too. You'll find links down below for most of the things I talk about today: If-Then statements, DCount, record sets, the On No Data event, that kind of stuff. So check all that out.

And that's going to be your TechHelp video for today. Hope you learned something. Thanks to Kevin for this idea. Live long and prosper, my friends. I'll see you next time.

A special thank you and shout out to our Diamond Sponsors. First, we have Juan Soto with Access Experts, software solutions manufacturing experts specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at AccessExperts.com. 

Another shout-out to Semmy Shamam from Shama Consultancy. Semmy is a certified Microsoft Office Specialist, and he not only offers Access application development but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Semmy is your guy. Check them out at ShamaConsultancy.com.

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

Filtering Long Customer Lists by Date of Birth in Forms and Combo Boxes in Microsoft Access

 

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we're going to talk about filtering by date of birth.

What does that mean? Well, it's filtering by the customer, patient, or user's date of birth, so that when you go to look them up in a list, it's not a big, gigantic list with thousands of names in it. It basically makes it easier to find people.

Today's question comes from Owen in Sugarland, Texas, one of my Platinum members. Owen says, when I go to my doctor's office, they ask for my date of birth first. I assume that's to shorten the list of patients in their database, so when they ask for my name, it's on a much shorter list. I have the same situation with my database, patient records. Since I always have their date of birth, how can I limit the results so my combo boxes aren't filled with thousands of patients?

Well, Owen, this is a great technique. A lot of businesses use it to look up customers. If you don't have a key that you can index with no duplicates, like a phone number, social security number, client number, or order number, if you just got some bit of data like their date of birth, you can at least limit the list of results based on that. 

If you've got 10,000 customers, odds are one in 400 or so will be on any one particular date. If you type in their date of birth first, which is perfectly acceptable for a business like a doctor's office, then you might only have three patients left. You can say, "Oh, what's your first name?" "Oh, I'm Richard." "Oh, hi. How are you doing? Here you are. I got you."

So, that's what we're going to do today. There are a lot of different ways to do this, and I'm going to show you a couple of different techniques. First, I'm going to show you a non-programming way, a non-VBA way, so this will be an expert-level class.

Owen is an expert-level student. What does expert mean? You're a little bit beyond the basics, so you're not a beginner, but you're not quite a developer yet, so you don't have to use any VBA for this technique. But you should know solid access basics.

Let me tell you some prerequisites first. We're going to use my TechHelp free template, which I show you how to build in this blank template video. You should know how to get a value from another form. So, if you've got a customer form open and you want to know how to get a value off the order form, that's what's covered in this video. Likewise, knowing how to have your query criteria get a value from an open form. If you don't know what query criteria are, go watch this. 

I will also be setting up an alias in one of my queries. It's just another name for a field. If you haven't watched all these videos and you're not familiar with these concepts, go watch these and then come on back.

Here I am in the TechHelp free template. We're going to take this text box here, and we're going to make that equal to our date of birth filter. Then we're going to click the hello, hello, that button. We're going to click the hello world button, and then open up our customer list form, this thing, showing just the customers with that date of birth.

I already got a field in this table called customer since. We're going to cheat and pretend that's the date of birth field. Okay?

Let's go into design view. Let's change this guy to the DOB filter. I am going to change its name, come on, all the way up top here. Let's change your name to DOB filter. Let's get rid of the control source. The format of short date is fine, and I'm going to put a default value in here so that I don't have to keep typing in a date in here. So, I'm going to put in my birth date. I'm going to put in 1972-10-23, just like that. I use the ISO date format, which is year-month-day, that way it's not ambiguous. I am on a mission to change the whole world over to the ISO date format. Go watch this video for more information.

We could change this button so it says open customers by DOB or whatever you want to have on the button caption. In fact, we can delete this button now that I'm thinking about it. We have a button right here that opens up the customer list. Move that over there. Open customer list by DOB. Okay.

Now save this, close it, and open it back up again. Right now, if I open up my customer list, it shows everybody. This form is getting its data from the customer table. What we can do is make a query where the query says, "Hey, get your filter from this guy right here, and then show me a list of customers with that date of birth."

Let's make a query next. Create query design. Let's bring in the customer table. Where are you? Customer table right there. Bring in all the fields.

I'm going to cheat. I'm going to make a field called DOB. We already have a field in here called customer since. That's a date field. So, I'm just going to use that. Bring that over here, and we're going to alias it here. I'll zoom in so you can see it. Shift-F2. Oh, my zoom box got really big. There's my zoom box. We're going to go DOB: customer since. That's an alias right there. We're just saying, "Take the customer since field, and from this day forth, call it DOB." That way, we're just cheating and giving ourselves another field. See? It's over there. Now we can refer to it as DOB. It's the same thing as that one. Okay?

Now I can also tell the DOB field to get its criteria from that form field. The criteria, here I'll zoom in again. Well, I'm not going to zoom in because if you zoom in, you lose the IntelliSense. I'm going to go equals Forms! MainMenuF! DOBFilter, which is right there off the screen. See that? You can still type it in, you can see it better, but you don't get the little IntelliSense popup. That would be nice to add to the list, right, Sammy? Put that on our list for the Access team. IntelliSense in the zoom window. Why not? Because that can't be too hard to add. 

Let's save this as the customer list query, and I'm going to close it and open it. Where's customer list? There it is. Open her up, and there is nothing in there. Why is that? Well, probably because no one has my birthday. Probably not even me. Let's go to the customer form. Customer since is the birth date, so I'll put in 1972-10-23. Let's give one other person, one other lucky person, like Malcolm Reynolds, my birthday too, so we have two people, 1972-10-23. Yes, I'm old.

Now, if I run this query, oh, look at that, there we are. Because we matched that date of birth. Now all we have to do is tell our customer list to get the values for itself from that query. So, open up the customer list, go into its controls here, go into its properties, and where it says record source, we're going to make this the customer list query that we just made. Save it, close it, close it, open it, and there you go. That's the easy way.

We can open up one of these other customers just by doing that, which we covered in the other videos. See, that's one easy way to filter based on a date of birth. Now keep in mind, you have to have this main menu form open, because if you try to open up the customer list form and the main menu isn't open, you're going to get enter parameter value because you can't find it. So you could, if you wanted to, at this point, type in 1972-10-23, and it will work. But you have to have that form open if you want to use this criteria.

Now, what if you don't have a default value in here? What if you don't have this value set, which normally you wouldn't. If you're having a, you know, entered patient information form, you might not want to have to enter their data, but they're just looking for someone. See, now you get no records if that's blank. Because you're telling the criteria, it's got to be equal to that. So who's got a date of birth that's equal to null? Nobody in there, I don't think.

So what do you do if this is blank or null? Well, now we're getting a little more advanced. We can use two more functions, the if function, immediate if, and is null. I got videos for these. If you want to go learn more about them, here's the if function, immediate if. And here's a video that will teach you about null, is null, not null, all that stuff. And if you want to learn about null, go watch this.

So what we can do is go into that query, design view, click on that criteria, zoom in. I mean, I am going to zoom in this time. This gets a little complicated. I'm going to copy this to my clipboard. Now we're going to say if is null that guy. If that's null, what you're going to use for the criteria for this record is the value of the record itself, in this case, customer since. Otherwise, the criteria is going to be the criteria on the form. And that's it. Tricky, I know. But that's just how it works. So if the criteria is null, then use the date of this record as its own criteria. So you'll always get the record. Because it is its own criteria. Otherwise, if this is not null, then use it as the criteria. This is a little more advanced stuff. I usually teach this in my higher expert-level classes. Save it. Close it. Open it up.

Now, what's that? Expression is typed in incorrectly, or it's too complex to be available. Okay. Well, what does this mean? All right. Don't hit debug. Hit it.

Let me explain to you what happened here. I'm going to leave this in the video because this happens a lot. This happens to me all the time. And it's just a quirk with the way that we wrote the statement. Let's go back into the query real quick, design view. And again, let's zoom in on that field.

Can you see what's different now? In fact, let me do this so it's easier to read right there. Can you see what's different from what we typed in? Notice the quotes. I put customer since in here. And Access, trying to be nice, converted that into a string. The actual value, customer since. I don't want the actual value, customer since. But if you do that in the zoom box, it puts quotes around it for you. If you do it down here, if I just put in customer since like this and hit tab, look at that. It puts the brackets around it. See, it fixed it for you. This is a lot smarter than the zoom box. So the answer is you got to have brackets around this so Access sees it as a field and not as a text string. And this happens a lot. This comes up a lot in the forums. I get emailed this question all the time. So I'm leaving it in the video so you learn and see it. Quotes and brackets, very important in Access.

Save it. If you run the query right from here, you should get results. And since there's no criteria in there, that's what you want. See, perfect. But if I do type in 1972-10-23 and hit customer list, there it goes. It's working. See that?

So there you go. That's the basics of how to filter by DOB. Now I told you at the beginning of the video that I'm going to show you the non-programmer way how to do this. But it's actually, in my opinion, easier to do this if you know a little bit of VB. Instead of having to make a separate query and use all that criteria, we can do it with a couple of lines of VB code much, much easier. We'll cover that in part two. We'll do the developer version of the same thing, but with some VB code.

This video is going public on Friday, August 30, 2024. We will cover this in part two. Monday is a holiday. It's Labor Day here in the States. So we'll cover it on Tuesday, September 3. Check back then for part two. 

That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. Have a good weekend, and I'll see you on Tuesday for part two. 

You can check them out at AccessExperts.com. Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office Specialist, and he not only offers Access application development but he also provides one-on-one tutoring services. If you need someone to hold your hand and help you with your Access project, Sammy is your guy. Check him out at ShamaConsultancy.com.

Give a thumbs up and post any comments you may have below. I do try to read and answer all of them as soon as I can. Make sure you subscribe to my channel, which is completely free. Click the bell icon and select "All" to receive notifications when new videos are posted. 

Want to learn more? Click the "Show More" link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. Once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted like they used to do. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list, and you can pick how frequently to get emails from me, either as they happen, daily, weekly, or monthly.

If you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the "Join" button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my Code Vault, lots of VBA source code, template downloads, and lots more. I'll talk more about these perks at the end of the video.

Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the Tip Jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I got some puppies to feed. But don't worry, no matter what, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.

If you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over 4 hours long. You can find it on my website or on my YouTube channel. I'll put a link down below you can click on. Did I mention it's completely free? The whole thing. Free. 4 hours. Go watch it.

And okay, okay, a lot of you have told me that you don't have time to sit through a 4-hour course. So I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster in about 30 minutes. And no, I didn't just put the video on fast forward. But I'll put a link to this down below as well.

Now, if you like Level 1, Level 2 is just a dollar. That's it. One dollar. That's another whole 90-minute course. Level 2 is also free for paid members of any level, including supporters. So if you're a member, go watch Level 2. It's free.

Okay, want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. While I do try to read and respond to all of the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month, and sometimes I get thousands of them. So send me your question here on the TechHelp page, and you'll have a better chance of getting it answered.

And while you're on my website, be sure to stop by my Access Forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps out on the site. I appreciate everything you do. I couldn't do it without you.

Be sure to follow my blog, find me on Twitter, and of course on YouTube. Yeah, I'm on Facebook too, but I don't like Facebook. Don't get me started.

Now, let's talk more about those member perks if you do decide to join as a paid member. There are different levels: Silver, Gold, Platinum, and Diamond. Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks. Gold members get all the previous perks, plus access to download the sample databases that I build in my TechHelp videos, plus access to my Code Vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any TechHelp questions. Now, answers are never guaranteed, but you do go higher in the list for me to read them, and if I like your question, you got a good chance of it being answered. You'll also get one free expert level class each month after you've finished the beginner series.

Platinum members get all the previous perks, plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject, and I cover lots of different subjects like Word, Excel, VBA, ASP, lots of different stuff, not just Access. These are the full-length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month, so lots of training.

And finally, you can also become a Diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.

So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.

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

Microsoft Access Quick Queries #19: Multi-Page Forms, Referential Integrity, Numeric Sort, More...

 

Welcome to another TechHelp quick queries video brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Welcome to number 19. Can't believe there are 19 of these already. Is there a quick answer? Quick answers? I can't talk today. Quick answers to queries. Although, you try saying that 10 times fast. Quick answers to queries about Microsoft Access may or may not contain questions about actual queries, although probably, I think we have one on there today. 

Anyways, let's get to it. Today's first question comes from TXM More. He says, how do you approach a two-page form, that is, you have a form in which you are entering a ton of data and you want to break it up into two separate forms, aka page one and page two? 

Well, there's a lot of things you could do. First, you could just make it a really big form. You can make it a really long form and just scroll down, and as your user is typing, just you know, go to the next field and just scroll down. You could set the form size whatever you want on the screen, but the user can scroll up and down as much as they want. Another thing you could do is use tab controls.

Now, when tab controls first came out, I didn't like them because they were kind of buggy, but in recent versions, they've gotten much better, and you can put this tab control on your form, right, and you can put like contact info, an address block, or more info. So you've got the customer information up here and then all their other information, you know, you can put their phone numbers and stuff in here. So a tab control works too if you want to switch between different tabs, I guess, inside of the customer form. That's another option.

You could make subforms that you can switch between, and in this video, I show you how to do that with a vehicle maintenance database. You've got the vehicle over here, the maintenance history, and the future maintenance coming up, and those are just two different forms inside of a subform control and you just change it with the subform's SourceObject property. That requires a tiny little bit of VBA, but this is the method I personally prefer. I like doing this because these can be completely different subforms in here.

Or, you could use any navigation form that you want. I personally like making a form and then putting buttons on it to go to other forms. You could even set up something like this and make like a wizard, right, where the first page has just the fields you want, and then on the bottom is a button that opens up the next form and you make individual forms for it. You know, the first one is enter your name and address, you click next and it goes to another form and it says okay, enter in your whatever, you know, banking information. I don't care. All these forms are still linked to the same table, to the same record, right, but they just display different information and you can have forward and backward buttons and all that stuff. 

So it's all about how you want to do it. If you want to see some more examples, post a comment down below, and maybe if enough people are interested, I'll make a whole separate video just on this topic. 

Alright? Okay. 

Xavier says, are you going to teach us how to embed images in the HTML message? This comment was on my video on how to send email using CDO (Collaborative Data Objects) in Microsoft Access where you can send right from the database and not have to go through a program like Outlook. In the extended cut for the members, I show how to format HTML messages. 

Well, if you're formatting your message as HTML, then all you have to do is put an image tag in it. This is just a basic HTML command, right, starts off with image src=" and then a link to your picture. I recommend putting your pictures on your website or at least a public web folder, right, a Gmail or a Dropbox folder that's public because you don't want to be sending images through email. It makes the emails big and slows down the whole system. So just drop your image on your website somewhere or in a public folder and stick an image tag in there. And if you want to hyperlink that you can too. So that's just knowing a little bit of HTML. I do have an HTML basics course on my website. It's real basic, but I'll put a link to it down below.

Ralph PHP says, I understand that using short text field is a good workaround if you're not doing math on a field, but doesn't Access sort numbers incorrectly if they are stored as text? 

Yes, if you sort a short text field and you've got numbers in it, they're going to sort alphanumerically. Alright, so if I have a table and I've got, let's say, an ID, right, and I've got my value, let's call it, and that's a short text field, okay, my table, and if we put some numbers in here, alright, in my value, like 101, 2, 34, 14, whatever, okay, if we sort this field, alright, right-click, sort A-Z, you're going to get that. That's an alphanumeric sort, which looks at it letter by letter.

Okay, if you want to sort this numerically, just use a query and convert that over to a number. Alright, so you'll create a query and you'll bring in that table, right, my table. Okay, bring in whatever you want and then right here we're going to say my number is just convert to whatever you want, CLng, that's convert to long, my value. Okay, and if you run it now, you see there's numbers. Now here's the big difference, you can see these ones are lined up on the right side of that cell, let's call it the column, these are on the left. Left tells you that's a text value, right tells you it's a numeric value. Okay, and now if you sort it, whether you sort it in the query or not, you can sort it right here. Right, and now you'll see those sort by the proper number. That's all you've got to do, use a query. 

Things like zip codes, phone numbers, social security numbers, I store those as text. They're easier to manipulate, they're easier to work with, they're easier to deal with than actually storing them as numbers. Plus, with some things like zip codes, you lose that leading zero, but it's real easy to convert it to a number if you want to sort them that way. 

Okay, and that's just, again, the CLng function. There's a whole bunch of different type conversion functions. There's convert to byte, currency, date, double, long, whatever you want. Now, this video talks about all of them.

Lucian says, dude looks like Charlie Sheen. Thanks for the help. I certainly hope you mean the Major League young, handsome, attractive Charlie Sheen and not the older "winning" Charlie Sheen. This guy. Yeah, I could see it. Okay. Alright. This guy, I don't know, actually he's still not that bad looking. He's, what, pushing 60. So, not a bad looking guy for his age. 

I've been getting a lot of emails like this one, a lot of comments like this one. Shaky says, is there any way to receive emails without Outlook? Yeah, it's possible, but it's not easy. I've been using Outlook myself to retrieve emails. I've got my Access server database, right, sits in the corner. It sends and receives emails. I send through CDO and Gmail, but I receive from Outlook. It's really easy to receive email through Outlook. I cover it in my email seminar. I'll put a link to that down below. But you've got to have Outlook for it, and I'm worried that they're going to change it so you can't do that in the future. So I am looking for a solution myself. 

I know it's got to be possible to connect using Google's API or something like that to connect and pull in emails, but I just haven't done it yet. But I'll be on the lookout for a solution myself. If anybody knows of a good solution, let me know too, that doesn't require having to have an email program. 

Next up, Elise has a few questions. She says, how do I get Access? I use an iMac. My condolences. I believe I can't use this for Access, correct? And I do have a Microsoft Surface. Can I use Access on that device?

First of all, I pick on Mac users a lot, but I do have respect for Apple products. They're just, with Apple, you're kind of in the walled garden. It's whatever Apple says you can use, and they're very hard to tinker with and play with. I like PC and Android because you can get in there and get your hands dirty and break stuff, right? With a Mac, it's like this is what you're using, these are the colors you hang outside. It's like, no. I haven't really used a Mac product since an Apple II in high school.

But let's address these questions. First of all, how do you get Access? Well, I got a whole video on how you can buy Access. There is no free version available, like some of the other apps like Word and Outlook and Excel. They've got free versions. There is no free version of Access. I currently use the Microsoft Apps for Business, which is eight bucks a month, which I think for a business application like Access is a steal. Okay, now this was as of October 2022. I don't think the prices have gone up since. I could be wrong, check Microsoft's website, and they rename stuff all the time. So it might even have a different name by now, but they do definitely still have a version. I recommend the 365 subscription. It's a small monthly fee instead of a big upfront charge, but whatever works for you. But this video explains a lot more.

Alright, how do you use it on your iMac? Well, you are right, Access by itself does not natively run on a Mac. This question comes up often in our forums, and Alex, my right-hand man, he's got a bunch of different things here. You know, you can use VirtualBox, there's all kinds of things you can do. I'm going to leave this up to Alex. Alex, why don't you put together a video on how to get Access to run on your Mac because you're a Mac user? And there's those different options that are available. 

I just asked ChatGPT to see if it's got any new information, and basically it says, yeah, use a virtual machine, Parallels Desktop, VMware Fusion, VirtualBox. That'll basically create a virtual Windows machine inside of your Mac OS. There's Boot Camp which dual boots, you can remote access into a Windows PC if you've got a Windows PC running around. There's apparently a program now called Crossover. It says that not all features of Access may work perfectly with Crossover, so I don't know about that one. And of course, there's a cloud service. A cloud service is basically a PC in the cloud on the internet. You set up your own Windows desktop with whatever applications you want to install, including Access. 

And this is great even if you are PC users, and you want to have a shared Access database up in the cloud that a bunch of people can use. I've been recommending Access Database Cloud for years, and they're fantastic. So that's definitely another option.

And as far as using it on your Microsoft Surface, well, yeah, you should be able to. Give it a try. And again, I posted this to ChatGPT just to get its opinion. The versions of the Surface that run Windows should definitely be able to use it, like the Surface Pro. Some of the other ones, as long as you're not running in Windows S mode, which only allows apps from the Microsoft Store, and Access is not available there. The Surface Duo and Neo are using Android, so you can't use those. Some of our other users, like Adam, one of our moderators, says he uses it just fine with the Surface. Jeffrey Crafts says yep. So yeah, as long as it's one of those better Microsoft Surfaces, you shouldn't have any problems.

Gary asks, I use Microsoft Outlook 365 for my work emails, is there any way to have those emails appended to a database so I can search and/or sort them, etc.? 

Yeah, the easiest way to do that is to send those emails from Access using the techniques that I mentioned earlier with my sending email with CDO. You can also send the emails through Outlook if you want to, although I recommend getting away from that. Can you use Access to pull information in from Outlook? Yeah, you can. If you've already sent the emails from Outlook, you can import them into Access, or you can even attach to those folders in Outlook from Access to look through them. But again, this only works with classic Outlook, not the new Outlook. Okay?

And if you're not sure which version of Outlook you got, check this video out. I did a video on an Office Watch article talking about how the new Outlook is not ready. You lose a lot of functionality with the new Outlook about sending emails and stuff especially. So the new Outlook is basically just kind of like a web version of it. It's not the classic, beautiful Outlook that we've known for decades.

This was a comment on YouTube regarding my move it up and down with the keys. So you can use on a continuous form. You can up and down arrow to move like an Excel spreadsheet through your continuous form, and one user said that this is not needed. Go to Options, Client Settings, and change the up/down, how it works. 

Yes, this is correct. You can change how the keys work on your system, but that's only in your client. Okay, if you're distributing a database to everyone else, you'd have to go around to everyone else's machines and make the same change. Okay, whereas if you do it with the move up/down keys trick that I show you, it's built into the database, and everyone gets the same functionality. So it's all about how you want it to work. Here's a link to that video if you want to check it out.

Alright, this one's from my guys beating me up because I forgot to cover something in the previous quick queries. When we were talking about copying and pasting controls, and Sammy says, you can somewhat control where Access will paste after you've copied the control. You want to click on another control, and then paste, and Access will paste under the control you clicked on between the copy and paste. And I'm like, yeah, I didn't cover all the tricks in the last video. Yeah, I know there's tons of tricks. There's so many tricks I don't always use all of myself. So if it's not something that I use on a regular basis, I forget about it. And one of the great things about my job is that I love learning things from you guys too. You guys teach me a lot of cool stuff that I never knew before, just a little like keyboard shortcuts and tricks like that. So let me show you what Sammy's talking about.

So if you're in a form, right, and you take this guy and you go copy, paste, okay, if you are on this guy and you go copy, paste immediately, it goes below it. If you don't have anything selected, it goes up there. Okay, what Sammy's saying is if you want to go under here, click on that guy first and it puts it under there. Okay, it actually lines up with the label, but right, if you click on, let me delete this guy. 

Let's see, you want to put something on the bottom here.Let's see you click on this. Copy if you click on that and paste it goes right there. So there's so many little tricks, and I can't cover them all, but that's what Quick Queries are for so we can go over all this weird stuff together. So thanks, Sammy. Here's an interesting question in my form. I'm supposed to by Jerry. Uh, Jerry says they have ActiveControl and PreviousControl.

So ActiveControl tells you what control you're on, like a button you click on or what text box you're sitting in. PreviousControl tells you where you've been. Okay, but he said he couldn't find a NextControl or FutureControl. And sometimes when I think I heard about this stuff, my head hurts. 

Um, yeah, and not being funny, you can figure out what the next control would be if you're tabbing right through the tab order. In fact, I asked Chat GPT to give me some code, and I have not tested this, but this looks about right. What you could do is you could say, "Hey, you know, if you click a button or whatever, look through the tab index," because every control on your form has a tab index, right? One, two, three, four, five. So that Access knows where to tab next.

Okay, and you can customize that order. I covered tab order in a lot of videos. But this will actually see what's the control with the next tab order. All right, and again, I haven't tested this, but it seems about right. So, yeah, okay, that's definitely possible. I can't think of a reason to do this when it would be useful, but is it possible? Yeah, possible.

Here's a great question by William. He says, "I have a database I made myself. I'm trying to compact and repair like I was told to do. When I try to compact and repair, I get an error message saying the compact and repair operation has been canceled. You might not have adequate permission to the folder the database is located in. It's on my own laptop. It's my personal database just used by me. My question is, what can I do to fix this problem?"

All right, I have a question. Can I put my database in a Google folder to do the compact and repair? All right, that's the problem right there, but there are some things. Obviously, run down the troubleshooter. Make sure it's in a trusted location. Okay, now the problem with putting it in a Google Drive folder, okay, is that Google Drive is constantly trying to synchronize whatever is in that folder with its copy in the cloud and your other machines. Okay, so if you're doing a compact and repair while Google's trying to back that file up, it's not going to say exactly that you don't have adequate permission because the file is open. Someone's using it. 

All right, so you got a couple of options. One thing you could do is go to your Google Drive while it's running. All right, right-click on it, go to settings, and then pause syncing. Okay, that will pause Google Drive from trying to make backup, so whatever is in your drive folder, and at that point, you should be able to do your compact and repair. Just remember to make sure you turn it back on. Or the other option is to not store your database in your Google Drive folder. What I'd personally do is I keep my databases in my C drive, uh, in a different  uh, folder that's not backed up in my G drive, for example, and then I have my nightly Access backup copy my databases into my Google Drive folder for backup. 

Okay, so don't run your actual Access database out of a Google Drive or a Dropbox or a OneDrive or any of those shared folder-type setups. All right, this question comes up so much. I got a whole separate video on it. Do not run Access out of a shared folder like that. Even if you're the only person using it, you can have problems like this. All right, I used to use, um... I think it was Google Drive. This is going back probably four or five years, but I used to have an office, and I used to drive, you know, from my house to my office, and it was about a 30-minute drive from Cape Coral to Fort Myers. And in the morning, you know, if I was on my computer, I would, you know, have my database open, and it was in my Google Drive, and I'd close it. 

And then by the time I got to the office in Fort Myers, it had had enough time to sync up. It was just a small database file, maybe, you know, a couple hundred megs, and no problems if you're the only person using it. But if you got two or three people doing that, or the database doesn't sync properly, you're going to have problems, or if it tries to do something like a compact and repair. So just you're better off just not using these things with Access. It's not designed for that, and of course, I like to mention this as much as I can, make sure you got good backups of your data. Okay, okay, back up back up back up!

This question's from June. He says, "I'm having a problem creating a relationship between OrderT and OrderDetailT with referential integrity and cascade deletes. It says that data in the table violates referential integrity rules." This usually happens when you're missing a parent. Okay, you're missing an order, but you've got records in the OrderDetail table already that are looking for, you know, that are linked to a parent that you deleted. Let me show you.

All right, here's my TechHelp database. I got orders, and I got order details. Okay, these all should be matched up. I don't have any global relationships in this database. I don't use them that often, to be honest with you. I prefer to handle stuff like that in my code. But let's say, all right, we've got orders here, and we've got order details. Let's say I delete order one. All right, we got detail items in here, right? For order one, those are the three line items for order one. If I delete that order, all right, lets me do it because I've got no referential integrity. One of the things referential integrity does is it says, you know, everybody on the one side of a one-to-many relationship, you got to have that one record, or you can't have any many records. It prevents orphans and widows. Well, it prevents it prevents orphans. It doesn't prevent widows. That's a whole separate thing. 

A widow would be basically an order that doesn't have any line items which you can check for that too, but it's not quite as easy. I got a whole separate video on that one. But now if I try to go into relationships and create a relationship between OrderT and OrderDetailT, right, just to link them by order ID, enforce referential integrity, okay, hit create and say ah can't do it. All right, it violates referential integrity rules because you've got records in the system already that are no bueno.

Okay, so you're going to have to go back in there and figure out who's missing what before you can set up that referential integrity. And how do you do that? Well, let's make a query. Create, query design. I'll bring in OrderT and OrderDetailT. Now the relationship here in the query is formed because Access sees that you've got that and that they're named the same and they're the same data type. There's, they're both numbers. So this is called an ad hoc relationship. It's not an actual system relationship, but Access is like, yeah, these things are probably related. So I'll relate them. Now bring them both down here. 

And if you run the query now, you'll see they all match up. What's the problem? Well, remember a basic inner join like this says all the records from here have to match all the records from here. So I need to be able to see where this might be missing. So I need to make this an outer join. So double click on that guy. Now we're going to say I want to see all records from OrderDetailT. I want this one show me all the detail items. All right, and if this is missing, okay. See, and you get that little arrow pointing that way. It says show me all of these guys and the related one over here. Now if you run this, oh look at that. You can see there's someone's missing right here. 

Okay, so now I can go over here and I can say give me criteria is null, and now that'll show you just which orders are missing. So you've got four detail items in here that are missing a parent item. Now you can go and fix that. You can figure out what happened in the order. You can add something new. You can delete these line items. However, you want to do it, and then once you do that, you'll be able to create your referential integrity, and once you've created the referential integrity, you can't have this problem anymore in the future. Okay, okay. 

If you want to learn about referential integrity, go watch this video. I've also got a video on cascade deletes, which I don't like them. I mean they've got their place sometimes, but be very careful because cascade delete says if you've got a relationship set up between like orders and details, that's fine. You delete an order. You know, you want all the line items gone even though you shouldn't delete stuff. You should archive stuff or mark it, you know, invalid or whatever. 

But if you've got like a customer related to your orders related to the contacts, you accidentally delete that customer, all of this stuff is gone. That's going to mess up your accounting. So be very careful with cascade deletes. I also spend a lot of time with relationships and stuff like that in my Access Expert Series Level 1. We go over relational database concepts. We go over referential integrity and Expert Level 2 normalization, global relationships, all kinds of cool stuff. And if you want to learn about outer joins, go watch this missing records video. Usually you see this where, you know, you've got a relationship set up between customers and contacts or customers and orders, and you do a query, and you're like, "I'm not seeing all my customers. Why aren't they showing up in this list?" Well, that's because you've got an inner join, so they have to have an order or a contact for you to see them in that list. 

And that's where you usually set up an outer join, left join, right join, outer join, inner join, self joins, all kinds of joins. And of course, I cover all the different kinds of relationships you could possibly want to know in my relationship seminar. Yeah, this is an older image. They're not as cool as my new ones, but this is one of my first seminars I did, but it's classic, and it's got lots of great information in it. All right, so I think that's going to about do it for today. There's your Quick Queries Number 19. I hope you learned something. Live long and prosper, my friends. I'll see you next time for Quick Queries Number 20 or whenever we decide to come out with it. 

All right, take care. A special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts. Software solutions manufacturing experts specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at AccessExperts.com. Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office Specialist, and he not only offers Access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sammy is your guide. Check them out at ShamaConsultancy.com.

For a complete video tutorial on this topic, please visit https://599cd.com/QQ19