DSum Function Question 1

[gepol]

The syntax is: DSum(expr, domain, [criteria])

I want to use the dSum function, and I want to use my forms recordset the domain to be inserted in the query, is this possible? and how?

 

[MajP]

No you can not use the recordset, but you can use the recordsource. So if the form is built on a query "qryMyForm", then

dsum("someField","qryMyForm","someCriteria")

The difference is that the recordset is the set of active records. So if you apply a filter to your form your Dsum is working on the original "source" of the records and not the filtered recordset.

 

[MajP]

If you can better explain what you want to sum, and the criteria we can give you a better answer. There are other ways to sum records on a form with and without the dsum.

 

[MajP]

Here is a way to roll your own Dsum function to work on a recordset

Public Function dSumRecordset(Expression As String, RecSet As DAO.Recordset, Optional Criteria As String = "") As Integer
  On Error GoTo errlbl
  If Not Criteria = "" Then
    RecSet.Filter = Criteria
    Set RecSet = RecSet.OpenRecordset
  End If
  Do While Not RecSet.EOF
     dSumRecordset = dSumRecordset + (Nz(RecSet.Fields(Expression), 0))
     RecSet.MoveNext
  Loop
  Exit Function
errlbl:
  MsgBox Err.Number & " " & Err.Description
End Function

Example to call this from your form:

= dSumRecordset("EmployeeID", me.recordsetclone, "EmployeeID <= 3")

So if you have a filter on the form, this will only return those records with an employeeID <=3 and also not filtered out by the form filter.

 

[gepol]

What I actually want to do, is have a running sum query, as recordset in a form - I did that.

But when I change the recordsource of the form from a list box, the query uses the table(logically) for the domain of the DSum function.

That is why I asked of the domain of the DSum function can be anything other than a table or a query. I tried with code to delete the table and with runSQL to create another table but it takes ages to do so.

Thanks for your answers, I will try to use them and come back to you.

PS.Unfortunately I can't make the FAQ by theAceman1 work so far.

 

[MajP]

This kind of works.

I am assuming you are changing the recordsource, but still summing the same field.

So I put a running sum field on my form. My records are sorted by ProductID

Its control source is:
=getRunSum([ProductID])

In the form's module

Public Function getRunSum(prodID As Long) As Long
getRunSum = Nz(dSumRecordset("UnitsOnOrder", Me.Recordset, "ProductID <=" & prodID), "")
End Function

I can change the query (assuming the records still sorted by ProductID and still summing UnitsOnOrder). And it still sums. I can filter the records on the form and still get the running sum.

The issue is if you change the sort order of the query, you would have to change the criteria.

 

[gepol]

I sum the result of [credit]-[debit], so yes the same fields

No, the queries always have the same sort order which is a date field. I will try your solution tomorrow and come back, thanks once more.

 

[MajP]

BTW, I was unaware you were working with AceMan on the same issue in another thread. His method is more flexible in that is allows you to sort any way you want and does not force a criteria to be passed. I would pursue his solution, and figure out why it is not working for you.

 

[MajP]

Also try not to post separate threads on the same topic, it causes confusion and is not the best way to get a good answer.

 

[gepol]

It's not the same topic actually. This is a DSum topic, with a question that will be usefull to me in other aspects too. Thanks