I want a label or text box on a form to display the summed amount of all entries in a quantity column. I've typed out the code and the SQL but can't get it to work. The closest I've gotten is getting the SQL statement to show up in the text box...

I've put the code in the BeforeUpdate and Enter events of the object...

Here's my code:

Private Sub Txt_NewInv_Ware_BeforeUpdate()

Me.Txt_NewInv_Ware.Value =
    "SELECT SUM Location_Quantity AS [Total Warehouse Quantity] " & _
    "FROM Product_Location " & _
    "WHERE Location_ID = 1;"

End Sub



I am, once again, at a loss...

Is your form set-up as continuos form?
just make an unbound textbox in the footer of the form, and enter this in design view

=Sum(Location_Quantity)





Sal

that returns an error...

A couple of descrepancies:

I don't know if the form is continuous or not. How do I tell?

Does the text box have to be in the footer area?

I also added the table name to the sum string -->
            =Sum(Product_Quantity.Location_Quantity)

Create a separate simple select query that has Product_Location as the underlying table. Call it, say, qryTotWareQuant. In this query have the fields Location_Quantity and Location_ID as destination fields. Save it.

Then in your procedure, put this in your event procedure:
    Assuming Location_ID is a number or:
    Assuming Location_ID is a string.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

That doesn't work either...

When I make the query it writes this SQL:

SELECT DISTINCTROW Sum(Product_Location.Location_Quantity) AS [Sum Of Location_Quantity]
FROM Product_Location
GROUP BY Product_Location.Location_ID;

I tried putting the code you wrote in the BeforeUpdate and Enter events. Neither works...

Sorry, I didn't make this clear enough. Make the query a simple Select query. Don't try and do any counting, summing, or any math in the query... nothing. Just add the table, the fields that I mentioned, and that's it.

What's happening is that you made the query try and do the work and therefore rendered the DCount function useless. In my instructions, the query just lists everything and the Dcount function does the counting.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Just enter exactly what I mentioned earlier on a text box on the footer of the form.
To check if the form is continuous or not just view in design, click properties and under format it will say "Continuous Form" on the "Default View" (make sure the complete form is selected to view the properties)

Now, if you want the count of all items use =Count(Location_Quantity).




Sal

Anubis! If your name is adam and you once went to nchamber email me at [email protected] immediately. If not then nevermind. Sorry if this post is unrelated to the topic, but I'm just trying to find someone I used to know. :)

u can use the Dsum function.

I prefer to use the recordset.
Set rst = currentdb.openrecordset(Select * from table where" _
" Location = 1")
While not rst.eof
   sum= sum + rst!Total
   rst.movenext
End While