Fitness 31
By Richard Rost
2 months ago
Copy Fields Between Tables VBA, Returning New ID
In this Microsoft Access tutorial, I will show you how to copy fields and data from a food table into a food log table while handling different units and calculations, update your code to capture and highlight the ID of the newly added record, reset and update combo box values, and improve form workflow for easier data entry. This is part 31.
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
Recommended Courses
Up Next
Keywords
TechHelp Access, fitness database series, recordsets, food table, food log, field names, units, calories per unit, carbs per unit, fiber per unit, sugar, added sugar, protein, SQL Server, default values, function return ID, combo box focus, update row source, VBA, Access forms, PowerToys snap
Subscribe to Fitness 31
Get notifications when this page is updated
Transcript
This is part 31 of my fitness database series. If you have not watched parts 1 through 30, you have a lot of watching to do before you watch this one. And like I say at the beginning of every one of these videos, this is not just about fitness. It is about building a database. Lots of cool tricks and tips and all that good stuff. So let's get back to it.
Alright, we have our recordsets here that we are working on, and we are working on the cream filling or the jelly filling, which I prefer. I am dependent on the day with my coffee. I kind of prefer cream. We are doing a fitness database here. Why am I talking about donuts? I have had a donut instead of a protein bar.
Let's finish putting this in here. I closed the tables, but let's open them back up again. We have the food table where stuff is coming from. And then we have the food log where stuff is going to. Let's take that one there. There we go.
And yes, the field names are different. I kind of did that intentionally for several different reasons, but here in the food table, it is assumed that is one unit, whatever your unit is. But in the food log table, you might have multiple units of something, like two cups of cereal. I want this to make sure that the developer knows this is per unit because we have to do some multiplications and math.
Now at this point, it is just copying stuff over from description down pretty much. I am just going to grab description, copy, and I am going to go RS log description equals... In this case, where did I go? I lost you. Come here. See, that is the problem, I have this one all wide. Fix that. That is you, like that. Okay, much better. In this one, it is going to be food description.
So this one is going to be RS food... Oh yeah, RS food. I got them backwards, did not I? Yep. You know why? Because in the other video, I had these flipped.
So RS log food description equals RS food description, just like that. Now for the rest of these, it is going to be basically just copy and paste information. But I am going to do this. I am going to put in here like this. We are going to put a bunch of X's on both of these, and then we are going to copy and paste a bunch of them down.
What do we need? We need for the food log, we need to do one, two, three, four, five, six, seven, eight of these. Alright. So copy, two, three, four, five, six, seven, eight. Alright. Good enough.
Now if you arrange these just right, we can do it. Okay. Good.
So next we have calories per unit, copy, paste that per unit, paste, carbs per unit, paste, fiber per unit, paste, sugar, added sugar - that is the deadly one right there - and then protein. I might have one too many. We will check in a minute.
Now we just grab the other ones from the other things. So calories is calories, then fat, carbs, fiber, sugar, added, protein. And then, okay, so we have notes, URL, and is active. We do not need those.
Now, notes is up to you. If you want to copy the notes from the actual food table over to the notes from the log, I am thinking that the notes for the log are something that you can type in for that meal. If you want to copy over the notes, you can.
Looks like I copied one too many over so we can get rid of that one, Control-Y. Like I said, user ID, we are going to take care of in the future. Has eaten should default to no. Quantity should default to one. We can keep these defaults. That is fine.
Keep in mind, by the way, if you are working with SQL Server, because eventually I am going to put this online, you have to make sure you accept those defaults on the server too, or you have to put them in here. So that can be a trick.
Food log notes, that is up to you if you want to copy that over or not. The meal description, we are going to get that from the meal itself. So if you add a meal, it will bring in the name of the meal. So you could say, that was all for lunch. We can group together when we do a report. But if it is just a food item, we are not going to put anything there. We just leave it blank.
I think this is good for now. That is fun with copying fields from tables. Let me resize everything. Snap. I am using PowerToys to snap stuff on my desktop, watch. Ready? Click, snap. See? I love that. Use it all the time. But the code window I like to have over to the side just a little bit, and I make it a little bit smaller, so I can still click on the Access window behind it. But over here to the right, see how I leave it hanging out over here, so I can just click over here. That is how I go back and forth between them quickly.
A lot of people ask me that, how do you do that so fast? You can click on your toolbar on the bottom and the taskbar, but that is actually slower. Those are the habits you pick up from doing this for 20 years.
So back down here after we add it, let us throw in a Me. Not recordset dot requery. And there is that beep every time we hit the recordset dot that will requery the list of stuff that we just added something to. Save it. Debug - compile once in a while.
Come back over here. Let us close it, open it. I am going to get rid of this blank line that I added somehow. Let us add an avocado. Are you ready? Click. There it is.
Pretty cool. Let us check the log, make sure it got everything right. It should be right there. It has the date and time, it has the time format, it has the name, and all the macros are good. Somehow, I think sugar is wrong. Let us see. Avocado - oh wait, it is avocado. I was thinking pear. No, you are right, no sugar.
Now, it would also be nice if that item was highlighted up here. So if I add something else, let us add... I also want to blank this box after the user makes a selection. Let us say they add provolone cheese. And we can optionally close this later because right now, I am over the dropdown. Let us hit add.
I think it would be nice if once you add the item, the focus goes up here so the user can see that that is highlighted. I mean, it is kind of up to you. I like that. But you might also want to blank this and have it here so they can type in the next item too. It all depends on your workflow, how you want to do it. And maybe make this label like the Alt-F. Put the Alt in front of that F there. Not the Alt, the ampersand. Now if you want to use the keyboard anywhere on this form, just hit Alt-F and then put you right there. You can start typing in oil. If you are a keyboard warrior like me.
Oops, someone is beaming in. Let me delete these. So let us put the focus up here on the item we just added. Now in order to do that, we have to get the ID that was just added to the food log table. I am doing this primarily to teach you something. So we have to say, "OK, you just added an item, which item is it?" We cannot just rely on looking at the most recent one, because IDs are not always that way, especially if you are working with SQL Server, especially if you have a multi-user environment, and especially if you are using random autonumbers or something different.
So what we are going to do is, when we add the item to the table, we are going to say, "I need you to return that ID to me." What record you just added, give me its ID. If we are going to do that, then we cannot rely on this being a subroutine. Let us turn it into a function and return that value. Let us change this to a function, and we are going to return a long. So, as long.
This is easier than you think it is, by the way. Right here, after we do the RS log dot add new, at that point we are assigned an ID. This is different in SQL Server, by the way. In SQL Server you do not get that ID until the record is committed. In Access, you get it immediately. So we will have to do some modifications if we decide to use this in SQL Server, but for now, it works just fine.
At this point, I can now say:
AddFoodItemToLog = RSLog.FoodLogID.
Now I have the order number that was just created, and that will get returned as a function. So now we will come down here, and we will Dim NewID as Long, and we will say NewID = ... this is now a function, so do it like that.
Now we can requery. Now we can find that item by saying:
Me.Recordset.FindFirst "FoodLogID = " & NewID
We just added. Now we can set focus and do all that other stuff. FoodDescription up in the top there .SetFocus, and then how about FoodCombo = Null to blank the combo box. Or if you want to have the focus down here, that is up to you. If you are more of a keyboard person or a texting person or whatever.
Save it. I am giving a debug compile. Come out. Let us close, open it. Now let us do Rebels, then add it, and look at that. I am sitting right on it and the box is blank.
Drop it down. Oh, look at that, it did not clear it. We set it to Null, but we did not reset the lot to do that too. When we set this value to Null, we did not update the row source. That should be an easy fix. But also, we have to take it... see, I do this a lot. A lot of times you find you have to call stuff from somewhere else.
So you could call FoodCombo_Change if you want to, but I do not like doing that because later on you might add more stuff in here that is not related to that. So I am going to say, update FoodComboRowSource as a sub. And we will make this guy Public Sub, because other forms might need it. This is where you might have other stuff. I think I might want the FoodCombo dropdown just up here so that it is not triggered if I call this from somewhere else, that I am not always dropping that box down unless I am manually changing it.
So now I can take this and put it in this button's code. After we do this, FoodCombo = Null, update the row source. See what I am doing. Save it. Close it, close it, close it. Open it.
Alright, let us get rid of that. And banana. I do not know if I like that staying open like that. Let us see. Add it. Oh, you cannot reference it. This has focus. You book. It has to have the focus.
Alright, that is interesting. I did not think of that. See, this is why I like building this with you. I did not run through all of this ahead of time.
So what is happening is we are trying to update the combo row source, but the focus is not on that box. But what we are doing here depends on the user typing into that box. So what we are going to do is instead, we are going to move some of this stuff. We are going to move this up here.
So Dim S as String is going to go up here. We are going to set it to FoodCombo.Text. Do our replacement there. Then we are going to say UpdateFoodComboRowSource S. We will send in S as String and we will make that optional, watch: Optional = blank.
So what is going to happen now? If they are typing, S will get sent with whatever text is in the box.If they're not typing, if we're doing it from our automated thing in the button, S will be blank and it will just assume we want this, without a way of string. That should get us around the problem. I don't know. I haven't tested it yet. Let's try it and see.
Save it. The bug, compile over here. Let's close it. Open it. Get rid of that banana. And let's try it. Banana. Add it. Oh, it's a beautiful thing. And this guy has all of its records in it.
Let's type in chicken. And we'll add chicken to the sea. Add it. Look at that. Uh-huh. Looking good. Looking good.
Next up, we're going to tackle the problem of when you pick something and you add it. Let me do this. Let me make this bigger so I can show you what's going to happen here. Get ready.
If we go in here and type in, like, chicken, and then I click on this, when I make that selection, I don't want the box to stay open. I want the box to close. In fact, I want to put the focus on this button here.
We'll do that in the next class. I'm not saying tomorrow, probably tomorrow. Tomorrow is going to be Wednesday the third. So yeah, we'll do one more class tomorrow. Then Thursday, I might do something different because I'm going to try to, like I said, do one different class in a week. Then we got Quick Queries Friday. So we'll get to it soon, and it's tomorrow or the next day.
Members, I'm going to keep recording, so you're going to watch it right now. But that's going to do it for part 31, folks. Post a comment down below. Let me know what you think.
I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow.
TOPICS:
Copying data between food and food log tables
Handling different field names between tables
Calculating per-unit nutritional values
Setting default values for new log entries
Retrieving the ID of a newly added record
Converting a subroutine to a function to return values
Requerying and highlighting newly added entries in a form
Clearing and resetting a combo box after adding an item
Updating the combo box row source dynamically
Using parameterized subroutines to update UI elements
Managing focus control after adding records
Testing and troubleshooting user input workflows
COMMERCIAL:
In today's video, we are continuing with part 31 of the fitness database series. You will learn how to move data from the food table into the food log, including handling fields with different names, copying food information, setting sensible defaults, and making sure data updates correctly for each user entry. I will show you how to highlight newly added log items, reset form controls, and use VBA functions to get the ID of the recently added record for better form navigation. There are also tips for optimizing the workflow whether you are using a mouse or keyboard. You will 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 are the field names different between the food table and the food log table?
A. To confuse the developer for security purposes
B. To ensure each table stores unique types of data
C. To emphasize that calculations in the log table are per unit and to signal that some math operations need to be done
D. To allow the user more customization when entering data
Q2. When copying data from the food table to the food log, which of the following fields is intentionally left with a default value and not copied directly?
A. Calories
B. Description
C. Quantity
D. Protein
Q3. In Access, when using a recordset to add a new record, when is the new record's ID available?
A. Immediately after .AddNew is called
B. Only after the form is closed and reopened
C. Only after the database is compacted
D. After the record is committed, but not before
Q4. If you want to copy "notes" from the food table to the food log, what consideration does the instructor mention?
A. Notes must be encrypted first
B. You should always copy them, no exceptions
C. It is optional, depending on whether log notes are intended for per-meal notes or not
D. Notes cannot be copied between tables
Q5. What is a key workflow improvement suggested after adding a food item to the log?
A. Automatically navigate to a report form
B. Blank the combo box and/or set the focus back to the relevant control, such as the combo box
C. Print the item details to the printer immediately
D. Send an email confirmation
Q6. Why is it important to return the specific ID of the newly added food log record when adding a new item?
A. Because you can always guess it based on prior entries
B. Because the most recent entry may not always be the one just entered, especially in multi-user or SQL Server environments
C. Because Access deletes duplicate IDs automatically
D. Because IDs are always sequential and can be skipped
Q7. What function does the user-defined UpdateFoodComboRowSource serve in this process?
A. It automatically commits all records to the server
B. It updates the combo box's list of options, optionally based on a string being typed, solving focus issues when refreshing the dropdown after an add
C. It backs up the current combo box source to another table
D. It sets all combo values to null
Q8. When modifying the code to make AddFoodItemToLog a function instead of a sub, what key advantage does this provide?
A. It allows the recordset to be reused without closing
B. It lets you return the newly created record's ID to the caller
C. It increases the maximum number of fields allowed per table
D. It makes the process suitable for non-relational databases
Q9. In the video, what is suggested as a keyboard shortcut improvement for accessing the combo box?
A. Use Ctrl-Alt-Del for the combo box
B. Add an ampersand before the F in the label so Alt-F sets focus to the combo
C. Name the box "Shortcut" so it auto-highlights
D. No keyboard shortcut is suggested
Q10. Why is copying and pasting field assignments when building recordset-based automation suggested in the tutorial?
A. It is faster than using wildcards and safer since field names may differ
B. It ensures automation never fails regardless of table structure
C. It fills every field with placeholder Xs as a design rule
D. It moves all data into a log regardless of validation
Q11. What is the potential issue with just relying on the most recently added record to find the new item after adding it to the log?
A. Network latency might reorder the records
B. Other users might have added a record at the same time, so the most recent is not always yours
C. Access will automatically delete the last record you add
D. Food log IDs may sometimes reset
Q12. If you need to ensure certain default values are set when adding a new food log record (like HasEaten or Quantity), where do these defaults need to be enforced when moving your solution to SQL Server?
A. Only in the Access client
B. Only at the report generation stage
C. In both Access and on the SQL Server side
D. They cannot be set in SQL Server
Q13. According to the tutorial, how does the instructor recommend managing rapid workflow between forms and windows when developing in Access?
A. By closing Access each time a change is made
B. By using PowerToys or similar tools to snap windows for fast switching
C. By always working maximized without switching windows
D. By exporting forms to other applications
Answers: 1-C; 2-C; 3-A; 4-C; 5-B; 6-B; 7-B; 8-B; 9-B; 10-A; 11-B; 12-C; 13-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 continues my fitness database series. If you are just joining in, I highly recommend watching parts 1 through 30 first, since each lesson builds on the previous one. Remember, even though these videos use a fitness database as the example, the core concepts are all about practical database design and development, and I cover a lot of useful tips and techniques that you can apply to other projects.
In this session, I am working with recordsets and focusing on transferring data from the food table to the food log table. The analogy I use is like filling a donut: sometimes it's cream, sometimes it's jelly, depending on your preference, but in our case it's transferring database fields. The food table contains all the base information for each item, and the food log stores the actual foods consumed, including possible variations in quantity or units.
One intentional nuance in the design is the difference in field names between the two tables. For example, in the food table, nutritional values are considered 'per unit' of the base item, such as one cup of cereal. However, in the food log, the quantity consumed could be more than one unit, so it is important to make this clear for future calculations. This structure helps avoid confusion later when you need to perform math on the data, particularly when calculating total calories or macros per meal.
The data transfer at this point is pretty straightforward. For each field that needs to be copied, like description, calories, proteins, and so on, I simply map the corresponding food table fields over to the food log entry. This is usually a matter of copying over fields and making sure they match up correctly.
There are some fields you may not want to copy directly. For example, notes in the log table might be intended for user comments about a specific meal, not just a direct copy of the notes from the food table. Also, some fields such as user ID or 'has eaten' can have default values set at this stage, which is something you should keep in mind, especially if you plan to later migrate to SQL Server or another database platform.
As I demonstrate, I like to use tools like PowerToys to snap windows on my desktop for easier workflow. This helps keep the code window and Access window visible side by side, which lets me quickly switch between them without using the taskbar.
After adding a food item to the log, I ensure the interface updates correctly. The code re-queries the data to reflect the new entry. I always save and compile the project frequently to catch any possible errors before testing the functionality.
During testing, I notice that it is very helpful for the user's focus to move automatically to the newly added food log entry and clear the combo box for the next item. This way, the workflow feels smoother, especially for users entering multiple items in succession. I explain several interface improvement options, such as automatic highlighting, blanking out input controls, and adding keyboard shortcuts like Alt-F, making data entry faster for experienced users.
A common question I get is how to make sure the correct new record is highlighted, especially in multi-user environments or when using SQL Server. The key here is to capture and return the unique FoodLogID immediately after adding the new record, then search the recordset for this ID to set the user's focus accordingly. In Access, this is pretty straightforward, since the ID becomes available as soon as you call AddNew on the recordset. In SQL Server, be aware that you do not get that primary key value until the update is actually committed, so your approach may need to change.
To implement this, I convert the subroutine that handles the new log record to a function that returns the new primary key. After re-querying the recordset, I search for the new FoodLogID, use SetFocus to move to the right record, and clear the combo box for the next entry. If a user prefers, they can also keep the focus in the combo box, ready to type in the next item.
Sometimes these adjustments reveal new minor issues, like needing to update the combo box's row source after clearing its value. Rather than calling the Change event directly, which I try to avoid for maintainability, I create a new public sub that handles updating the row source, optionally accepting the filter string from the combo box. This gives me better control over how and when the combo box is refreshed.
I walk through fixing a focus issue when trying to update the row source while the combo box is not actually in focus. By refactoring the code to collect and pass in the current text, I ensure consistent behavior whether the update is triggered by user action or by code.
Once these refinements are applied, the interface behaves more as expected: adding a food item automatically clears the entry box and highlights the new log record, streamlining the user experience.
Next, I mention a planned improvement for the upcoming class: closing the combo box and putting focus on the add button or another suitable control after making a selection. This will help further tighten up the data entry workflow. I also share my schedule for more videos, letting viewers know what to expect in upcoming lessons.
That wraps up part 31. As always, I encourage you to leave comments with feedback or questions. For a complete video walkthrough with step-by-step instructions demonstrating everything discussed here, visit my website at the link below.
Live long and prosper, my friends.
Topic List
Copying data between food and food log tables
Handling different field names between tables
Calculating per-unit nutritional values
Setting default values for new log entries
Retrieving the ID of a newly added record
Converting a subroutine to a function to return values
Requerying and highlighting newly added entries in a form
Clearing and resetting a combo box after adding an item
Updating the combo box row source dynamically
Using parameterized subroutines to update UI elements
Managing focus control after adding records
Testing and troubleshooting user input workflows
|
