Fitness 30
By Richard Rost
7 months ago
Escape Double Quotes in Strings, Add Records with VBA
In this Microsoft Access tutorial, I will show you how to deal with double quotes in string values for your fitness database combo box, explain the process of escaping characters to prevent SQL issues, and walk through building a subroutine to automatically add selected food items to your food log. You'll also see how to use recordsets to read from one table and write to another, format text fields using custom functions, and update your forms for improved usability. This is part 30.
Members
There is no extended cut, but here is the file download:
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
Up Next
Keywords
TechHelp Access, fitness database, combo box, double quote escaping, SQL injection, food log, OnChange event, Replace function, recordset, AddNew, Update, currentdb, AddFoodItemToLog, FormatFoodLogTime, FoodID, FoodDateTime, FoodTimeText, public subroutine, button caption, meal collection, loop through records
Subscribe to Fitness 30
Get notifications when this page is updated
Intro In this video, we continue building our Microsoft Access fitness database by addressing how to properly handle double quotes in string data to prevent errors in SQL statements. You'll learn how to escape double quotes using the Replace function in VBA, update the combo box event to handle user input with quotes, and set up a button that adds selected food items to your log. I'll show you how to structure a reusable subroutine for inserting records, use DAO recordsets for reading and writing data between tables, and create a public function to format date and time fields consistently. This is part 30.Transcript Today is part 30 of my fitness database series. And if you have not heard me say this in parts 1 through 29, well, it is a database tutorial. Fitness just happens to be what we are doing. So all these tips and tricks are going to be valid for any database you might be building.
Today, what we are going to do is take this combo box we built yesterday. We can pick an item, click a button, and have this item put up here in the log automatically. We do not want to be typing stuff. That is no.
But before we do that, there is one little issue I want to address first. Let us say in your food list, you have got quotes around something like this: Apple "Red Delicious." Now, the way we have our food logs set up is that shows up there just fine. If you just type in red, it shows up just fine. But what happens if you put in here "red"? Look at that. Even though that is in there, it breaks the string because of the way we are assembling it.
If you come in here and take a look, come back in the OnChange event that we did yesterday. Let me resize this. There we go. What we are basically doing is looking inside the string here, the text in the box, and building a WHERE string out of it. Now, if this WHERE string happens to include a double quote, it is going to break this string. It is going to basically terminate the string and it is not going to work.
So what we have to do is take a normal set of double quotes and turn them into double double quotes inside a string. We basically have to do that here. If the user types in double quotes, we have to change it. It is called escaping it. You have to do this with SQL all the time, especially on websites. You have to watch out for it because hackers can do something called an SQL injection attack. I am not going to teach you all about that now, but Google it. You will see what I am talking about.
Basically, we have to intercept this string here. If there is a double quote in it, make it a double double quote. So I am just going to make another variable up here. We will call it s as a string, ms as equals food combo dot text. Now, s equals replace. It looks like this: it is four quotes here, comma, and then quote, double quote, double quote, quote, there. Yeah, I know. Crazy.
So basically, we are saying if there is a double quote anywhere in the string, I want you to replace it with two double quotes. Since you reference a double quote inside a string with double double quotes, that is what it has to look like. You are basically turning this into this and that whole thing is inside of strings. I know it is confusing. It confuses me sometimes.
Now we just have to take all of these s's and replace food combo dot text everywhere down here with s. That is all we have to do. Right here. I think that is everything. Yep. The rest of it should work great.
All right. Now save it. Debug, compile one time. Oh, replace. Oh, wow. Oh, duh. Anybody see the error? It says argument not optional. What does that mean? Teaching moment because I was talking. We need to put s in here too. You need to put the string you are looking inside of, what to replace, and then replace it with.
Debug, compile. Close it. Close it. Close it. Open it. Now if I come in here and type in red with a quote in front of it, let us say "red," see, it still works because we are escaping that double quote.
So that is taken care of.
One more thing. I like to mention all the things I did off camera so no one gets confused. I made this form wider. This was only out to about here. I just slid everything over more because I was using this myself this afternoon. I thought that is not big enough, so I had to make it bigger.
We have got a combo box. Now we are going to add a little button here that the user is going to click on to put the stuff up top here.
So let us go to Design View. I will grab a button, copy, click, paste, slide it over here. I am just going to put a plus in the button for the caption. Double click right there. Slide it up here. I think someone is beaming in. Hold on. Oh, yeah. We will name this guy. Let us see here. Let us call this the Add Food button BTN.
Right click, build event, comes back in here.
Now, since we are going to be adding multiple food items later when we add meals. What is a meal? A meal is a collection of food items. Since we are going to have to loop through those items in a meal later, let us turn this into a subroutine right off the bat. Here, we are just adding one. But when we add the meals later, we can just loop through this sub we are going to write right now.
We are going to say in here, we are going to just say AddFoodItemToLog, and then we will send it the ID, which this ID happens to be in the food combo.
Now, what is AddFoodItemToLog? Well, we are going to create that right above here. I am going to make it public so anybody can use it. If later on we need to make it global, we can move it into a module. But for now, this is fine sitting here. Let us do that.
Public Sub... Sorry. Copy and paste the name. We are going to send into it FoodID as a Long.
Now, we need two record sets, one to read and one to write. Since I am going to be using multiple record sets in the same subroutine, I am going to declare a database object too. So Dim db As Database.
Then we are going to have rsFood as a recordset. That is going to be from the Food table that I am reading from. Then rsLog as a recordset, and that is going to be the FoodLog table that I am writing to.
OK.
Now let us set up our shell. Let us set up the outside of the donut, and then later on we will put the jelly inside the donut. I always like to put the shell together first.
Set db = CurrentDb. Yes, I have done this so many times I could do it in my sleep. Set rsFood. This is the one we are reading from, equals db.OpenRecordset, and then our SQL statement: SELECT * FROM FoodT WHERE FoodID = the FoodID I sent in.
Theoretically, they are only getting to this place by picking it in a combo box, so there really is not any chance for that not to exist. But just in case, we will use a While loop in case maybe you have a multi-user set up and someone deletes a record or you could check to make sure that the recordset is not empty here, it should not be.
That is what we are reading from. Now, how about the one we are writing to? set rsLog = db.OpenRecordset, and we only need to put in here FoodLogT because we are adding to it.
Technically, we should not need this, but I like to put this loop here anyway. We are going to say: While Not rsFood.EOF, do some stuff, rsFood.MoveNext. Technically, do not need it, but we are going to future-proof in case in the future you decide to send in some different parameters and there could be multiple food items. We might do this with the meal later. I have not decided yet. We could do it here with a loop. As of now, that is fine because, like I said, if for some reason the FoodID does not exist, this will kill it. It will start off at EOF and it will never move and it will never go into the loop.
When that is our While End, now we have to close up shop. So rsFood.Close, rsLog.Close, Set rsFood = Nothing. If you set it, you have to forget it, and Set rsLog = Nothing. That is the end of it. That is the outside of our donut, and now we just have to put in the jelly filling in here.
If you have a hard time remembering how to do all this recordset loop stuff, I do them all the time. I have been doing these for years. I can easily remember all this stuff. But if you cannot, I have it on a mouse pad in the merch store. Here you go. It will be right in front of you the whole time.
Now it is time to put the filling in the donut, which is what we are actually doing. So we are going to add a new record. It is rsLog.AddNew and then toward the bottom, we are going to go rsLog.Update. Add a new record and then update it. We will get rid of some of these blank lines here.
Now, inside the AddNew and the Update, I like to indent inside there. You do not have to, but it just makes things easier to read, I think.
This is where it is also handy to have the field list right in front of your face. What I do is go to the tables, I will put them in Design View, and then maybe drop those into Paint or something. So let us go to the FoodLog we are adding to. Design this. Here are all the fields and you could just leave the table design open. I have to make my window a little bit bigger so I can stretch this out more.
There are all the fields from the FoodLog table and you can copy and paste up to it. They are getting copied from the FoodT design view and there are the fields for that. If you have room, just put them side by side so I can slide these over here. You get your code over here. That is why it is handy to have them both like that, side by side, and you can at least see them, like that, and then put your code window like that. Now you can see here is coming from here going to there.
First thing on the list, the FoodID, well, we sent that in, right? So rsLog.FoodID = FoodID. We just sent it into the subroutine, so that is good.
Now, next is the UserID. I am going to leave that alone for now. We have got a default value of 1 in the table. We will deal with that later. We are going to do a whole set of lessons on the multi-user stuff.
The FoodDateTime, that is an easy one. rsLog.FoodDateTime = Now.
The FoodTimeText, that is a tricky one because the FoodTimeText, if you remember, we have a whole bunch of code that sets that. Where is that located? Let us go find that. That is in here. I believe that is in the AfterUpdate event for this guy. Let us see, Events, AfterUpdate. UpdateFoodDateTimes right here. This is what formats the FoodTimeText into whatever time format you want to see.
Now, I do not want to have this format in two places because if I change it later, let us say I want to change it for manual entry, then I have to change it everywhere else. So let us make our own function that handles that formatting. That way, if I want to change it later, I only have to change it in one spot.
This is the kind of stuff you have to think about. You do not want to duplicate stuff like this, just like you do not want to duplicate code. So right here, let us make a public function. I am going to call it FormatFoodLogTime.We'll send a date time value into it as date, and we'll return a string, which is what's happening down here.
We're formatting this food date time as a string, and we're storing that in the display, this field right here.
So now what we're going to say is format food log time, which is the value of this function that it's going to return as a string, equals that, but now we're just going to put D in here.
So D comes in, it gets formatted, and it gets returned as this string.
So now down here, food time text equals that function. Oops, I got too much in there. This function of food date time, yeah, that works.
So food date time is the actual date time value. We're going to send it up to this function. It's going to return a string formatted like that and put it in this thing here. Same thing, exact same thing as just happened a minute ago.
I didn't change the functionality at all, but now I can call format food log time from my button.
So we're going to copy this and go back where we were before. It's easier just to go back through the button here.
There we go.
All right, add item to food log. I think it's right up here. Yep.
So the next step is that food time text, our s log food time text, equals that function with now in it. Or if you really care about to the second, I would send this value in there, because, I mean, it's only a split second, but there's a chance it could be a different second. If that matters to you, it doesn't matter to me.
See what we're working with here?
So what do we have so far? So far, it's going to add that record and so far, we have the food ID, the date time, and the food time text.
Let's, I like to stop occasionally and just make sure it's working. So debug compile.
Let's close it. Close it. Close it. Save it. Yes. We can leave the, yeah, let's close these for now too because we're going to come back to submit.
Let's make sure it's working. Drop, drop this down. I'm going to pick avocado. I'm going to hit plus.
Now, nothing appeared to happen because we're not refreshing our, we're not requiring our records at all. But if I hit requery, oh, look at that. There's a new item right there. See it? And it is 5:14 p.m. Let's move this down so we can see it better.
And if you look in the food log table, there it is right there. We have the time in there and we have the proper food time text.
Now, we have all these other fields to go. We're going to get that in there in just a minute.
Well, when I say just a minute, it's going to be just a minute for me. But for you, you have to wait till Monday for the rest of it. It's currently Thursday, the 28th of August, 2025. Tomorrow is going to be Quick Queries Friday.
Oh, actually, Monday is a holiday here. Monday is Labor Day. So no video then. So you have to wait till Tuesday, Tuesday, September 2nd. Unless you are a member, and then you're going to watch it right now because I'm going to keep recording for a little while tonight too.
I have a bunch more stuff I want to do. But everyone else, I'll see you on Tuesday.
So that's going to be your TechHelp video for today. Hope you learned something.
Live long and prosper, my friends. Have a nice weekend. I'll see you Tuesday.
TOPICS:
Escaping double quotes in SQL strings
Replacing double quotes with double double quotes in VBA
Using the Replace function to sanitize input
Updating combo box OnChange event to handle quotes
Creating a subroutine to add food items to a log
Setting up and using DAO Database and Recordset objects
Reading from one table and writing to another with recordsets
Structuring a subroutine for future meal entry features
Adding new records to a table with AddNew and Update
Matching source and destination table fields in code
Using Design View to review field lists for coding
Populating FoodID and FoodDateTime fields programmatically
Creating and using a public function to format date/time text
Refactoring code to avoid duplicating formatting logic
Calling a formatting function when adding log entries
Testing record insertion and verifying table updates from VBA
COMMERCIAL:
In today's video, we're continuing with part 30 of the fitness database series, showing you how to handle tricky situations when your data includes double quotes, making sure strings are escaped properly so your searches work the way you expect. You will learn how to update your combo box to pass food items to the log with a single click, and how to set up a reusable subroutine in VBA for adding records. We will look at creating a custom function to format date and time fields so your output stays consistent, and I will show you helpful tips for organizing your database windows to keep things clear as you work. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. Why was there a need to handle double quotes in the food item names?
A. Double quotes would break the SQL string, causing errors when searching
B. Double quotes are not allowed in Access databases
C. Double quotes can cause the application to freeze
D. Double quotes automatically delete records if left unchecked
Q2. What is the process of turning " into "" inside a string called?
A. Escaping
B. Parsing
C. Sanitizing
D. Replacing
Q3. In the Replace function shown in the video, why are "double double quotes" used?
A. To reference a double quote inside a string value in VBA
B. To remove quotes entirely from user input
C. To make the code easier to read
D. To ensure compatibility with Excel
Q4. Why did the initial Replace function fail and show "argument not optional"?
A. A required argument for the function was missing
B. There was an invalid character in the string
C. SQL syntax was incorrect
D. Access was offline
Q5. What is the main purpose of creating a subroutine for adding a food item to the log?
A. To make it reusable for both single items and future collections like meals
B. To reduce file size
C. To enable exporting to Excel
D. To make the form wider
Q6. When adding a food item to the log, what are the two tables involved in using recordsets?
A. FoodT for reading, FoodLogT for writing
B. MealsT for reading, UsersT for writing
C. FoodLogT for reading, MealsT for writing
D. FoodT for writing, FoodLogT for reading
Q7. Why does the code use a While loop when reading from the rsFood recordset, even though only one food item is being added at a time?
A. To future-proof the subroutine for scenarios with multiple items
B. Because it is required by Access
C. To reduce speed
D. To avoid SQL injection
Q8. What is the "jelly filling" of the Donut analogy referring to in this context?
A. The section of code where the actual record fields are set and updated
B. The design of the database form
C. The naming of controls
D. The selection of database tables
Q9. Why did the instructor create the FormatFoodLogTime function?
A. To centralize the logic for formatting date and time strings so it only needs to be updated in one place
B. To increase the speed of database queries
C. To ensure all food items are sorted by date
D. To archive old food log entries
Q10. What is the purpose of the rsLog.AddNew and rsLog.Update commands in the subroutine?
A. To add a new record to FoodLogT and save changes
B. To replace old records with new ones
C. To permanently delete a log entry
D. To requery the database
Q11. Why should you have a list of table fields visible when coding the AddNew/Update logic for the log?
A. To know which fields are available and properly map values between tables
B. To design new forms more efficiently
C. To make the database go faster
D. To check for spelling mistakes in the table names
Q12. What will happen if you click the plus button to add an item and nothing appears at the top of the log right away?
A. The displayed log is not being refreshed; a requery is needed to show the new item
B. The record was not saved to the database
C. The database crashed
D. The combo box is disabled
Q13. Why does the instructor recommend not duplicating formatting logic in multiple places?
A. To make future maintenance and updates easier and less error-prone
B. To make the database more stylish
C. To increase storage efficiency
D. To use fewer variables
Q14. The instructor mentions the potential danger of SQL injection. Why is it important to escape input in SQL?
A. Escaping prevents malicious manipulation of SQL statements by users
B. Escaping increases CPU performance
C. Escaping reduces the size of the database file
D. Escaping is only required in Excel
Q15. The subroutine AddFoodItemToLog can be later modified to allow adding multiple items as a "meal." This demonstrates an important programming concept known as:
A. Reusability
B. Compilation
C. Inline documentation
D. Data encryption
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A
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 30 of the ongoing fitness database series. As I've mentioned several times in previous lessons, while we're building a fitness database, all of the techniques and advice I cover are applicable to just about any database project.
Our main goal today is to enhance the combo box we created previously. Right now, we can select an item from the combo box, click a button, and then have that selected item added to our log automatically. The idea is to eliminate manual data entry as much as possible.
Before we get to the new features, there's a small but important issue to address. Consider a scenario where the food list contains something with quotes, like Apple "Red Delicious." If you simply type in the word red, you'll find it works fine. However, if you include quotes, for example "red", you might notice it breaks the logic because of the way the search string is being assembled.
This all happens in the code behind the combo box's OnChange event. Here, the text entered is taken and used to build a WHERE clause for searching. If the entered text includes a double quote, that quote ends up terminating the string prematurely, which naturally breaks the query.
To solve this, we need to escape any double quotes by converting every single double quote in the string into two double quotes. This is a common practice when dealing with SQL and string manipulation, especially for security reasons. In fact, this type of technique helps prevent SQL injection attacks. If you're not familiar with SQL injection, I recommend looking it up for more context.
Practically, what this means is intercepting the user input string and replacing all instances of a double quote with two double quotes. This way, any quotes entered by the user don't interfere with the query string construction.
So, in the code, rather than working directly with the combo box text, I assign it to a variable, replace all double quotes as needed, and then use this sanitized version everywhere else in that procedure. After making that adjustment, just remember to do a debug and compile to catch any missing arguments or mistakes in the Replace function parameters — a simple oversight can trigger an "argument not optional" error, which just means you forgot to supply necessary arguments in the function.
Testing it out after making the changes, the combo box can now handle input with quotes correctly. If you enter something like "red," the search works as expected because we've escaped the double quotes.
While I'm on the subject of behind-the-scenes tweaks, I also expanded the width of the form a bit. This was simply because I needed a bit more room for data entry during my own use, so I slid everything over to make things more spacious.
With that housekeeping taken care of, let's move on to streamlining data entry from the combo box into the food log. The next step is to add a button near the combo box. When the user clicks this button, the selected food item should be added to the log above.
I create this button in Design View and give it a plus symbol as a caption. Then, I name the control appropriately so it's easy to reference later.
Thinking ahead, since meals in this database will eventually be defined as a set of food items, it's best to put this "add item" logic inside a subroutine right now. This way, when it's time to add entire meals (collections of food items), I'll just loop through and reuse this routine as needed.
The public subroutine will take the FoodID as an argument. I also set up two recordset variables: one for reading from the Food table and another for writing to the FoodLog. I use an explicit database object as well since working with multiple recordsets in the same procedure is easier and more robust this way.
With those basics in place, I set up the outer structure or "shell" of the subroutine. This includes initializing the database and both recordsets, one for reading and one for adding data. Although technically there should only ever be one record returned for a given FoodID, I wrap the reading in a simple loop to future-proof in case we allow for more advanced querying or if the data structure changes down the line.
Once the "donut shell" is ready, I make sure to properly close and dereference both recordsets. I always stress the importance of setting objects to Nothing when they're no longer needed.
Now it's time to fill in the important logic inside that framework — the "jelly filling" of the donut, as I like to call it. Within the loop, I add a new record to the FoodLog using AddNew and then Update.
For ease of reference, I typically keep the design views of both the Food table and the FoodLog table side by side. This makes it much easier to see which fields need to be copied where and catches any small mistakes during development.
The fields that need to be handled are straightforward for the most part. FoodID comes right from the passed-in argument. The UserID can be left at its default value for now, as more advanced multi-user logic will come later. For FoodDateTime, I simply set it to the current date and time.
FoodTimeText, however, is a bit different. In the earlier code, this was formatted inline wherever it was needed. Instead of duplicating that logic all over the place, I create a dedicated function to handle formatting the date and time for display. This public function takes in a date value and returns the properly formatted string. This change keeps the code maintainable and avoids duplicated effort – a small thing now, but a huge help down the road.
Once the logic for adding a food item is set up, I step back for a quick test, compiling and running the database. I try adding a food item and confirm the new entry appears as expected after refreshing the records. In the FoodLog table, the new record displays both the timestamp and the formatted time text correctly.
At this point, only the basic fields are being filled in, but that covers the essentials for now. I will continue to expand on this logic in future lessons, including handling all of the remaining fields.
If you want to see every step of the process in detail, there is a complete video tutorial on my website covering everything discussed here. Be sure to check it out for more hands-on guidance.
Live long and prosper, my friends.Topic List Escaping double quotes in SQL strings
Replacing double quotes with double double quotes in VBA
Using the Replace function to sanitize input
Updating combo box OnChange event to handle quotes
Creating a subroutine to add food items to a log
Setting up and using DAO Database and Recordset objects
Reading from one table and writing to another with recordsets
Structuring a subroutine for future meal entry features
Adding new records to a table with AddNew and Update
Matching source and destination table fields in code
Using Design View to review field lists for coding
Populating FoodID and FoodDateTime fields programmatically
Creating and using a public function to format date/time text
Refactoring code to avoid duplicating formatting logic
Calling a formatting function when adding log entries
Testing record insertion and verifying table updates from VBA |
