I am so Damn Stupid... Shoot me now! (Subform Total Problem)

[haavy]

... and another Newbie joins the list.

I have a simple Order Form with a SubForm within it.

On the Subform, I've used a Combo Box (Lookup from another table) with a Text Description in one column and a numeric value in the second column. The intention here is to have the user choose a product in the "Description" field, and have it automatically poplulate a separate numeric field for further calculations... in an unbound text control on the subform.

Everything works, so far... the description automatically calls up the numeric value in the second text control, and I do a calculation on that value to determine a total cost (also in an unbound text control on the subform).

The problem is, that I can't figure out how to perform a Sum on the auto-populated text control.

I've been trying "=Sum([Fieldname])" and then referring to that text control from the main form, but I always get a "#error". Yet using an inputed value, like price, gives me the correct calculation.

Help is much appreciated!

- Grant.

 

[Anauz]

it could be that the value U are trying to sum is a string. Try converting it to an INTEGER before you do the sum.

ie DIM num AS INTEGER

num = field.value

SUM(num)

[This message has been edited by Anauz (edited 03-16-2001).]

 

[haavy]

Anauz,

Thanks... but I know nothing of how to input the code into the Visual Basic editor in Access (2000).

I've tried placing the formula: "=Int([Fieldname].Column(2))" in the control box I wish to Sum, and then attempting to sum that Column in the Footer, but it still fails with a "#Error".

I really appreciate the attempt to help, though.

 

[Alexandre]

I am not clear on what you are summing and where you try to display the result. You ve got a combobox, a unbound textcontrol which takes its value from what was selected in the combo, and a total cost (total of what?) "in an unbound text control on the subform".

Let me make a guess: you want to sum different values successively chosen through the description, and show the total in an unbound field.
In this case, use the afterupdate event of yor combo, to add the last selected value to the previous one:

Me![NameOfTotalTextControl] = Me![NameOfTotalTextControl] + [Me![NameOfValueAssociatedToDescriptionControl]

Sounds a bit artisanal still. I guess that you coud get directly your value associated to the description with Dlookup, and wouldn t need to show it on the form if you wanted.

 

[haavy]

Alexandre,

The Combo box lookup control carries a description for lumber (i.e. name of product), and the associated volume for that product (called from a separate table). The number of pieces and price/piece are manually inputted (price varies depending on market fluctuations), and the total cost is a simple calculation of number of pieces x volume x price.

The initial calculations work (i.e. second value in combo box (the number) multiplies correctly with the inputted values.

... and yet, a sum of the field containing the volume (taken from the combo box) doesn't work. I've tried "=Int([Description].Column(2)", I've tried "=Val([Description].Column(2)" and I've tried leaving it as it was and using the "Int" or "Val" terminology on the footer sum, all with no success.

When I substitute the manually inputted fields for the combo box numbers, the sum works.

With limited understanding of Access Basic, I'm left with only one alternative.

..... hair pulling.

 

[Alexandre]

I understand that your combo is populated from a query ("associated volume for that product called from a separate table").
Then I would suggest that you make your sum directly on the content of the Volume field in the query. Something like:

Public function SumVolume() as Decimal
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDB()
Set rst = dbs.OpenRecordset("SELECT" _
& " Sum(Volumes)" _
& " AS [Total_Volumes] FROM TableTheVolumesAreIn " _
& " INNER JOIN [TableHoldingDescriptions] ON" _
& "[TableHoldingDescriptions].JoinField" _
& " WHERE ( Here is your eventual criteria);")

' Populate the Recordset.
rst.MoveLast
SumVolume = rst![Total_Volumes]

dbs.Close
End Sub

Then : Me![NameOfTotalControl] = SumVolume()


[This message has been edited by Alexandre (edited 03-16-2001).]

[This message has been edited by Alexandre (edited 03-16-2001).]

[This message has been edited by Alexandre (edited 03-16-2001).]

 

[haavy]

I knew there would be programming involved!

I feel like such a nuisance for asking, but if:
Order = name of main table,
Lumberlist = name for the sub table containing the fields "Description" & "FBM_Bundle" (the volume info)
and OrderSub = table containing information about the actual orders (i.e. "Pieces", "Price"), what do I substitute in the code you've provided to get it to work properly?

I really apologize for being such a newbie with Access.. I promise to work diligently at becoming more proficient over time.

Thanks again.

 

[Alexandre]

I would also need you to provide me with the SQL statement (query text) you combo is base on.

 

[haavy]

Alexandre,

Sorry for not getting back to you sooner... The Combobox points to a simple query "Query1" that includes "ProductID" (Autonumber), "Description" (text), and "FBM_BNDLE" (Long Integer, 3 decimals).

I'm going to order an Access 2000 book today (Office 2000 bible doesn't give me much help), so any suggestions are appreciated.

-Grant.