Sum Function in Access 2003

[Alaron]

Hello all. I'm having a bit of trouble with Access's Sum function.

I have a Form for distributor orders with a couple of subforms on it. One of the subforums shows the items that were ordered with fields for Model, Number Ordered and Price.

I want to create a box next to that subform that shows the order total.

I set up an expression in the control source to take the sum of Price * Number ordered. It looks like this:

=Sum([Ordered Items].Form!Num_Bikes*[Ordered Items].Form!Price)

But it comes back with an error.

If I leave it as: =[Ordered Items].Form!Num_Bikes*[Ordered Items].Form!Price , The box will show the total for one model at a time, but I need a complete total.

If anyone has any suggestions about what I'm missing, I would appreciate it.

[doctorgonzo]

Do you mean that the subform shows one model at a time, but you want the sum to be over all models (that is, over all of the records in the subform)?

[Alaron]

There are multiple models in the subform. The Order Total box will only show the total for one record at a time.

I attached a screen shot of the form.

The order total is only showing 12000 for the 3 M300 bikes. I would like it to show $29990 for the total of all 5 bikes.

Thanks.

[doctorgonzo]

That's what I thought: there are separate records for each model. You aren't going to be able to do this using VBA functions, or at least I don't know of a way to do this. You will need to create a query that will sum up everything for each order ID, and then display that in the box. Something along the lines of "SELECT SUM(Num_bikes*Price) FROM Order WHERE OrderID=[the order ID]". Of course, replace the table and field names with what you have in your database.

I would put this in code and set it to update every time the subform is updated.

[Alaron]

Thanks for the help. I feel like I'm closer.

I created a query that multiplies Num_Bikes by Price and puts the value in a field named Total. The query runs and creates the correct total but its still for just one model at a time. I can't seem to get all the bikes from one particular ID to sum up.

I then went back to my Order Total box in the Order Form and added a new control source from that query. But now when I check the form, it says "#Name?"

Any ideas?

The attachment shows first the query setup and then the expression for my Order Total.

[doctorgonzo]

I don't think that will work, as it doesn't reference the particular order number that you are dealing with.

Like I said, I would put this in VBA code using ADO to open up that query, read the value determined by the query, and then display it in the box. Setting the query up as the control's data source doesn't seem to work well.

[Alaron]

I'm not familiar with ADO, so I suppose I'll leave well enough alone. This is for an introductory MIS course I'm taking, so that is out of our scope.We had been working only on basic Design View and Wizards rather then actual code. I was just trying to figure it out from personal interest. Thanks for all the help DrG. I'd be grateful for a walkthrough if you can though.

[doctorgonzo]

This is very rough kinda pseudo-code, but this is what it would entail. I would change the control where the data will appear to a label, then do something like this in the "On Change" or "On Update" event for the subform:

Dim recBob as ADODB.Recordset
Set recBob = new ADODB.Recordset
recBob.Open "SELECT SUM(Num_bikes*Price) FROM Order WHERE OrderID=" & Me.[OrderIDInfo], CurrentProject.Connection, adOpenStatic, adLockReadOnly
Me.[LabelForData].Caption=recBob.Fields(0).Value //Note, this may have to be changed to get the right number
recBob.Close
set recBob=Nothing