Fitness 56

Add Color-Coded Goal Indicators to Forms in Microsoft Access - Fitness #56

from Computer Learning Zone News https://599cd.com/Fitness56

Fitness 55

How to Save and Load User Settings in Microsoft Access - Fitness #55

from Computer Learning Zone News https://599cd.com/Fitness55

Scroll Wheel Rich Text

Why Your Scroll Wheel Doesn't Work in a Rich Text Box in Microsoft Access

from Computer Learning Zone News https://599cd.com/ScrollWheelRichText

Fitness 54

How to Let Users Type Equations Directly Into a Field in Microsoft Access - Fitness #54

from Computer Learning Zone News https://599cd.com/Fitness54

Quick Queries #63

The Lazy Habit Quietly Bloating Your Microsoft Access Queries and Wasting Bandwidth. Quick Queries #63

from Computer Learning Zone News https://599cd.com/QQ63

Pick Address 2

How to Pick From Multiple Customer Addresses for an Order in Microsoft Access, Part 2

from Computer Learning Zone News https://599cd.com/PickAddress2

Pick Address

How to Pick From Multiple Customer Addresses for an Order in Microsoft Access

from Computer Learning Zone News https://599cd.com/PickAddress

Fitness 53

How to Make a Select All Checkbox for Yes/No Fields in Microsoft Access Forms - Fitness #53

from Computer Learning Zone News https://599cd.com/Fitness53

Fitness 52

How to Lock or Unlock a Field on a New Record in Microsoft Access - Fitness #52

from Computer Learning Zone News https://599cd.com/Fitness52

Quick Queries #62

Important Missing Index That Could Be Killing Your Microsoft Access Performance - Quick Queries #62

from Computer Learning Zone News https://599cd.com/QQ62

Fitness 51

Implement Click and Shift-Click to Select Contiguous Records in Microsoft Access - Fitness Part #51

from Computer Learning Zone News https://599cd.com/Fitness51

Fitness 50

How to Calculate Totals for Checked Items Automatically in Microsoft Access - Fitness Part #50

from Computer Learning Zone News https://599cd.com/Fitness50

Fitness 49

How to Enter Excel-Style Equations in Any Text Box in Microsoft Access - Fitness Part #49.

from Computer Learning Zone News https://599cd.com/Fitness49

Columbus Day or Indigenous Peoples Day? Let's Rename It "Reflection Day"

Every October, the same debate resurfaces here in the US. Columbus Day or Indigenous Peoples Day? It's one of those holidays that means very different things depending on who you ask. Some celebrate exploration and discovery. Others remember conquest and loss. The truth is... both are right.

History isn't clean. It's messy, complicated, and filled with people who did terrible things for reasons that made sense to them at the time. Columbus didn't wake up one morning and decide to be a villain. He was a man of his era, driven by faith, ambition, and the values of a world that saw conquest as divine purpose. Was it brutal? Yes. But so was most of human history.

And it didn't start with Columbus. Conquest and colonization go back as far as we do. The first Homo sapiens leaving Africa displaced or wiped out the Neanderthals. The Romans conquered most of their known world with their coordinated, well-armed legions. The Crusaders spread their faith at the tip of a sword.

Empires rose and fell across every continent long before Columbus, and long after him as well. It's what our species has always done: explore, expand, and, too often, destroy.

But we've also evolved. The same curiosity that sent explorers across oceans, sent humans to the moon, and probes across the solar system. The same human urge to understand "what's out there" has also turned inward, toward understanding who we are, and where we come from. That's real progress.

So maybe the point of this holiday isn't to argue about who deserves a statue. Maybe it's to reflect on what kind of explorers we want to be now. Not conquerors, but learners. Not missionaries, but scientists. Not rulers, but caretakers.

There's a meme that floats around every year: "Celebrate Columbus Day by moving into someone else's house and telling them you live there now." Funny to some people, sure, but it also reminds us how far we've come. We can laugh because we know better.

For more info please visit:

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

Quick Queries #61

Can Microsoft Access Handle API Calls or Act Like a Web Server? Quick Queries #61

from Computer Learning Zone News https://599cd.com/QQ61

Fitness 48

Microsoft Access: Create a Default Value Box for New Records in Form Header - Fitness Part #48.

from Computer Learning Zone News https://599cd.com/Fitness48

Fitness 47

Microsoft Access: How to Copy Records to Today but Keep the Same Time - Fitness Part #47.

from Computer Learning Zone News https://599cd.com/Fitness47

Fitness 46

Microsoft Access Date/Time Secrets: Why Timestamps Aren't What They Look Like - Fitness Part #46.

from Computer Learning Zone News https://599cd.com/Fitness46

Fitness 45

Create a Button to Copy an Item Using a Recordset in Microsoft Access - Fitness Part 45.

from Computer Learning Zone News https://599cd.com/Fitness45

How to Make an OpenAI Chatbot in Microsoft Access That Remembers Conversations

 

Today's TechHelp tutorial from Access Learning Zone focuses on building a chatbot in Microsoft Access with real conversation memory. This means creating an OpenAI-powered chatbot that can remember and carry on an ongoing conversation, just like ChatGPT does in your browser.

Previously, I have shown how to connect your Access database with OpenAI to create simple chatbots. Those early versions were limited since they did not retain any memory of prior conversation: each question was a fresh start, and there was no context from what had been discussed earlier.

In today's lesson, I want to show you how to give your chatbot the ability to remember the conversation. Think of it as adding "memory" to your chatbot, much like ChatGPT remembers all the back-and-forth you have in a session online. With this improvement, the chatbot in Access can maintain ongoing threads, making it useful for all kinds of business situations: customer service chat records, tracking ongoing project discussions, internal help desk issues, sales and CRM histories, employee training, or even creating self-quizzing tools that remember what has already been asked. There are numerous practical applications when your bot can remember each back-and-forth with users.

To illustrate this, I set up a sample database for testing. For example, in one conversation, I chatted about Star Trek, role-played a little adventure, and even had the bot quiz me on anatomy. Each interaction is part of a continuous thread.

In a typical session, you start a conversation and ask something like "It was a shame that Firefly only had one season." The chatbot will respond appropriately. Now, if your next message is something like "Any word on a season two?", the chatbot, because it remembers the previous discussion, knows you are referring to Firefly and can respond correctly. Without this memory feature, it would not make that connection.

This tutorial is geared toward those comfortable with Access at the developer level. If you are new to VBA programming or have never worked with OpenAI in Access, I recommend reviewing my introductory video on connecting Access and OpenAI for all the basic setup steps.

After that introduction, I also created a video on requesting images from OpenAI and making further improvements to the codebase. Even if you are not focused on images, I recommend watching to learn about enhancements like switching to the GPT 4.1 API model and other minor changes.

For members at the Gold level, I will be working from the database that was developed at the end of the images video. Silver members and nonmembers, you will have to build out the database by following the previous tutorials.

To get started, in your test database, first place your OpenAI API key into the designated spot. I suggest enlarging the text box used to send input to the AI to give yourself more space to type. You should also create two more text boxes: one to display the AI's response, and another to store the full conversation thread.

The critical point here is that for the memory function to work, you must send the entire conversation thread back and forth with each request. The prompt essentially tells the AI, "Here is the conversation so far. Here is my latest comment. Please reply accordingly." This builds context, just as ChatGPT's web version maintains its state by constantly referencing the conversation history.

I suggest using different background colors for these new text boxes to distinguish their purposes visually. If you no longer plan to use image features, you can set aside those controls.

Next, there will be some minor adjustments required in the supporting code. Occasionally, OpenAI changes the API response format, so things like the markers for response text may need updating. For example, if the AI's response format changes, you may need to adjust the parsing logic in your function that sends queries and receives replies.

I recommend sticking with GPT 4.1 as your model for now. Although GPT 5 and its Mini and Nano variants are available, GPT 4.1 generally gives the best balance of response quality and speed.

You will need to update the code in your form's command button to handle both the single-message and memory-enabled chatbot modes. If the user fails to fill in the input box, make sure you prompt them with a status message before sending anything to OpenAI.

For our memory-enabled chatbot (let's call it bot number seven in your system), update the logic so that if the conversation thread is empty, you flag it as such for the AI. Each time the user submits input, append both their message and the AI's response to the thread box, always keeping it current. When the bot is in memory mode, make sure your command text incorporates the full conversation thread, followed by the new message.

Be aware that all data sent to and from OpenAI must use JSON formatting. This means you cannot send VB new line characters or double quotes as-is—they must be escaped or converted into friendly characters (for instance, using "backslash n" for new lines and escaping double quotes). Similarly, when processing the reply, convert any escaped characters back into their appropriate Access equivalents for display.

Occasionally, you might encounter bugs due to copy-paste errors or API changes. For example, if your code mistakenly overwrites input variables or fails to add updated content to the conversation history, double-check that you are passing and appending data correctly. Consistent naming and careful troubleshooting habits pay off here.

Once set up, you can see the memory feature in action by having a conversation about favorites from Star Trek or launching into a Dungeons and Dragons adventure, with the AI retaining context and responding naturally to follow-up comments. You can also create learning quizzes, where the AI remembers which topics or questions have already been covered.

There are still a few small details to polish, such as ensuring the chatbot can handle conversations that end with certain special characters (like double quotes) without error, and deleting unnecessary clean-up steps in the code if they create problems.

Currently, the conversation is only stored in the form during use. If you want to preserve entire chat threads or manage multiple ongoing discussions within your database, you can create a table to save conversation records, including an ID, description, and the thread text. This way, every interaction is available for later reference.

One item to keep in mind is the conversation length. Each time you interact with the chatbot, the entire thread is sent back to OpenAI. Long conversations—those reaching around 100,000 characters—will slow down performance and may hit the limits of the AI's context window. In the extended cut of this lesson, I will show techniques for handling long threads and managing multiple chats in your database. Gold and Silver members get access to these extended-cut videos, and as always, Gold members can download the databases shown in the videos for their own use.

When I begin advanced lessons, I always start from a standardized database so everyone is on the same footing. This helps avoid issues from previous small modifications or errors that may have crept in while following along at home.

In summary, today's tutorial covered creating a chat interface in Access with memory using OpenAI, adjusting your code for context handling, conversation tracking, JSON compatibility, and addressing common pitfalls. With this setup, you can have rich, ongoing conversations right from Access and tie them into your actual business data.

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 more info please visit:

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

The #1 Mistake People Make with Null in Microsoft Access - Quick Queries #60

 

In today's Quick Queries video from the Access Learning Zone, I want to talk about the single biggest mistake people make when dealing with Null values in their Microsoft Access databases. I'll give you a hint: Null is not actually equal to Null.

Let's get right to the point. In Microsoft Access, Null isn't equal to anything, even itself. This trips up a lot of beginners and even some experienced users from time to time. Today, I'll go over that foundational concept and also respond to a variety of questions and comments I've received from students through YouTube and email.

The first discussion for today comes from a question posted in my website forums. I don't always get to answer questions there myself since the moderators or other students are usually pretty quick to respond, but this time I managed to catch one just after it was posted.

This leads to an important point: while I used to help answer questions on all sorts of other forums and sites, I don't do that much anymore. By the time I see questions, others have usually taken care of them already. That's why most of what you see in these Quick Queries videos comes straight from emails or YouTube comments. I get far too many emails to answer each one personally, so I encourage students to use the forums for faster responses. Still, if a question stands out as particularly interesting or useful, I might include it here.

So, Tim posted a question about handling situations where a company name might be Null. He wants to use the first name if there's no company name present but use the company name when it exists. This situation is really common, and it highlights a critical misconception with Nulls in Access.

Null is a unique value. It doesn't mean zero, an empty string, or just a blank field. It specifically means unknown. By definition, something unknown can't be compared directly to anything else—not even to another Null. That's why, when you ask Access if Null equals Null, the answer is always false.

Because of that, you can't just use the equals sign to compare values to Null. Access gives us specific tools for this: special operators and functions designed for Nulls.

In Access, there are two methods for checking for Null. You can use the IS NULL operator or the ISNULL function. IS NULL is typically found in query criteria or SQL statements, like "select * from Customers where CompanyName is null." It is an operator and not a function, and if you're not working directly in SQL, you'd use IS NULL or IS NOT NULL in the criteria field of your query.

Now, ISNULL (spelled as one word with no space) is a function that returns a True or False value. You use it in expressions and VBA. For example, you could create a calculated field in a query, and use the IIF function: "if IsNull(company), then use FirstName, otherwise use CompanyName." The same approach works in VBA. Just remember, you should never use "equals Null" in your expressions or code. That's never correct.

For instance, you shouldn't write "if FirstName equals Null" inside an IF function or WHERE clause. That logic will never be true.

Tim, to answer your question, all you need to do is replace "CompanyName equals Null" with "IsNull(CompanyName)". That's it—no equals sign to Null, ever.

One more detail: if you're working from just one table or query, you don't have to specify the field name with the table or query alias every time. Also, since you don't have spaces in your field names, those brackets Access adds are technically not necessary.

Here's an advanced shortcut: you can use the NZ function. NZ stands for "null to zero," but it's flexible; you can specify the replacement value, so if CompanyName is Null, use FirstName. This is a compact way to write your logic, and you'll see it used a lot in more advanced applications.

To summarize: Never check for equality to Null with an equals sign. Use IS NULL, IS NOT NULL, or the ISNULL function. Keep in mind: Null isn't Null. It will never equal itself or anything else. Always use the correct tools when handling Null values.

I have several other videos on my site that deal with Nulls more deeply, including videos about zero-length strings, Null values in calculations, and the NZ function. These are all free resources available both on my website and my YouTube channel. I'll include links below.

Switching gears, I posted a conversation starter this week: What is one feature in Access you wish Microsoft would improve or update? I got some great feedback from all of you.

Jim mentioned enhancing conditional formatting options in Access. I couldn't agree more. Excel has far better tools for this, and Access absolutely deserves more attention here.

Dom talked about improving the tab control and asked for content on subforms as a replacement. I've covered this in my "Association Database" series, specifically part eight, where I simulate tabbed pages by switching subforms with labels, making for a much simpler and more flexible setup than using tab controls. A similar method is used in the ABCD series, part one.

Another topic raised was automating compact and repair operations. While it's possible to have Access compact and repair on close, I don't recommend doing it this way. I suggest compacting your backend databases manually or with a scheduled event, such as weekly. This helps prevent your databases from becoming bloated over time.

Kevin asked about adjustable font sizes in property sheets and controls. There's supposed to be a zoom feature on the way, which I'm really looking forward to myself.

There were also comments about making Access look less like Access for end users. I have a video series on making a database appear more custom and less like a standard Access file.

Several of you also mentioned wanting AI integration, exporting reports to Word (docx) instead of RTF, creating executables from Access database front-ends, and better Gantt chart support. On that last point, I've built a template for creating a basic Gantt chart in Access. It's not as robust as dedicated project management tools, but it gets the job done for most internal uses.

Andrew brought up the idea of being able to re-sort ID numbers. Remember: AutoNumbers in Access are for internal use—relationships and unique identifiers. If you need custom numbering formats, you'll have to code your own solution. I have videos covering that as well.

There was also discussion of regular expressions in Access. Previously, using Regex required VBScript, but recently Regex support was added as a library in VBA. If you're curious, check the Access Forever article for the latest details.

Several people mentioned that the developer help files could use a major update, and I agree.

Zooming in the Access work area came up again. It remains an outstanding feature request that I hope to see soon.

I also got a lot of feedback about why IT professionals tend not to like Access. Many say it's because Access is not web-based. That's true—Access is for desktop applications only, and the web app features disappeared years ago. It would be a game changer if Microsoft allowed Access forms to migrate to web pages, combined with SQL Server on the backend.

Another common issue is that many companies and IT departments don't want to support Access, fearing the risk of business-critical databases being left orphaned when employees leave.

Someone else asked about native Git support in Access for source control. This isn't present yet, but there are rumors it will be added in the future. Source control is especially useful for team environments.

Regarding the SQL text editor, some of you noticed Microsoft is pushing updates and working on new features, though there are still bugs. Microsoft does encourage those who need advanced data solutions to move toward SQL Server.

And the topic of running Access as a web or cloud-based solution—right now, you can't run it as part of Microsoft 365 in the browser like you can with Word or Excel. Personally, I prefer the desktop tools, but I know many of you want an online version. That would be a huge advance.

To wrap up: Null in Access is never equal to anything, including itself. Always handle it with the special operators and functions provided. Thanks for all your great questions and comments. I really appreciate the interaction and the ideas you send in each week.

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 more info please visit:

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

Quick Queries #60

via Computer Learning Zone News https://599cd.com/QQ60

Chatbot With Memory

via Computer Learning Zone News https://599cd.com/ChatbotWithMemory

Happy Birthday Excel

via Computer Learning Zone News https://599cd.com/HappyBirthdayExcel

Fitness 44

via Computer Learning Zone News https://599cd.com/Fitness44