Fitness 32
By Richard Rost
5 months ago
Building a Weight Plate Calculator with Access VBA
In this Microsoft Access tutorial I will show you how to build a weight plate calculator as part of a fitness database, including creating a table to track the types and quantities of weight plates you own, adding a user-friendly form for inputting your target weight, and beginning the logic for a function that recommends which plates to use based on your available inventory. This is part 32.
Prerequisites
Recommended Courses
Up Next
Keywords
TechHelp Access, fitness database series, weight plate calculator, track fitness, programming logic, plan plates, plate type table, calculate weight plates, recordset, target weight, button event, quantity owned, global module, plate weight, order by descending, load weight, primary key, form design, text box, status box
Subscribe to Fitness 32
Get notifications when this page is updated
Intro In this video, we continue building the Microsoft Access fitness database in part 32 by creating a weight plate calculator. I will show you how to set up a table to track the types and quantities of weight plates, add a form for entering a target weight, and begin the programming logic to calculate which plates to use based on what you actually own. We will cover adding controls to your form, writing event handler code, and constructing the core plan plates function using loops, recordsets, and sorting to ensure the heaviest plates are selected first. This is part 32.Transcript Today is part 32 of my fitness database series, building a database to track fitness, but you do not have to care about fitness. The real value is all the techniques that I am showing you, all kinds of cool stuff.
Today we are going to do some programming logic. We are going to build a weight plate calculator. Now, you might not work out with weights. You might not need help calculating what plates to put on each side. But this is a fun programming experiment. Experience. Whatever you want to call it.
I will be the first to admit, sometimes when I am in the gym, I am listening to Rush, I have the music cranked, I am getting my groove on, I am lifting. I get gym brain sometimes. I am doing, let us say, a bench press. I have to put, I do not know, 135 pounds on the bar. And now also, I am drawing blanks.
All right, we will start with two 45s. So this is a cool little utility that I figured I would build so that you could say, okay, my target is 175 pounds. And then the system tells you, all right, you put this on each side, this side, this side, and so on.
And yes, I know you can do this in your mind. Most people can at least. I can do it when I am not working out and when I am not drinking. But it is a cool little programming exercise. That is the word I was looking for. It is a programming exercise.
We are going to do some loops and some record sets and it is going to be a little more difficult than just typing in a number because you might not own more than a couple of 45 pound plates. So we are going to keep track of how many you own too. Because I only have like a couple of five pound plates. So I would not want the system to say, okay, put six five pound plates on there and, oh, wow, I do not have that many.
All right, let us get into it. Obviously, if you have not watched parts one through 31, go watch those first. And yes, I know we are going a little out of order here. We have not even started the workout stuff. We are still on the food stuff. But this was just as I was actually working out yesterday, I am like, this would be a cool little side project. So we are going to throw this in now.
I was planning on building this eventually when we get to the workout part of it. So we are just going a little out of order, but it is a little different, fun experiment. It will be kind of something fun.
All right. So the first thing we are going to do is create a table to store the weight plates that we have. We will do plate type ID. I was going to put plate ID, but to me, if it is a plate table instead of a plate type table, that kind of says these are individual plates. Like individual products, you have a barcode on that, so plate type works better.
That is our auto number. The plate weight, or as a 45 pound or 35 pounder, this will be a number. I am going to make this a double because I do have 2.5 pound plates. Sometimes if you have to add five pounds to a barbell exercise, you want to put five pounds on the bar, you need to have all plates to do two of them.
Then finally, the quantity owned, how many of these do you have? That will be a long integer because you cannot own half a plate. I mean, you could, you could saw it in half somehow. I would not want to use that plate then.
All right. Save it. PlateTypeT primary key. Yes. Let us throw some data in here. I am going to put my actual data in here. 45. Yes, I actually have 10 45-pound plates. It is a long story. I have had a couple of different gym sets in my life. Whenever I wanted to upgrade, I would sell the bench and stuff like that, but I always kept my plates. So I have a lot of 45 pounders.
35. I have four of those.
25. I have four of those.
10. I have four of those.
Five pounders, I actually have eight of those bad boys.
And 2.5 pound plates, I have four of them.
All right. If you want to put other ones on there, if you have different stuff, you can put on there whatever you want.
Now, let us stick this on the main menu for now. We might move it later because I might do a whole workout sub menu and then probably move the food to a sub menu too. But for now, it is okay sitting here. So we have the food stuff up top here. Food list. Our food groups. Our meal list. Maybe we will make this a little bit taller.
All right. I am going to put on here a box to put the weight on there and then a button to calculate the weights that we need. For now, we will just put the results in the status box.
All right. So form design. Let us grab a text box, drop it here. This will be "weight." We type in the actual finished weight you are looking for. This will be the box where we put the amount and leave a little extra room because we have something special coming after that. We will get to that in a little bit. Maybe part two. We will see.
All right. There is my box to put the weight in. I am here. We are going to name this bad boy target weight. Target weight. Now, I am going to put a default value in there of, let us say, something weird, like 100 pounds so that we do not have to keep typing it in.
All right. Let us stick a button down below it. I just copied and pasted the food groups button. This will be "calculate weight plates." All right. This will be my plate button, what I am going to call it.
All right. So we are going to right click on this guy. Go to build event. We have code build. Let me move this back down here.
All right. So in this button and my plate button click, we are going to basically call a function. We are going to call it plan plates or whatever you want to call it. I do not care. That is going to return a string that will have the stuff in it like I showed in the title slide.
All right. This will all be a string right here that will just status out. So it will say target 170 pounds. This plate, this plate, this plate, this plate.
Okay. So dim S as a string. Status box equals blank. We are going to blank the status box. It just looks better if it is just the only thing on there. Instead of you can see all the previous results like the status box obviously does. Then S equals plan plates and we are going to send into it our target weight.
I cannot type today. Then we will status S and then we will make it green. Okay. And then a beep. And that is it.
All right. That is the easy part. Now we have to write plan plates. I am going to throw plan plates in my global module because I might later on want to use this in different places. I do not know where, but I just like to throw stuff like this in global.
All right. So public function plan plates. We are taking in a target weight as a double. All right. Because they might send, you know, 15.5 or whatever. Like 12.5 would work if you are doing like a single exercise.
Yes, we are going to get to breaking it in half because some exercises you need to break the weight in half because like a barbell you have weight on both sides. Some machines only take one stack of plates. Like I have a leg extension and ham curl thing that you put on the end of your bench. That just takes one stack of plates. So you do not have to divide everything by two. So we are going to make that an option. Eventually, but right now we are going to just focus on just one set of plates.
This will return a string just for your human consumption reading portion.
We are going to need to take that target weight and save it in a variable because what we are going to basically do is loop through the records in that table that we just created. We are going to read them in reverse order because obviously you want to start with the heaviest plates and work backwards. Use all your 45s.
If you are asking for 90 pounds, you use two 40s. If you are asking for 100 pounds, you are going to use two 40s and you have 10 left. So for 90 you use two 5s. If you are doing it double or 110. So we are going to need to take another weight, a copy of that weight basically, and subtract from it as we take off weight.
So we are going to make another variable. So dim load weight. Let us call it as a double. This will be how much weight we still need to, or we still have left. We still need to calculate or whatever.
All right. We will start that off as load weight equals the target weight. As we take weight off of it, we will then subtract from that.
Okay. Now we need to open our table sorted from the largest plates down. Loop through table. Start with largest weight plates.
Now we need our record set. So we need to write "on RS". So dim RS as a record set. Then we have set RS equals currentDB.openrecordset. What is it going to be? Select from PlateTypeT. We do not need to "where" here, just all the records. So order by plate weight descending.
Okay. So give me all the fields. There are only two. You do not really need the ID. If you are dealing with big, giant record sets and you only need one or two fields and there are 30 fields in the table, you will save a little time if you only request the fields you need. If you are dealing with 500,000 records over the internet, just request those fields.
But for small databases like this, I always use the star because it will save you a headache later when you go to request a field in the middle of the recordset loop and you did not put it in the SQL statement, and you cannot figure out why it is not working, and then you realize, oh, you did not use the star. So until I get to the optimized phase of my database building, I just always put the star right there. Unless I know ahead of time this is going to be a giant record set that I am worried about.
There is proper database theory, and then there is 30 years of experience building actual databases, learning what you really should and should not do. I know I have lost a lot of sleep over problems caused by not using that star.
All right. That is where we are going to end it for today, folks. That is your TechHelp video for today. We will pick the rest of this up on Monday and hope you learned something. Live long and prosper, my friends. I will see you next time.
TOPICS:
Creating a plate types table to track weight plates
Defining fields for plate type ID, plate weight, and quantity owned
Entering weight plate data into the table
Placing the plate calculator on the main menu form
Adding a text box for inputting target weight
Setting a default value for the target weight box
Adding a button to trigger plate calculation
Writing event handler code for the calculation button
Calling a custom function to calculate required plates
Blanking and updating the status box with results
Creating the plan plates function in a global module
Passing target weight as an argument to the function
Processing total weight with respect to individual plate quantities
Looping through plate types in descending order of weight
Calculating how many plates of each type to use
Building and using a recordset to access plate data
Ordering the recordset by plate weight descending
Subtracting plate weight from the remaining load iteratively
COMMERCIAL:
In today's video, we are continuing with Part 32 of building a Microsoft Access fitness database, but you do not have to care about fitness to get value from this lesson. We are learning about programming logic by creating a weight plate calculator. I will show you how to set up a table for storing plate types and quantities, build a form to enter your target weight, and prepare the code structure for calculating which plates to use—taking into account the specific plates you actually own. We go over using loops, recordsets, and how to sort your data so heavier plates get used first. 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. What is the primary programming task demonstrated in this part of the video series?
A. Building a diet tracking interface
B. Creating a weight plate calculator
C. Logging workout routines automatically
D. Designing a cardio fitness tracker
Q2. Why is it important to track the quantity of each weight plate owned in the database?
A. To ensure plates are properly colored
B. To prevent the system from suggesting more plates than are available
C. To log purchase history
D. To predict equipment wear and tear
Q3. In the PlateTypeT table, which data type is suggested for storing the plate weight?
A. Text
B. Integer
C. Double
D. Currency
Q4. Why is the function for planning weight plates put into a global module?
A. To keep the main form uncluttered
B. Because global modules are required for all programming
C. So it can be reused in other parts of the database
D. To make the code more difficult to debug
Q5. What is the reason for sorting the plates from largest to smallest weight when planning the required plates?
A. It makes the table look better
B. To minimize the use of smaller plates and maximize larger plates
C. It is required by Access syntax
D. To save storage space in the database
Q6. When creating the PlateTypeT table, what primary key is used?
A. plateID
B. PlateTypeID
C. plate_number
D. weightID
Q7. What is the purpose of the loadWeight variable in the plan plates function?
A. To store the maximum weight the user can lift
B. To keep track of the remaining weight to be assigned to plates
C. To calculate the total weights owned
D. To record the weights used during previous workouts
Q8. How does the system determine which plates to use for a given target weight?
A. It randomly selects plates from the list
B. It starts with the heaviest plates and subtracts from the target weight as it can
C. It always uses the same combination for any target weight
D. It divides the weight evenly among all plate types
Q9. Why is a string returned from the plan plates function?
A. To allow the result to be easily displayed for human reading
B. To store in a numeric field for later use
C. Because only strings can be returned from functions
D. To make status box updates more complicated
Q10. Why is it helpful to use SELECT * in the SQL statement for opening the recordset?
A. It saves time with small databases by including all fields
B. It makes the code less readable
C. It is required to run any SQL query
D. It limits the risk of duplicate records
Q11. What does the status box display after the plan plates function runs?
A. The total number of plates owned
B. The recommended plate combination for the target weight
C. The list of all available plate types
D. The last meal logged in the database
Q12. If a weight plate calculator user selects a target weight that cannot be met with their available plates, what will the system do?
A. Display an error or incomplete solution, depending on implementation
B. Automatically purchase more plates online
C. Suggest alternative exercises
D. Ignore the limitation and suggest impossible combinations
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-B; 8-B; 9-A; 10-A; 11-B; 12-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 continues our series on building a fitness database. Now, even if you are not interested in tracking fitness, there is still a lot to gain from following along because we will be working through a variety of valuable techniques that can apply to many different projects.
In this lesson, we will step through the process of creating a weight plate calculator. While the focus is on managing weight plates for barbell workouts, the real benefit is the programming logic behind it. Whether you are a gym-goer or not, this is a good programming exercise that involves logic, loops, and working with recordsets.
To put things in context, sometimes when I am at the gym with my music turned up, it is not always easy to do the mental math for loading a bar with various plates, especially after a few sets. For instance, if I need to set up 135 pounds for a bench press, I may just go on autopilot with the standard two 45s and a few extra plates. The mental math is not always easy in the moment, so I thought it would be practical and fun to create a utility that lets you enter a target weight and have the system tell you exactly which plates to put on each side of the bar.
Even though most people can do these calculations in their heads, this is really an opportunity to build something useful and get some practice with programming concepts, especially handling situations where, for example, you cannot use more plates than you actually own. If you only own two five-pound plates, the system should not tell you to use six of them.
To get started, I recommend you watch parts one through thirty-one, as we are building on previous concepts, but it is fine if you are here just for the programming ideas. We are doing this a little out of order since we are still working on the food-tracking portion of the database, but I wanted to include this project after it came to mind during a workout.
The first step is to build a table to store your inventory of weight plates. I created fields for plate type ID (which serves as the primary key and is an auto number), the plate weight (recorded as a double so it can handle odd weights like 2.5 pounds), and the quantity owned (which is a long integer because you generally will not own a fraction of a plate). After saving the table as PlateTypeT, I entered my own data: for example, I have ten 45-pound plates, four of each of 35, 25, 10, and 2.5-pound plates, and eight five-pound plates. You can tailor this to match your own equipment.
Once that is done, I add a section to the main menu of the database to work with the plate calculator. Although I might later move this to a submenu focused on workouts, for now, it is fine as part of the main menu alongside the food tracking features.
On the form, I add a text box for entering the target weight and a button labeled "calculate weight plates" to run the calculation. The results of this calculation will be displayed in the status box.
Moving to the programming part, I assign a name to the text box (target weight) and set a default value, such as 100 pounds, to save time during testing. When the calculate button is pressed, the code will call a function (which I named plan plates) that processes the target weight and returns a string listing the plates to use to reach that target.
This function will ultimately take the target weight, loop through the plate inventory starting from the heaviest plates down, and determine the best combination based on what you have available. It will track how much weight is still needed as it works its way through the available plates and subtracts from the remaining total.
An important feature we will add later is the ability to choose between splitting the total weight across both sides of a bar or just one side, since some exercises or equipment only require a single stack of plates rather than balancing the load across both sides. For now, though, we are just going to focus on calculating one stack of plates.
To do this, the plan plates function will loop through the PlateTypeT table, sorting the plates from heaviest to lightest. Even though best practice is to request only the fields you need when opening a recordset, for small databases like this, selecting all fields keeps things simple and avoids errors if more fields are needed down the road.
That brings us to the end of this lesson. We have set up the core data structure and are ready to move forward with the programming logic. In the next installment, we will pick up from here and complete the calculation functionality. As always, 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 Creating a plate types table to track weight plates
Defining fields for plate type ID, plate weight, and quantity owned
Entering weight plate data into the table
Placing the plate calculator on the main menu form
Adding a text box for inputting target weight
Setting a default value for the target weight box
Adding a button to trigger plate calculation
Writing event handler code for the calculation button
Calling a custom function to calculate required plates
Blanking and updating the status box with results
Creating the plan plates function in a global module
Passing target weight as an argument to the function
Processing total weight with respect to individual plate quantities
Looping through plate types in descending order of weight
Calculating how many plates of each type to use
Building and using a recordset to access plate data
Ordering the recordset by plate weight descending
Subtracting plate weight from the remaining load iterativelyArticle In this tutorial, I am going to walk you through creating a simple weight plate calculator as a programming exercise in Microsoft Access, but the techniques involved are applicable to many different scenarios. You do not have to be interested in fitness to get value from this example, because you will learn how to structure tables, design forms, and use VBA to build practical, logic-based tools.
Let's start by thinking about a common problem for anyone who lifts weights. Often, you know the total weight you want to load onto a bar, but figuring out which combination of plates you need, and whether you actually own enough of each size, can be a hassle—especially if you are distracted or thinking about something else. This calculator will let you enter your desired target weight, reference your actual plate inventory, and then figure out the right combination, working from the heaviest plates down and making sure you do not get told to use more of a plate than you have.
To achieve this, the first thing we need is a table in our database to store the types of weight plates you own and how many of each size you have. To do this, create a table in Access called PlateTypeT. The fields should include PlateTypeID (an AutoNumber as a primary key), PlateWeight (a Double so it can handle fractional weights like 2.5), and QtyOwned (a Long Integer). For example, you might enter rows for 45, 35, 25, 10, 5, and 2.5 pound plates, reflecting your own inventory. For each row, enter the weight and the quantity you own.
Once your table is set up and contains your actual equipment, the next step is to add this functionality to your user interface. On your main menu form, add a text box for entering the target weight you want to load onto the bar. Give this text box a meaningful name—such as TargetWeight—and perhaps set a default value so you do not have to type it in every time for testing. Below this, add a button labeled "Calculate Weight Plates." This button will trigger a VBA event that performs the calculations.
For the code behind this button, you will want to call a function called PlanPlates, which will accept the target weight as an argument, run the logic, and return a string summarizing which plates to use. Your button click event procedure might look like this:
Private Sub btnPlate_Click()
Dim s As String
Me.StatusBox = ""
s = PlanPlates(Me.TargetWeight)
Status s, , , "green"
Beep
End Sub
In this code, you first clear any old results from a status message box. Then you call PlanPlates, passing in the target weight the user entered. The result is then displayed and optionally set in green text for visibility.
Now let's look at how to write the PlanPlates function. Because you might want to reuse this logic later, put it in a global module. The function receives the target total weight (as Double) and returns a summary string of the plates to use.
Here's how you can start:
Public Function PlanPlates(TargetWeight As Double) As String
Dim LoadWeight As Double
LoadWeight = TargetWeight
' The rest of the logic will go here
End Function
You need a working variable (LoadWeight) that keeps track of how much weight you still need to allocate as you loop through your plate types. In order to properly allocate plates, you will use a recordset to read the PlateTypeT table, ordered from heaviest to lightest. This ensures you use the largest plates first, which is standard practice in strength training.
Set up the recordset like this:
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM PlateTypeT ORDER BY PlateWeight DESC")
Looping over this recordset, you will check for each plate type: How many of this plate do I own, and how many can be applied to the remaining load weight (on both sides of the bar, if that is relevant)? Subtract the allocated weight as you go.
Note that you might eventually want to handle whether plates are distributed evenly on both sides of a bar, or all on a single stack for machines—this can be added as an option later. For now, simply focus on distributing the plates logically according to your available inventory and the remaining required weight.
As you allocate each plate size, reduce LoadWeight accordingly. When LoopWeight gets to zero (or as close as possible within the available plate sizes), you can summarize your results in a string to return.
This function, when finished, will let you enter any target weight (such as 135, 185, or even odd numbers depending on your plates), and the database will figure out the right combination using your real plate inventory.
To summarize, you have learned to structure a table reflecting your equipment, designed a form for input and triggering the calculation, and you now have a starting point for VBA code to figure out the optimal combination of weight plates. The recordset loop and allocation logic are good programming exercises, applicable to any situation where you need to break down a total using a set of available resources.
This project is both practical if you have a garage gym and instructive if you want to practice database logic in Access or another platform. As you continue, you can refine the logic to handle different types of equipment, finer reporting (such as how many plates on each side), and edge-case handling if you do not have enough plates for a desired total. This modular, step-based approach will let you build robust tools for all sorts of needs, fitness-related or not. |
