ChatGPT VBA Code
By Richard Rost
3 years ago
Have ChatGPT Write VBA Code for Microsoft Access
Hello and welcome everyone to this comprehensive video tutorial! In today's session, we'll be focusing on leveraging the power of Visual Basic for Applications (VBA) within Microsoft Access to automate tasks and manipulate data in a seamless, efficient manner.
Throughout this tutorial, we'll be working with a sample database containing a CustomerT table, which stores customer data, including their credit limits. Our primary goal is to demonstrate how to write VBA code to loop through the records in this table and double each customer's credit limit.
We'll start by guiding you through the process of creating a VBA subroutine to perform this task. Next, we'll illustrate how to add a button to a form in Microsoft Access, which will be used to execute the subroutine. We'll go step-by-step, explaining how to connect the button to the VBA code, and finally, we'll show you how to test the button to ensure that it successfully doubles the credit limits for all customers in the CustomerT table.
By the end of this tutorial, you'll have gained valuable insights into using VBA within Microsoft Access to automate tasks and enhance the functionality of your database applications. So, grab your favorite beverage, and let's dive right in!
Notes
I had ChatGPT write that introduction for me as well as a list of keywords. I asked it for a title, too, and it came up with: "Mastering Microsoft Access: Automate Data Manipulation with ChatGPT-Generated VBA Code."
I didn't like that, so I said: "It's a quick tutorial. "Mastering" is a bit too grandiose. Something simpler? It came up with:
A simpler title for this tutorial could be: "Quick Access Tutorial: Enhance Your Database with ChatGPT-Generated VBA Code." Pretty good! I'll tweak it a little bit and there's the title.
Links
Recommended Courses
Updated Template
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, ChatGPT Microsoft Access help, OpenAI GPT-4 Access tutorial, ChatGPT VBA assistance, GPT-4 Access database automation, AI-generated VBA code, ChatGPT-assisted Access recordset, OpenAI GPT-4 Access form button, AI-powered Access VBA subroutine, ChatGPT Access credit limit update, GPT-4 Access VBA code example, ChatGPT Microsoft Access solutions, AI-assisted Access form design, OpenAI GPT-4 Command Button Wizard, ChatGPT-generated VBA functions, AI-enhanced Access database customization, GPT-4 Access form controls, ChatGPT Access VBA event handler, AI-driven Access data manipulation, OpenAI GPT-4 Access database optimization, ChatGPT VBA best practices
Intro In this video, I talk about using ChatGPT to help write VBA code for Microsoft Access. You'll see how I use ChatGPT to generate a VBA subroutine that loops through records in a table and doubles each customer's credit limit, plus how to add this code to a module and connect it to a button on a form. I also share tips on reviewing AI-generated code, explain where ChatGPT's strengths and weaknesses are, and discuss how you might use it as a learning tool for basic development tasks in Microsoft Access.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to talk about using ChatGPT to write VBA code for Microsoft Access. If you've been following my videos for any length of time, you'll know that I'm absolutely fascinated by ChatGPT. I think it's fantastic.
Up until this point, I've pretty much just been using it to do minor creative tasks. For example, I wanted to add some orders to my order entry system for the TechHelp free template. I used to sit there typing in orders by hand. So, I said to ChatGPT: "Thinking about the rock group Rush, make me a list of witty items from their songs to be found in a gift shop." It was fantastic. For example: a Time Stand Still paint set, The Trees bonsai kit, a Subdivisions jigsaw puzzle. That's crazy.
Then I said: "Okay, thinking about just the guitarist Alex Lifeson, who's known for his witty sense of humor." Even ChatGPT knew about his witty sense of humor. For example, the "Blah Blah Blah" speech bubble t-shirt based on his Hall of Fame speech and just all kinds of stuff.
I've pretty much been using ChatGPT for things like this, and to format text. For example, I give it a list of items and have it laid out in a table format or a CSV. It can do all kinds of crazy stuff.
Yesterday, I decided to test it with some VBA code. I said: "Write Visual Basic code for Microsoft Access to loop through the records in a table, CustomerT, with a recordset and double every customer's credit limit. Loop through all the customers and just double their credit limit." It gave me near-perfect code to achieve this task using VBA code within Microsoft Access.
First, you want to create a module, although you can put it directly in a form, and then copy the following code into the module. The VBA code then creates a subroutine that loops through the records in the CustomerT table and doubles each customer's credit limit. There it is.
I'd make a few minor changes to this, but it's pretty much perfect code. You can copy the code. I'm going to copy it and take it over to my TechHelp free template. Let's open that up. I'm going to go to a module down here and drop that code in, paste it right here. Now it gives you the Option Compare and Option Explicit lines, but if you're creating a new module, you don't need those if you already have them up there, so I'll just get rid of that.
So we have "DoubleCreditLimit" as a subroutine. It's going to set a database object. It's going to set an SQL statement right there to open up the customer table. It's going to create the recordset object. It's going to loop through all the records. It's going to double the credit limit right there, and the recordset is properly formatted and moves to the next record. Close everything up, clean everything up, and give you a message box that says everything is done.
It commented everything. It wrote better code than I usually do. I'd make a few minor changes, like I don't need to specify DAO because it's the default, but aside from that, it's pretty good.
So, "DoubleCreditLimit" is the sub that I created. I'm going to close that and let's drop a button on the main menu here: Design View, Button, drop it there, cancel the wizard. I'm going to right-click on the button, go to Build Event, and right in here we'll just call that code. I can name the button and put captions on, but you get the point. Save that. Close it.
Let's check real quick what some of the credit limits are. So, CustomerT, let's slide to the right and we've got 5,000 and 4,000 for the first two. If I run that, it should double those. Ready? Click. Credit limits have been successfully doubled for all customers in the CustomerT table. Let's double check and see. Open it up, slide to the right, and look at that: 5,000 and 4,000 became 10,000 and 8,000.
So, the code did work.
Then I got to thinking to myself, there are a couple of extra steps in there that I knew because, of course, I'm an Access guy. So I scrolled down and said, "How do I create a button on a form to run that code?" It gave me step-by-step instructions: open the database, go to Form Design, find the Command Button, put it on the form, click, and put it in there just like I did.
I can see eventually ChatGPT putting me out of a job when they get it to the point where they've got an AI talking personality with my sense of wit and humor, then I'll be worried. But if you're looking to write some simple code and you're not exactly sure how to do it, ask ChatGPT. It'll give you the code.
I have heard some reports, because I posted this in my forum, from a few of my students who said that they tried this and ChatGPT was doing some weird stuff. It was giving properties for list boxes that don't exist, for example. Gary Jane says the bottom line is take what ChatGPT gives you with a fistful of salt.
I wouldn't put this in any business-critical systems, but if you're just building a simple database and want to test it out, check it out. If nothing else, it'll give you a template to get started and understand that stuff.
When I was first starting to learn Access, one of the major things that helped me learn, aside from a few books of course, was tearing apart other databases. The Northwind database, the one that comes with Access, is fantastic. It's got a lot of great code samples in it, and they're revising it, by the way, so you'll learn more about that pretty soon.
That's my take on it. I want to hear from you. Have you played with ChatGPT as far as using it with Microsoft Access, whether it's for learning how to do something or writing code? I'm very curious as to what you have discovered with it. So post something in the comments down below.
There's your Fast Tip for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What was the main use of ChatGPT discussed in the video?
A. Writing VBA code for Microsoft Access
B. Designing graphics for databases
C. Generating SQL reports
D. Building web applications
Q2. Before trying ChatGPT for code, what kind of tasks did the instructor mainly use it for?
A. Writing Python scripts
B. Minor creative tasks like generating witty lists and formatting text
C. Creating web forms
D. Editing database relationships
Q3. What did the instructor ask ChatGPT to do with the CustomerT table?
A. Delete customer records
B. Email customers about their credit limits
C. Double every customer's credit limit using VBA code
D. Create a backup of the table
Q4. Which of the following describes how the code generated by ChatGPT performed?
A. It crashed Access
B. It was nearly perfect with minor changes needed
C. It was unusable and required complete rewriting
D. It corrupted the database
Q5. In the procedure described, what is the main purpose of the DoubleCreditLimit subroutine?
A. To add new customers to the table
B. To loop through records and double each customer's credit limit
C. To generate a report of customer credit limits
D. To remove duplicate records
Q6. Where did the instructor suggest placing the generated code?
A. In a new query
B. Directly in the table
C. In a module or within a form's code
D. In a macro
Q7. Why did the instructor mention removing the Option Compare and Option Explicit lines?
A. They were invalid for Access
B. They can conflict if already present in the module
C. VBA does not support those statements
D. They cause syntax errors in forms
Q8. How did the instructor set up the form to trigger the DoubleCreditLimit code?
A. By writing a macro
B. By adding an event procedure to a button on the form
C. By changing the default form property
D. By attaching the code to a label
Q9. What was the result after running DoubleCreditLimit on the sample CustomerT table?
A. Credit limits were halved
B. Credit limits were deleted
C. Credit limits were doubled as expected
D. Credit limits remained unchanged
Q10. What caution does the instructor share about using ChatGPT-generated code?
A. Only use it for graphics design tasks
B. Assume all code is 100 percent accurate
C. Test the code carefully and do not use it in business-critical systems without verification
D. Never use it in Access databases
Q11. What does the instructor say about the Northwind database?
A. It is not useful for beginners
B. It has no VBA samples
C. It is a great resource for learning with useful code samples
D. It is only available in Excel
Q12. What general advice does the instructor give regarding learning from others' code?
A. Avoid looking at sample databases
B. Tearing apart other databases helps you learn
C. Only use your own code, never use examples
D. Focus only on books for learning
Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-C; 7-B; 8-B; 9-C; 10-C; 11-C; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on how you can use ChatGPT to write VBA code for Microsoft Access. I have been thoroughly impressed with the capabilities of ChatGPT so far, especially when it comes to creative and technical tasks.
Previously, I mainly used ChatGPT for minor text-related jobs, such as generating lists of items for my order entry system or formatting information. For one example, I asked it to create a witty set of gift shop items inspired by songs from the rock group Rush. The results were creative and amusing, like a Time Stand Still paint set or a Subdivisions jigsaw puzzle. ChatGPT can even tailor its responses when you specify a particular person, like Rush guitarist Alex Lifeson, offering humorous ideas that show a good understanding of the subject.
Beyond creative lists and formatting, ChatGPT also shines with technical tasks. Recently, I wanted to automate updating my customer records in Access. I asked it to write Visual Basic code that would loop through each record in a table (CustomerT) and double every customer's credit limit. The code it generated was almost perfect right out of the box.
To use the code, you create a module in Access and paste it in. The code defines a subroutine that accesses CustomerT, loops through each customer, and doubles their credit limit value. While I would make a couple of tweaks, such as removing the explicit DAO declaration since it's not strictly necessary, the core logic provided by ChatGPT was correct and well-commented.
After copying the code into a module in my Access database, I added a button to the main menu form and set up an event so the button would run the DoubleCreditLimit subroutine. Before running it, I checked that a couple of customers had credit limits of 5,000 and 4,000. After clicking the button to execute the code, I confirmed that their credit limits updated correctly to 10,000 and 8,000.
I realized there were steps in this process that might not be obvious to everyone, such as how to connect the subroutine to a button on a form. So, I turned to ChatGPT again, asked it for instructions, and it provided a clear, step-by-step outline on how to add and configure the command button to run the code.
This got me thinking about the long-term potential of these tools. If AI ever develops a sense of humor as lively as mine, I might just have to watch my back! But for now, if you find yourself stuck on some basic VBA tasks, I recommend giving ChatGPT a try. It can save you time and help you understand the structure of Access code.
That said, some of my students have reported that ChatGPT occasionally produces code with errors, such as adding non-existent properties to controls. The important lesson here is to treat ChatGPT's output with plenty of skepticism. Check and test anything it gives you, especially before using it for anything business-critical.
For those just starting out with Access, exploring code and database designs, whether from ChatGPT or databases like Northwind, is incredibly valuable. I learned a significant amount about Access by dissecting and experimenting with examples I found elsewhere.
I would love to hear what experiences you have had using ChatGPT to help with Access projects or code writing. Share your stories in the comments.
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.Topic List Using ChatGPT to generate VBA code for Access
Creating a module and adding VBA code
Writing a subroutine to loop through Access table records
Doubling customer credit limits using VBA
Pasting and modifying ChatGPT-generated code in Access
Cleaning up unnecessary lines in a new module
Explanation of Option Compare and Option Explicit
Attaching a VBA subroutine to a button on a form
Creating a button in Design View
Setting up a button's event to run VBA code
Verifying changes to table data after running code |
