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.

[NeoPa]

Curious27:
Would you explain the above code for me, it's the two parts MaxOf - IngA, ingB and ingcC.

I'd be happy to :-)

First, I need to explain that my earlier change to switch from MaxOf() to MinOf() was only half done. Apologies for leaving an inconsistent mess but it's been corrected now if you look again.

Let's first deal with the lngA & ingB confusion. Not sure why you've misread the lower-case l as an upper-case I in this case because the font is specially selected to ensure the difference is clear. lng is a three-letter prefix used for the Long Integer type of variable, which is consistent with the following As Long in all cases. What that means is that all these internal variables are Long Integers, so integers but with greater scope than a standard Integer variable.

As for why I use lngA, lngB & lngC instead of lngTextBox1 etc, that is because this is a VBA procedure and not tied to which values are passed to it. You can use it for TextBox1, TextBox2 & TextBox3 - but you, or another part of the same project, could also use the same procedure for three completely different Controls on different Forms, or even three other variables from within your VBA code. lngA, lngB & lngC are internal variables whose scope does not extend beyond the procedure itself. This allows it to be independent of whatever situation it's called from.

I suspect you don't need the internal logic explained but can't be sure so :
Each variable in turn is checked against the smallest so far found and, if it's smaller, it replaces the smallest and proceeds. When done you have the smallest of the three values passed. Simples!

[Curious27]

I did miss read "lngTextBox1, when I copied it into Windows NotePad, it looked like a capital I. Now I know it's an lower case L.
I do get your explanation and now I know more about variables it will be used again in this project.
What calls the function, the =MinOf formula?


Thank you.

[NeoPa]

Hi again :-)

I believe if you look again at post #6 you will get a clearer understanding of where such a procedure would be called from in your scenario.

Shout if that's less help than I think it will be.

[Curious27]

Hi
Duh... I actually answered my own question in Post #15. I just tried it and it works, I now know two different ways of getting a Max/Min answer.

Thank you.

[Curious27]

The Function does work but when the Form is opened the txtMin textbox shows #Type!. When a Caliber is selected #Type! is replaced with the proper value. Is this a Type Mismatch error and if so why is it replaced with the right value when a caliber is selected?

[NeoPa]

Hi.

I'm not sure this needs to be a new thread, as it's all about the same basic requirement, but it will need to include the situation we're in first - in adequate detail - so that we can consider the circumstances without having to trawl through the whole thread picking up relevant details from various different posts in order to understand exactly what you're dealing with.

Nothing springs easily to mind as I would expect such behaviour only when the values you're dealing with rely on a selected record. I don't remember that to be the situation so a resumé of the situation might help direct my thinking.

[Curious27]

In my SubForm's Form Footer I have 3 TextBoxes that sum numeric totals and there are 4 TextBoxes on the MainForm and
three of them "txtBrass, txtBullet and txtPrimer receive the totals from the SubForms Form Footer tBrass, tBullet and tPrimer. The forth TextBox "txtMin" on the MainForm is where you helped me get the Minimum number of the three. Here is the code I placed in it, which works.

=IIf(IIf([txtBrass]<[txtBullet],[txtBrass],[txtBullet])<[txtPrimer],IIf([txtBrass]<[txtBullet],[txtBrass],[txtBullet]),[txtPrimer])

And you showed me a Function that did the same thing but could be used as is for other projects. Sweet.

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

Placed This in the txtMin Control Source.

=MinOf([txtBrass],[txtPrimer],[txtBullet])

And my question was, when the Form is opened the TextBox "txtMin" shows a #Type! in it which goes away when a ComboBox selection is made. It then shows the proper value like it should show but I don't know why or how to fix it.

[NeoPa]

Hmmm. ComboBox? Not sure where that fits in, but otherwise a pretty decent description.

I guess that, until the ComboBox has a selection the data in the SubForm is all empty. That means we must handle that in our procedure, something like the following :

Public Function MinOf(varA As Variant, varB As Variant, varC As Variant) As Long
    MinOf = Nz(varA, 0) 
    If MinOf > Nz(varB, 0) Then MinOf = Nz(varB, 0)
    If MinOf > Nz(varC, 0) Then MinOf = Nz(varC, 0)
End Function

Try that. I expect it to work but obviously can't test it without your exact setup.

[Curious27]

#Type! is still there. I could upload it if needed.

[NeoPa]

Go on then. I'll have a look at it for you and see if I can spot what needs to change where.

NB. I use 2019 persistent licence and not any M365 stuff so hopefully it will load.

Zip it up of course ;-)

[Curious27]

Shouldn't be a problem loading.

[NeoPa]

Right. I've downloaded the file and I can now see why my suggestion for the code won't work. From the picture you should see (If it's too small to see clearly on here then do the same on your system - See Debugging in VBA for how to).

The values being passed through from your Controls are not Nulls but ZLSs (Zero-Length Strings). We just have to update the code to ignore any values that are not numeric where they occur.

Public Function MinOf(varA As Variant, varB As Variant, varC As Variant) As Long
    If IsNumeric(varA) Then MinOf = varA
    If IsNumeric(varB) Then
        If MinOf > varB Then MinOf = varB
    End If
    If IsNumeric(varC) Then
        If MinOf > varC Then MinOf = varC
    End If
End Function

Note that even should a Null be passed, IsNumeric() returns False when passed a Null so all will work as expected.

NB. I also changed the .ControlSource of [tMin] (within the Form Header of [fStock] to =[txtNewMin] and that works fine now too.

[NeoPa]

PS. In case you're wondering - and I hope you are actually - why the varB & varC If statements include two separate Ifs rather than a compound one (If IsNumeric(varB) And MinOf > VarB Then ...), it's bacause were we to try the latter with non-numeric data then the second part would be processed and cause the code to crash. The way we have done it avoids that as that part is never even checked unless we already know it's safe to do so.

[Curious27]

If the ZLSs (Zero-Length Strings) calculate fields are not numeric, what set them to ZLS? Also, should I keep the previous MinOf Function for any future use of numeric values? Just slightly confused because I even set the format type to Standard for each TextBox.

Applied the code and walla!

If the textbox txtNewMin is a ZLS then how would I remove the zero it shows when the form is loaded. The other calculated boxes did the same thing because the SubForm was empty, so I added "IIf(Not IsError" to remove the zero's. Not being picky just trying to learn a little bit.

Thank you

[NeoPa]

Curious27:
If the ZLSs (Zero-Length Strings) calculate fields are not numeric, what set them to ZLS?

They come from the unbound TextBox Controls. When these have nothing in them, they really contain ZLSs. Bound TextBoxes behave like their bound Fields so don't expect the same behaviour.

Curious27:
Also, should I keep the previous MinOf Function for any future use of numeric values?

No. I wouldn't bother. The latest handles all the possible situations whereas the older one crashes under certain circumstances.

Curious27:
Just slightly confused because I even set the format type to Standard for each TextBox.

If you expected that to make a difference then it won't. It merely tells the TextBox how to display numeric values should they contain any. They do not specify it will/should always contain them.

Curious27:
Walla!

Totally unconnected with technology, but that term is from the French and is actually "Voila!". It's a single word now but derives from two which, translated literally, mean "See there". Used by performers and conjurors to invite the audience to be impressed. It's commonly used in English now and spelled exactly the same as in French.

Curious27:
If the textbox txtNewMin is a ZLS then how would I remove the zero it shows when the form is loaded.

The value you show is the value returned from the procedure :

=MinOf([txtBrass],[txtPrimer],[txtProjectile])

MinOf() is designed, currently, to return a Long value. If, instead of a routine to return the lowest value of three provided, as originally specified, you want one that does that when passed numeric items but a ZLS or Null when passed items that aren't, then it has to change fundamentally. Apart from anything else its return value type can no longer be Long (as a Long can never hold either a ZLS or Null).

A Function Procedure to handle that, quite different, specification might be :

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

A version that returned ZLS instead of Null would have the following two lines inserted after line #9 (If the return value isn't set within the procedure then it's set based on its type - so in this case Null for Variant.) :

    Else
        MinOf = ""

[Curious27]

That explains it, and the zero is gone to. I appreciate your time and knowledge when helping me, never got this type explanations any where else.
Just don't want to be a burden.

Thank you

[NeoPa]

It's what we're here for. Having receptive students pay attention is actually gratifying :-)