NeoPa
Here is an update as to how you and I have solved my problem. As stated above I wanted to place a textbox "CanBuild" next to the Group Header "Caliber" to show how many cartridges can be loaded, based on the totals of each component and selecting the lowest amount available.
What I did was create three textboxes in the Component Footer and naming them, txtTBrass, txtTPrimer, txtTProjectile. I made them not visible for the report aesthetics.
Then in the Control Source for each one, I put it's own expression;
-
=Sum(IIf([Component]='Brass',[mAvailQty],0))
-
=Sum(IIf([Component]='Primer',[mAvailQty],0))
-
=Sum(IIf([Component]='Projectile',[mAvailQty],0))
-
Doing this, pulled out each component, Brass, Primer and Projectile's totals. Then in the textbox "CanBuild" I placed it it's Control Source this expression;
- =MinOf([txtTBrass],[txtTPrimer],[txtTProjectile])
Now in "CanBuild" I have the minimum amount of cartridges I can make, based on the component stock levels.
I would also like to thank you for the valuble lesson you taught me on the value of making a Public Function; we named as "MinOf". This was put together to solve a problem I had a couple months back where you told me it would be better to make a Public Function and have it available through out my project rather than writing an expression for the Control Source each time I needed to find a minimum value. Well, here I am calling on that public function and it works like a charm. Appreciated, is the fact that you took the time to teach me the How, Why and Where of this function.
For those interested in that function, I'll place it here.
- Public Function MinOf(varA As Variant _
-
, varB As Variant _
-
, varC As Variant) As Variant
-
If IsNumeric(varA) _
-
And IsNumeric(varB) _
-
And IsNumeric(varC) Then
-
MinOf = varA
-
If MinOf > varB Then MinOf = varB
-
If MinOf > varC Then MinOf = varC
-
-
End If
-
End Function
Again, Thank you.
37
