AI Chef Helper 2
By Richard Rost
2 years ago
Integrate ChatGPT into MS Access for Recipes, Part 2
In this Microsoft Access tutorial, I will show you how to integrate OpenAI (ChatGPT) into your Chef's Kitchen Helper database to generate new recipe ideas based on pantry items directly from Access. This is Part 2.
Members
In the Extended Cut, members will learn how to create an exclusion list so you can say "don't give me the Breakfast Bacon Pancakes ever again." We'll also see how to add special instructions like "mom is visiting this weekend. Vegetarian dishes only, please."
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, OpenAI integration, recipe generation, Access database, Chef's Kitchen Helper, part two, recordset loop, SQL queries, VBA coding, JSON response handling, dynamic recipe suggestions, AI-generated cooking ideas, VBA replace function, Access UI design, Access debug compile, ingredient-based recipes, OpenAI ChatGPT application, Access and AI tutorial
Intro In this video, we continue building the AI Chef Helper in Microsoft Access by integrating the Chef's Kitchen Helper database with OpenAI to generate recipe suggestions based on your available ingredients. I'll show you how to create a button that compiles a list of in-stock products using a record set, filter out items with zero quantity, and format that list to send to ChatGPT. We'll test the output, handle the response from OpenAI, update the user interface for readability, and see how to add new ingredients for more varied results. This is part 2.Transcript Today is part two of my AI Chef Helper video series, where we're taking the Chef's Kitchen Helper database and we're integrating it with some AI so we can send it our list of stuff, and it'll tell us what dishes we can make. So, go watch part one. If you haven't watched part one yet, you'll find a link down below, and then continue with me for part two. We now join the program already in progress.
All right, so in yesterday's class, we got to this point where we got a button. We made a little button here. We're going to ignore this stuff, remember. And this button, we're going to click on it and then we're going to send to OpenAI what our string of products is, and then the instructions from bot 7. What's bot 7? Well, bot 7 is just d-lookuping this stuff here. I'm going to give you the contents of my food pantry blah blah blah. So that's the system message, and the user message is going to be whatever is in the product string. How are we going to get the product string? That's what we're going to make today.
So, to do that, we have to use a record set, loop through all the products in the product table. I'm going to say furthermore we'll ignore any ones that have zero quantity on hand. Just give me all the products that we actually have in stock and put that in a string that we can then send to OpenAI. So, we're going to need a record set. And as I mentioned in part one, go watch the record sets video. If you haven't yet watched that one, well, you're in trouble. You didn't do your homework.
All right. So, we're going to dim RS as a record set. We're gonna come down here and say product string is blank. We're going to start it off blank and then we're going to loop through all the records in the table and just add them to the string, okay?
So, set RS equals current db.open record set. What are we opening up? Now, you could put a query in here if you want if you're not SQL aware and you want to just make a query that doesn't show any products that have a zero quantity, you can do it that way. I'm going to stick to a little SQL. If you're not that familiar with SQL, I got videos on it. Go to my website, search for SQL, you'll find them.
Select star from product T where quantity on hand, that's the field in the table, is greater than zero. It's that easy. And one of the things that I did when I was learning SQL was I would make it in the query designer because I got used to building the queries graphically with the QBE, right, the query by example designer, and then I would just switch to SQL view and say, "Oh, that's how, okay, that's what it looks like. All right, okay." That's how I learned.
All right, now we got the record set open. We're going to loop through all the records and just add them onto that product string. So, while not RS.EOF, end of file, why it's end of file, I don't know, end of the table. And then while end, but don't forget inside here I like to put my RS.MoveNext before I do anything because I don't want to end up with an endless loop. Very bad. And then while we're at it, I like to finish up the stuff here, RS.Close, set RS equals nothing, clean up shop. Now we can go work on the stuff inside the loop right here.
And all we're doing here is we're just adding those fields to the string. We've got product name, quantity on hand, unit type, and we're going to send it in a format that GPT should understand. I'm going to do product name and then a space, and then in parentheses, I'll put "4 each," or "12 each," whatever. And then after each line, I'll put a semicolon. And I've tested it, and GPT seems to get it. The way we've got it set up right now, GPT doesn't like getting blank lines, so don't end it with a new line character. A semicolon should work just fine.
All right, so come back over here. Product string equals product string and RS!QuantityOnHand and a space and let's go to the next line. We'll do RS!UnitType and close parenthesis and a semicolon followed by a space. Okay, you with me? So it's going to look like this: it's going to look like chicken, four ounces; beef, two pounds; and so on. That's what it's going to look like.
I sometimes do this, by the way, with comments. I actually leave that comment in there so that future me can look at that and go, "What is it? Oh, that's what that's doing. OK. Alright. I get it now." Right? OK.
And that's it. Now, before we actually do anything with this, let's take a look right here and see what that product string looks like before we actually send it to OpenAI. Let's just throw in a message box, product string, and then an exit sub. OK. Just want to take a peek at it. Just want to take a look at it. Debug compile. Always a good idea to throw a debug compile on there. Let's save it. Come back out here. Let's close that. Let's close that. Close it. Open it and we'll hit my Chef's Helper button. Boop.
And that's what we got. Okay. That looks good. Chicken breast four each, eggs 12 each, flour four... Okay, good. That looks like it's in a format that I think ChatGPT will understand. So now we're going to couple that with our instructions from bot 7. That's going to get sent to ChatGPT, and we'll get a response back. You ready? Save it. Close it and hit the button.
Takes a second. And now look at that. There we go. Chicken parm with pasta. Oh, it looks great. It looks beautiful. Let me zoom in a little bit and okay members, that was a disclosure first and then you get this stuff okay, now one thing I want to address is this slash. And when you get a response back in the JSON, we talked about JSON in the opening, that slash and backslash is actually a new line character. So, we're going to replace that whenever we see one of those, we're gonna replace it with a new line character to make this a little more readable. Okay, hit okay. Let's change this green too while we're at it.
All right, let's go into here. Back where we were. Now, I'm gonna go into my OpenAI, which is the function that I made in the extended cut. And this basically looks like what you guys have. Same kind of stuff with this request header, we build the send string. It's very similar. I'm going to come right down here and when I get the response text back, I'm going to come in here. I'm already stripping out CHR(10) because you get some of those. I'm going to also strip out, I'm going to say ResponseText equals Replace, the Replace function, ResponseText, we're looking for a backslash N and we're going to replace that on our end with VB newline, Okay.
And I also changed the status back color to just VB Green. Let's make that a little more subtle green, RGB 0, 255, 0. That's a brighter green. You make 255 is the brightest, all 255s is white. I'm going to bring green closer to dark. That's what that means. This was one, it'd be very dark green, but I don't want it that. I just want a subtle light shade of green.
Alright, save it, debug, compile, let's see what we got now. Now the nice thing is here we got chicken parm with pasta, we got spinach and parm frittata, we got pancakes and maple syrup and cereal topping, that's pretty interesting. Now if I hit chef's helper again, chances are it might give you different dishes. Let's do it. So, you don't like the three that it gives you to begin with, try it again, get some more.
Okay, that looks better. Chicken parm with pasta and spinach salad. Spinach and cheese omelet. See, it gives you different ones all the time. And I didn't quite get the green that I wanted. What happened there? It's supposed to be green. Oh, silly me, I went the other way. 0, 255, 0, I want all the green and less of the other two colors, okay. And while we're at it, let's make this a little bit bigger because it looks like it's giving us, I want to be able to see everything in this window here. So we're going to just do like that. We're going to make this a little bit bigger, like so. It doesn't have to be perfect. All right. I just want it to look good. There we go. To me, a good-looking database says a lot. If it looks better, if it's more presentable, your people are going to want to work with it.
OK. You can make yourself a button here if you want to open up your product table. Let's throw some more stuff in here. Let's say I got pepperoni. I got a pound of pepperoni. I got some mozzarella. I got two pounds of mozzarella. I got hot dogs. I got 12 of those. What else do we need? Marinara sauce we got. Eggs we got. Let's see what else. Let's put something else interesting in here. Mac and cheese. Let's see if it handles this. One pound. There are also other different stuff in here. Let's see what we get now. Click.
And boom. Chicken Parmesan Pasta. Baked Spinach and Pepperoni Quiche. Pancake Breakfast Pizza. Enjoy your cooking. That's pretty cool and it gives you how to do it too! This is awesome. Yeah, if you want to, you can go in here and strip these dot these little asterisks out. It uses those to indicate that that should be bolded. That's markdown, but that doesn't bother me. I like that.
So, there you go. That's pretty straightforward. That's pretty much it. If you don't like what it gives you, hit the Chef's Helper button again. It'll send the same list of ingredients and it should give you different dishes. Now, if it keeps coming up with dishes that you know you definitely don't like like the pancake breakfast pizza or the spinach and pepperoni quiche, you want to be able to tell the AI, hey, don't ever give me that again. Well, in the extended cut for the members, we're going to make an exclusion list where you can tell the AI, don't give me these things. OK, we'll cover that in the extended cut for the members.
Silver members and up get access to all of my extended cut videos, all of them, not just this one, all of them. There are hundreds of them now. I've been doing this for years. Gold members can download my databases that I build in the TechHelp videos, which you saw today how much of a benefit that is. If I'm referencing something else, oh, let's just go grab that database and we can work right with it without having to rebuild it from scratch.
And if you like learning with me, come to my website, check out my developer lessons. I've got lots of stuff to learn, tons of stuff on here, hours and hours and hours of viewing enjoyment more than a binge weekend's worth lots of stuff. And in addition to those developer lessons, I'd also have this which I built after the original opening ideas came out so you I didn't mention them I didn't mention this in those videos but I don't access an AI query builder where it will build queries based on the tables and stuff in your database.
You could say to it, "Give me a list of all unpaid orders that are 30 days late," and the AI will write the SQL for that. And the later version of this also does some VBA coding too. So check this out. I'll put a link down below. But don't forget, sign up for a membership for the extended cut for this video and all of my other videos.
And of course, that is going to be your TechHelp video for today. I hope you enjoyed this. I hope you learned something. I hope you enjoyed this little mini-series. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Integration of AI with Chef's Kitchen Helper database
Creating a button to send product list to OpenAI
Utilizing DLookup to fetch system messages
Generating product strings from a record set
Filtering products with zero quantity in SQL
Building SQL queries for non-SQL users
Looping through records in a record set
Concatenating fields to create a formatted string
Testing the formatted string with message boxes
Sending the formatted product string to ChatGPT
Parsing JSON responses from OpenAI
Replacing newline characters in the response text
Changing status back color for display
Repeatedly running the AI function for new results
Implementing a dynamic UI for better presentation
Adding new ingredients to the product table
Viewing and understanding ChatGPT's recipe suggestions
Creating an exclusion list for undesired dishes (extended cut)
Access to extended cut videos for Silver members
Downloading databases for Gold members
AI query builder for SQL generation
Troubleshooting potential endless loops in code
Cleaning up recordsets after use
Visual Basic Applications (VBA) coding integration with AI (later version)Quiz Q1. What is the purpose of integrating the Chef's Kitchen Helper database with AI in the video tutorial?
A. To manage kitchen inventory effectively
B. To calculate the nutritional value of dishes that can be made
C. To send a list of ingredients to the AI and receive dish recommendations
D. To automate the cooking process in a kitchen
Q2. What should the viewer have watched before starting this tutorial for better understanding?
A. OpenAI basics video
B. Record sets introduction video
C. Part one of the AI Chef Helper video series
D. How to create a query using QBE tutorial
Q3. In the video tutorial, how is the record set used in the Chef's Kitchen Helper database?
A. To calculate the total cost of the products in stock
B. To loop through products and create a string of available ingredients
C. To find the most used ingredients in the kitchen
D. To delete products that have a zero quantity on hand
Q4. Why does the instructor use a SQL statement to get the desired record set?
A. To practice his SQL skills
B. To filter products that have more than zero quantity
C. To avoid writing a separate function
D. To demonstrate the query by example designer
Q5. How does the instructor ensure that the loop through the record set does not result in an endless loop?
A. By placing RS.MoveNext at the beginning of the loop
B. By having an Exit Sub command at the end
C. By not allowing quantity on hand to be zero
D. By closing the record set after one pass
Q6. What format does the instructor use to prepare the product string for the AI?
A. Product name followed by quantity with a comma and space separator
B. Product name followed by quantity on hand and unit type enclosed in brackets
C. Product name followed by quantity in parentheses and ending with a semicolon
D. Product name and unit type concatenated with an underscore
Q7. What does the instructor suggest doing with the slash found in the JSON response from OpenAI?
A. Ignore it as it has no significance
B. Replace it with a new line character for readability
C. Remove it using the strip function
D. Highlight it to indicate bold text in the output
Q8. What does the instructor change about the status back color in the code?
A. Sets it to a bright red for errors
B. Chooses a more subtle shade of green
C. Converts it to blue to align with the AI theme
D. Removes color coding entirely for simplicity
Q9. Why does the instructor suggest hitting the Chef's Helper button again if the dish suggestions are not satisfactory?
A. To refresh the database records
B. To send a different list to OpenAI
C. To give OpenAI another chance to return different suggestions
D. To test the AI’s memory and consistency
Q10. What feature does the instructor plan to cover in the extended cut for members?
A. Making the database ingredients list editable
B. Designing a new user interface for the app
C. Creating an exclusion list for unwanted dish suggestions
D. Integrating image recognition for ingredients
Answers: 1-C; 2-C; 3-B; 4-B; 5-A; 6-C; 7-B; 8-B; 9-C; 10-C
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 part two of our AI Chef Helper video series, where we're continuing work on the Chef's Kitchen Helper database by adding integration with AI. The goal is to allow the database to send a list of available ingredients to OpenAI and receive back suggestions for dishes you can prepare. If you have not watched part one yet, I recommend checking it out first to ensure you are up to speed.
Picking up from where we left off, we've set up a command button designed for sending the list of available products to OpenAI. Before doing anything with it, I want to clarify that some earlier elements on the form can be ignored for now, as the focus today is to build the functionality for gathering and formatting the product list.
The button will construct two messages for OpenAI: a system message and a user message. The system message comes from what I call "bot 7," which is fetched using the DLookup function. It's essentially a static instruction to inform the AI how to interpret the data. The user message is the dynamic list of products you have on hand, which is what we'll be working on generating today.
To gather and format this list, the best approach is to use a record set to loop through your products table. Importantly, only products with a quantity greater than zero should be included. This means you're excluding anything that's out of stock. For anyone unfamiliar with record sets, I have a dedicated video on them, so make sure you've reviewed that if you feel lost at any point in this process.
We start by declaring and initializing the record set, then clear the product string that will hold our formatted ingredient list. Next, it's a matter of opening the record set using a SQL statement that selects all records from the products table where quantity on hand is greater than zero. If SQL is intimidating, you could set up a saved query to handle this logic, but I prefer writing the SQL directly, as it offers more control.
Once the record set is open, you loop through all its records while checking for the end-of-file condition to avoid infinite loops. Inside the loop, concatenate the product name, quantity, unit type, and format it in a way the AI can understand, such as "chicken 4 ounces; beef 2 pounds;" and so on. It's helpful to include brief comments in your code about the formatting for future reference.
Before sending this data to OpenAI, it's wise to display the resulting string in a message box just to verify it is formatted correctly. At this stage, you should see a line like "chicken breast 4 each; eggs 12 each;" and so forth. This format has been tested and works well with ChatGPT.
After verification, combine this product string with your system message (fetched using DLookup) and send them to the AI. Upon receiving a response in JSON format, you might notice that certain characters, such as the backslash followed by "n", represent new line characters. These can be replaced using the Replace function so the output appears more readable in your Access form.
To improve the user interface, you can adjust the background color of your status indicator — for example, by setting it to a shade of green using the appropriate RGB values. Modifying form control sizes for better readability is also a good idea, especially as the AI's response may contain multiple lines of suggested recipes.
If you want to test the system with more variety, you can open your product table and add additional items, such as pepperoni or mozzarella. Then, rerun the process to see what new dish suggestions the AI provides. Each click of the Chef's Helper button may yield different results, so if you do not like the first set of suggestions, try again for more variety.
If undesirable recipes keep resurfacing, such as ones you know you do not want to see, you might want to set up an exclusion list. In today's Extended Cut, I will show how to build a feature that allows you to specify dishes the AI should not suggest. This is a valuable addition for making the system truly practical.
Silver members and above gain access to the extended cut for this and all of my other videos. Gold members can also download the source databases used in these tutorials for direct use without the need to build everything from scratch.
For those interested in learning more, I encourage you to check out my library of developer lessons on the website, which covers a wide range of topics in Access, from SQL to automation and more. I have also developed an AI-powered query builder that can write SQL statements and even some VBA code based on your natural language instructions.
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 Integration of Chef's Kitchen Helper with OpenAI
Creating a button to send product list to OpenAI
Using DLookup to fetch instructions for AI
Generating a product string using a recordset
Filtering out products with zero quantity in SQL
Writing and using SQL queries in VBA
Looping through records in a recordset
Formatting product list for AI processing
Testing string output with a message box
Sending a formatted product list to ChatGPT
Parsing JSON response from ChatGPT
Replacing newline characters in AI response
Adjusting form status back color with VBA
Resizing and refining UI elements for usability
Adding new ingredients to the product table
Viewing recipe suggestions generated by ChatGPT
Removing markdown asterisks from AI text output
Troubleshooting endless recordset loops
Cleaning up recordsets after use |
