Full Version: Dsum function on a subform
UtterAccess Forums > Microsoft® Access > Access Forms
dallr
Hello Developers I want to run a total on a subform which is built from a query using the following function.
Sum(" [qry Order Details]![RentalCharge] ","qry Order Details"," [qry Order Details]![RentalDate]=Date()")
The problem I am having is that it is totalling all records where the date equals today and giving the same total on each subform even though I change records on the main form.
Does anyone know what to do?
Dane
erwardell
You can have a text box in the footer of the subform that would sum up the items and then you would need another text box in the main form to reference it. This way you do not need to use dsum and the total would be dynamic as you add more entries to the subform.
dallr
But how do I get it to sum the entries which are only made with todays date ?
erwardell
How is your form organized? Generally when you have a new order there is a new orderID to go with it and then that OrderID is used to tie the order details to the Main order. This is so that you can have one info like name, address, soforth to the many items that are apart of the order. this is how you do todays date. But if you are looking to get a sum of all orders of todays date then you will have to use a query to get that, but that is generaly used for a report.
dallr
After making the post I gave it some more thought and came up with the idea of using a query, just as you suggested. The query is working fine and giving me the required totals.
However I have a new problem, when changes are made to a record the totals are not update automatically. The are only updated when I close and reopen the form.
Any Ideas
erwardell
So are you using the query in a report or on a form?
dallr
In a form
erwardell
then you would need to do a requery on the textbox that has the totals. The only time that there would be an autoupdate is when you are getting the sum from a textbox in the footer of the subform.
You can put a button next to the textbox, if you are desiring to stay this course, and then on click have it do
text1.requery
That is if your textbox is called text1
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.