Fitness 33
By Richard Rost
2 months ago
Calculating Required Weight Plates Algorithm in VBA
In this Microsoft Access tutorial, I will show you how to continue developing the Calculate Weight Plates algorithm for a fitness database, including setting up variables to track plate quantities, implementing a recordset loop to determine which weight plates to use, handling edge cases for unusable remainders, and correcting common coding mistakes such as data type errors and returning results from functions. This is part 33.
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, Calculate Weight Plates, algorithm, plate weight calculation, plate quantity, recordset loop, loop variables, Int function, CInt function, data validation, order of operations, remainder calculation, symmetric checkbox, plate allocation, debug compile, function return value
Subscribe to Fitness 33
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part 33 of my Fitness Database.
We're continuing to work on the Calculate Weight Plates algorithm, where we're figuring out you have to do a hundred pounds, you have these plates, and what do you need for the plates?
Let's get back to it.
Next up, we will need a text variable to store our final results in, so let's add Result as a Text.
Let's start that off; we probably put that outside. We haven't actually started the loop yet, but we'll put it up here. We'll say:
Result = Target & " Target weight " & " pounds " & vbNewLine
I'm going to put a new line after every character just so you see it here. It's going to go "a hundred pounds" and then each one will be on its own line.
Obviously, for those of you not in the US using a proper system like kilograms, adjust accordingly.
Now we need our recordset loop, so we're going to come in here and go:
While Not rs.EOF
I always like to do my pieces of bread first before I put the meat and the cheese in between. Right:
rs.MoveNext
Wend
And we're all done:
rs.Close
Set rs = Nothing
I get that set up first before I start working out what's in the middle of the loop, because then I always forget rs.MoveNext and then I sit there with an endless loop and I have to kill Access, and it's not fun.
Let's get two more variables for the plate weight and the quantity owned, so we'll just call it Plate as Long and QuantityOwned as Long. Then we'll come in here and say:
Plate = rs("PlateWeight")
QuantityOwned = rs("QuantityOwned")
So now in the first loop, we're on the heaviest plate. Figure out how many of this plate we would need.
Another variable to toss that into - let's call it PlateCount as Long.
Now a little bit of mathing:
PlateCount = LoadWeight / Plate
But that could result in remainders, so we're going to Int that.
So if the math comes out to be, you need a hundred pounds and you're doing it with a 45-pound plate, you're going to get two point something as a remainder, so we don't want that remainder. We just want how many whole plates, so Int works great for that.
A lot of people think Int means to convert to an integer - that's CInt. They're two different functions. Int just takes your number and chops off the decimal point.
So now, if we got a hundred pounds and we're doing 45-pound plates, PlateCount is going to be two, but we might not own two of them. You might only have one 45-pound plate left from your Uncle Mike when he joined the Navy.
So now, we need to make sure we can't use more than we actually own. This was something I wanted to build in here because I had this problem years ago, trying to do something similar for a client, not with weight plates, but other products. I would say, OK, we need 5,600 of this product, and they'd say, we only have 15 on hand.
We'll need another variable. Let's call it UseCount, how many you can actually use, as Long.
If PlateCount is greater than QuantityOwned, then UseCount equals QuantityOwned. Otherwise, UseCount equals PlateCount. It's pretty simple: if the number we need is more than the number we have, then use the number we have; otherwise, use the number we need.
If for some reason that quantity is zero, you might want to keep the entry in your table, but it's zero. Actually, we should probably use some kind of data validation for the table and not let them put in negative four. That might mess up the results. That's handled on the data entry side.
I'm assuming here that all the data in the table is good. Of course, never assume for your users, but we're focused now on making this algorithm. We can worry about that stuff later.
When I am building projects, all these things always pop into my head, like what if they put in negative numbers? I don't want to waste time figuring that out now. Always keep a notepad or something on the side, or even put big comments in here like "check this later". I do that sometimes too if I know I'm coming back to this code, but I don't want to be distracted, like I just got distracted talking about it.
Anyways, at this point, add to Result:
If UseCount > 0 Then
Because you might get down to the point where you checked 45s, we need two of those, then you check 35s; if you're doing a hundred pounds, you might not need any 35s, so that'll be zero; we don't need them.
So, if UseCount > 0, then:
Result = Result & UseCount & " x " & Plate & " pounds" & vbNewLine
So, it will say "4 x 35-pound plate" and then a new line.
Now, we need to subtract that much weight from what's left. The LoadWeight (the one we're working on right here) equals:
LoadWeight = LoadWeight - (UseCount * Plate)
UseCount is how many we used of the 45-pound plates. Now looking at this, this will work, but I always like to use parentheses here even though you don't need them. I just did a whole article on my Captain's Log about PEMDAS: order of operations - parentheses, exponents, multiplication, divide, and so on. You don't technically need those parentheses, but they make it more readable. I think math is all about being readable.
You see all these memes on Facebook and other social media sites with these trick questions. The problem is the question is not written clearly. Yes, there's an order of operations and technically we don't need those parentheses, but I'm going to put them there because it makes it more readable:
LoadWeight = LoadWeight - (UseCount * Plate)
The total count of the plates times the plate weight. That's much easier to wrap your brain around.
That's the end of our jelly inside the doughnut, so we're done with our While loop now.
Now we might have leftovers. This catches edge cases, like if someone types in they need 136 pounds. You can't do that with the plates that we have. You get two 45s, etc. You get up to 135, but you can't get that extra pound that's left.
If the LoadWeight, when we get down here, is greater than zero, then we're going to just add it as a remainder at the end:
Result = Result & "Remainder: " & LoadWeight & " pounds not matched" or "not used" or "not possible" - whatever.
That should do it. Save it, Debug Compile once in a while, close it, reopen it, give it a shot, boom. Oh, what did I do?
Um, oh, OK, I see it.
Anybody figure it out? So, it's an easy one once you know what to look for. Pause the video and see if you figure it out.
I was talking to you guys and I said that Result is a Text field, but TextBox is not the right data type for that, so stop that. TextBox is not what it should be. This should be a String. That's why it gave us an error, because you can't set a TextBox equal to that. That has to be another object.
Save it and that will compile. That's not invalid syntax, but that's a runtime error. It's going to say no, sorry.
Debug Compile again and now let's give it a shot. Boom - and we got nothing.
You know why? Again, another error. We forgot to set our Result to the name of the function, so PlanPlates = Result. That'll return that now.
Those are two mistakes I make quite often. All right, one more time - go. Oh look at that, hundred pounds, two 45s and a 10.
How about let's try 120. Go - look at that. Beautiful. Let's try 121. One pound not possible.
Pretty good. Let's try 300 pounds. There we go, now we're talking. Let's try more than I have plates for. So, I've got... how many plates did I say I had? I forgot. 10, so 10 45s. So, let's try 500 pounds. It uses all 10 of them. How about let's try 600. All right, see, now it's going into the 35-pounders.
Perfect. I think our algorithm is good.
Now, the next thing we have to do is divide everything by two. That's not just as easy as that.
So, we're going to add a little Symmetric checkbox here, which I think most of the time it's going to be symmetric. For 175 pounds, you need to know there's one 45-pound plate per side, one 35 per side, and so on.
We're going to do this addition in Part 34, and we'll get to that tomorrow.
So, I'm not doing the whole "next episode at the same bat time, same bat channel", but that's what I do. You're in Part 33 now, so if you've been through this many of them, you've seen it a bunch of times. I save that for the noobs, the people who haven't seen my stuff.
That's going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for Part 34.
TOPICS:
Declaring variables for plate calculation
Initializing the result string for output
Setting up a recordset loop to process plates
Extracting plate weight and quantity owned from records
Calculating required quantity of each plate
Using Int function to get whole plate counts
Comparing needed plates to owned plates
Determining usable plate count for each type
Appending nonzero plate usage to result output
Subtracting used plate weights from remaining load
Handling cases where the requested weight cannot be exactly matched
Reporting remainder weight not matched by plates
Correcting variable data type errors in code
Assigning the result string to the function for output
Testing the algorithm with different input weights
COMMERCIAL:
In today's video, we're continuing with Part 33 of the Fitness Database series, learning about the Calculate Weight Plates algorithm. I will show you how to store your results in a text variable, loop through your available weight plates using a recordset, handle how many plates you need versus how many you have, and update the remaining load. We will also talk about dealing with impossible weights and common mistakes like variable types and function returns. You will see how the algorithm works with different weight combinations, and we'll get ready to add symmetric splitting in the next part. 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. What is the primary goal of the Calculate Weight Plates algorithm discussed in the video?
A. To count the total number of unique plates in inventory
B. To determine which combination of plates is needed to reach a target weight
C. To convert pounds to kilograms for international users
D. To organize plates by color for gym aesthetics
Q2. Why is the Int function used when calculating PlateCount?
A. To convert the weight to a floating-point number
B. To round up the total number of plates needed
C. To remove the decimal portion, keeping only whole plates
D. To format the plate weight for display
Q3. What is the main difference between the Int and CInt functions in VBA, as discussed in the video?
A. Int rounds up, CInt rounds down
B. Int truncates the decimal part, CInt converts to the nearest integer value
C. CInt truncates the decimal part, Int converts to the nearest integer
D. Int and CInt function identically in all cases
Q4. How does the algorithm ensure it does not select more plates than are actually available?
A. By deleting extra plates from the database
B. By capping UseCount to be the lesser of PlateCount or QuantityOwned
C. By skipping plates that have been used before
D. By raising an error and stopping the program
Q5. If there is leftover weight after the loop has finished allocating available plates, what does the algorithm do?
A. Returns an error and stops calculation
B. Adds a remainder message indicating the unmatched weight
C. Ignores the remainder and proceeds as if the match was exact
D. Suggests switching to kilograms for better accuracy
Q6. Why is it important to subtract UseCount * Plate from LoadWeight after allocating plates in the algorithm?
A. To track how many plates are left in the inventory
B. To keep the database up-to-date
C. To calculate the remaining weight that still needs to be matched
D. To increase the overall target weight
Q7. What cause of a runtime error in the video is identified related to the Result variable?
A. Result was declared as an Integer type
B. Result was not initialized before use
C. Result was declared as TextBox instead of String
D. Result exceeded the maximum string length
Q8. According to the video, what is the purpose of using parentheses in the expression LoadWeight = LoadWeight - (UseCount * Plate)?
A. To ensure compliance with database rules
B. To make the math operation more readable and clear
C. Because it is required for correct order of operations in VBA
D. To convert variables to the correct data types
Q9. Why might the data validation for negative plate quantities be left for later and not included within this algorithm?
A. Because negative numbers are always valid for plate counts
B. To allow flexibility for advanced users
C. To focus on the algorithm logic first and handle data entry rules separately
D. Because negative numbers will not affect the result
Q10. What feature does the instructor mention will be added in the next video (Part 34)?
A. Display weights in both pounds and kilograms
B. Handling symmetric loading for distributing plates on two sides
C. Automatic inventory ordering when plates run low
D. Graphical display of plate placement on the bar
Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-C; 7-C; 8-B; 9-C; 10-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 with part 33 of the Fitness Database series. In this lesson, I'm focusing on refining the Calculate Weight Plates algorithm. The purpose of this routine is to determine which weight plates you need, given a target amount like one hundred pounds and the specific plates available in your inventory.
To start, we'll set up a variable to hold the final result text. This will help us display the target weight and then provide a breakdown of which plates to use, each on its own line. For those outside the United States who use kilograms, simply adjust the values accordingly.
Next, I'll establish a recordset loop to go through each plate type available in the database. Before adding any core logic, I always like to set up the beginning and the end of the loop, including advancing to the next record and properly closing the recordset afterward. This way, I avoid common pitfalls like creating endless loops, which can freeze Access and require a restart.
Inside the loop, we'll declare variables to represent the current plate's weight and how many of those plates are actually owned. I'll extract these values directly from the recordset fields. When the loop reaches the heaviest plate, it determines how many of that particular plate are needed for the load.
Of course, it's important to account for how many plates you own. I introduce another variable for counting how many of each plate can actually be used. If you need more plates than you have, you'll only use up to what you own. This is an important feature, since in real-life scenarios you might be short a particular plate and need to make do with alternatives.
It's important to clarify the use of certain VBA functions here. Some people confuse Int and CInt. The Int function merely removes the decimal portion of a value, whereas CInt actually rounds and converts to the nearest integer. In our algorithm, we want to use Int so we only track whole plates. For example, if dividing the load weight by plate weight yields a non-integer, Int will simply chop off the fraction so only fully available plates are counted.
After determining how many of a given plate type can be used, the result string is updated if at least one plate is available. The routine will then subtract the combined weight of those plates from the overall load, and proceed to evaluate the next heaviest plate.
Once this loop is done, if any weight remains that cannot be matched with the available plate sizes, we append a note to the result letting the user know how many pounds could not be matched. This is especially helpful in edge cases, like requesting 136 pounds when the available plates can only total up to 135.
I also discuss some common programming mistakes throughout this process. For example, it's easy to accidentally assign the wrong variable type when declaring the result. Using the String data type for textual results is crucial, as using something like a TextBox reference will generate errors. Another typical slip is forgetting to assign the function's return value at the end. These are examples of minor mistakes that can halt your function and are worth double-checking during development.
To validate the process, I run several test cases. These include using nicely rounded weights, unusual inputs that cannot be matched, and entering higher values to see how the function responds when there are not enough plates in inventory. The algorithm performs as intended, providing clear and accurate results.
Looking ahead, the next enhancement involves splitting the plates symmetrically for loading on both sides of a barbell. Implementing a checkbox to support symmetric calculations is planned for the next lesson.
That wraps up today's session. If you'd like to watch a complete walkthrough with specific step-by-step instructions on everything discussed here, you can find the full video tutorial on my website at the link below.
Live long and prosper, my friends.
Topic List
Declaring variables for plate calculation
Initializing the result string for output
Setting up a recordset loop to process plates
Extracting plate weight and quantity owned from records
Calculating required quantity of each plate
Using Int function to get whole plate counts
Comparing needed plates to owned plates
Determining usable plate count for each type
Appending nonzero plate usage to result output
Subtracting used plate weights from remaining load
Handling cases where the requested weight cannot be exactly matched
Reporting remainder weight not matched by plates
Correcting variable data type errors in code
Assigning the result string to the function for output
Testing the algorithm with different input weights
|
