SubForm Problem

[Curious27]

My SubForm has Multiple TextBoxes, but the there two I need info from for the TextBox in the Footer.
TextBox1 Name - txtComponent
TextBox2 Name - mAvailQty
TextBox3 Name - txtBrass

What I'm trying to do is fill txtBrass with the value of mAvailQty when txtComponent shows Brass
and fill txtBullet with the value of mAvailQty when txtComponent shows Bullet etc.

Any ideas that would point me in the right direction?

[NeoPa]

Hi there.

Welcome to Bytes.com.

You say you have three TextBoxes, yet you refer to four :-S One, or more, is in the Form's Footer section, but you don't share which.

I'm going to assume you have two TextBoxes in your Footer section - txtBrass & txtBullet - and you have a Form shown in Continuous View with Detail section records which have the two TextBoxes called txtComponent & mAvailQty.

I would expect the easiest way to handle this is to have formulas in both the Footer TextBoxes that check the value of txtComponent and shows either blank (if there is no match) or the value of mAvailQty (if there is one).

The value of ControlSource for txtBrass should be :

=IIf([txtComponent]='Brass',[mAvailQty],'')

and for txtBullet should be :

=IIf([txtComponent]='Bullet',[mAvailQty],'')

I don't have anything to test this with so it's untested but see if you can get it to work. Shout if you struggle.

[Curious27]

NeoPa
Sorry for the mix up on the TextBoxes it's been a bad 2 days trying to figure this out because I was trying SQL and VBA. Not good at either one.
Here is my Form structure:
Main Form - fStock
ComboBox - cboCaliber - This lets me select a Caliber and the SubForm fills in all available components.
fStock Form Header - TextBoxes reference the three TextBoxes 7 - 9 in the SubForm Footer which will do a Sum calculation for total Brass etc.
TextBox1 - Reference - txtBrass
TextBox2 - Reference - txtBullet
TextBox3 - Reference - txtPrimer
SubForm - fStockSub
TextBox4 - Component
TextBox5 - mAvailQty
TextBox6 - pAvailQty
SubForm Footer - TextBox7 - txtBrass - Will Sum all Brass
SubForm Footer - TextBox8 - txtBullet - Will Sum all Bullets
SubForm Footer - TextBox9 - txtPrimer - Will Sum all Primers

Your answer for txtBrass works and fills TextBox1 how ever txtBullet does not fill TextBox2. It seems like it doesn't see the component Bullet.
There are no errors and the Form works great it. I will try to add a Sum to your formula for Brass and see if it moves past line one which is Brass because there could be multiple lines for Brass.

Thank you for your help.

[NeoPa]

Hi.

It helps to know that you require the Sum of values across the dataset rather than the specific value of the currently-selected record of the dataset. I get that writing questions is a lot tougher for most people than it might appear from the outside. I find in many cases that once the member has gone through the logic fully enough to write a question clearly then they no longer need assistance, but that's not a trivial matter and certainly a real struggle for many. Nor is it even universally the case of course.

It would help to have a clearer understanding of what circumstances are in effect when the Bullet data is not shown in TextBox2, but if it's because the current record is on a Brass record then that makes perfect sense and doesn't indicate an actual problem. If it happens when a Bullet record is selected then I would look very closely at exactly what value is there and what is expected. I would guess they somehow don't match - possibly in a very small or invisible way.

As for converting from showing the details of the current record to showing the Sum of all records, that should be something like :

=Sum(IIf([txtComponent]='Brass',[mAvailQty],0))

and for txtBullet should be :

=Sum(IIf([txtComponent]='Bullet',[mAvailQty],0))

Again - unable to test so see if you can get it to work, but shout if you struggle.

[Curious27]

Bingo!
All MainForm TextBoxes fill will proper totals.
I put the =Sum on the formula earlier and it didn't work because I forgot the trailing ,0.
Ok...
What I'm trying to do is have this Form and SubForm retrieve the available components for a selected Caliber and with a formula to look at all the component totals then give a number representing the most I can reload. Say the Primers total was 600, Bullets 300 and Brass was 135, then a TextBox would show 135 cartridges could be made for that Caliber.

Thank you.
Any idea on the last calculation, please throw it my way.

[NeoPa]

OK. So I would guess this extra TextBox is to be found in the Footer section too - possibly called [TextBox10]?

The formula for that would, I expect, be something like :

=IIf(IIf([TextBox7]<[TextBox8],[TextBox7],[TextBox8])<[TextBox9],IIf([TextBox7]<[TextBox8],[TextBox7],[TextBox8]),[TextBox9])

Alternatively (as that's a little messy), you could write your own VBA function procedure that took three parameters and returned the smallest one numerically, then call that instead :

=MinOf([TextBox7],[TextBox8],[TextBox9])

[NeoPa]

I suppose I should illustrate what such a procedure might look like in case you haven't done much work in VBA yet.

Public Function MinOf(lngA As Long, lngB As Long, lngC As Long) As Long
    MinOf = lngA
    If MinOf > lngB Then MinOf = lngB
    If MinOf > lngC Then MinOf = lngC
End Function

[Curious27]

I created a TextBox on the MainForm and i'm trying to get the Minimum value of one of three TextBoxes, txtBrass, txtBullet and txtPrimer.

I have read that MIN is only good for two and I have three. I tried DMin in the control source of the new textbox but nothing happens.

=Min(Min(("[txtBrass]", "[txtProjectile", "[txtPrimer]" ))

Is there a way to get a minimum value from the three TextBoxes, their numbers come from the SubForm Footer.

[NeoPa]

Why would you post that after my last two posts. Going off at a tangent is rarely likely to get you anywhere.

I can see that my earlier post worked on getting a maximum by mistake. My error. Your post was clear it should be minimum & I got myself confused in the complication of trying to fit it into an IIf() structure. Nevertheless that's easy enough to reverse. I have updated it to reflect minimum now instead of maximum.

I'll assume that you haven't seen either of my earlier posts and thus won't take offense at your ignoring them.

Please take up from there and if you have a related response then feel free to post it.

NB. I don't believe you will find that using Min() will be at all helpful. They work on an aggrgation basis so they will handle multiple records for each parameter rather than multiple parameters. Excel's version of Min() does the sort of thing you're after but I'm not convinced you could access that from where your formula is being used. Hence my guidance was in a direction that could work for you instead.

[Curious27]

NeoPa
I'm very sorry for that as when I came back to post I did not see your answers until I click on the Post Reply button, my screen blinked and there were your 2 posts. The only thing I could think of was you had them ready to post only if I came back to post something, then it would appear (Real Estate saved).

There was no Tangent you were helping me, Greatly and is very appreciated.

[Curious27]

NeoPa

Thinking about why your 2 Post were not there and why they just appeared when I clicked the Post Reply button. In doing that it refreshed my screen and there were your Post ahead of my question. I was going back and forth, posting and applying your multiple answers to my Form.

[NeoPa]

NeoPa:
I'll assume that you haven't seen either of my earlier posts and thus won't take offense at your ignoring them.

Looks like I was right to assume innocence until proven guilty :-D

To be fair, I've done similar myself where I've been working on a page for a while, so kept an old copy visible, then replied before refreshing to check it was still in the same state. So, apart from the fact that you did nothing wrong anyway - I forgive you. My error really ;-)

I'm glad you were able to get the help you needed anyway :-)

[Curious27]

Thank you NeoPa for all your help. It all works now, when I select a Caliber it now tells me how many cartridges can be made without me going though my stock and manually counting pieces.

Thank you again.

[NeoPa]

I'm very pleased to hear that was helpful and look forward to more questions from you as you learn more and take on greater challenges.

[Curious27]

NeoPa

Would you explain the above code for me, it's the two parts MaxOf - IngA, ingB and ingcC. I see they Integers but my confusion comes from the A, B and C, would they actually be named ingTextBox1 etc.? Then the =MaxOf line is placed in the Control Source of the TextBox that receives the Maximum value. It just looks better then the first formula with the IIf(.

Things to remember.