Is there anyway that I could get to 1

[Herriot]

Is there anyway that I could get totals from this...

I have a combo box that displays a product. When the user chooses a product the corresponding sizes and quantity are displayed in a list box. What I would like to do is have a text box directly below the list box that displays the sum total of the qty for the chosen stock.

Example:

Prod1:
size qty
10 5
12 6
---
Total 11 < text box

I would appreciate any help offered.

regards

Herriot

 

[JerichoJ]

Ok, I am going to make some assumptions about how your form works since you aren't specific.

I'll assume that you have code in the Combo Box &quot;AfterUpdate&quot; event that sets a new recordsource for the List Box when an item is chosen from the combo box.

So, what I would do is in that same event (Combo AfterUpdate), I would calculate the sum of the Quantities for the product chosen and set the text box to this value.

So, here is some sample code for the afterupdate event of the combo box:

Dim mssql as String
Dim varReturn as Variant

mssql = &quot;Select tblStock.Sizes, tblStock.Qty &quot;
mssql = mssql & &quot;From tblStock &quot;
mssql = mssql & &quot;WHERE ((tblStock.ProductID) = &quot;
mssql = mssql & Me.Combo1 & &quot;&quot;
Me.ListBox1.RowSource = mssql
mssql = &quot;[ProductID] = &quot; & Me.Combo1
varReturn = DSum(&quot;[Qty]&quot;, &quot;tblStock&quot;, mssql)
Me.TextBox1 = Format(Nz(varReturn,0), &quot;#,##0&quot

Notes:
1. Changing the .RowSource property of a listbox causes it to be requeried automatically, so it is not necessary to call the .Requery method after changing the .RowSource.

2. If your key field is a string type, then be sure to make adjustments in the code above to add delimeters (single or double quotes) around the value of Combo1 in both the listbox SQL statement, and the DSum Where clause

 

[Herriot]

Hi JerichoJ

Thanks very much. Your sample code worked a treat.

You are all a very nice bunch of very helpful people and I don't know where I'd be without this resource. I only wish I could upload some knowledge in return.

First Apology
I apologise for not being as specific as I could have been but it was late (over here in the UK anyway) when I posted it. Well that's my defence and I am sticking to it

Second Apology
I am sorry for the really stoopid subject to my post. If you look closely it's actually part of the first line of my post. I pressed the submit button before I noticed I had forgotten to put a subject header in.

Regards

Herriot