How to use query result in code

[stonward]

Hi Guys,

I can't believe I'm stuck on this - I get the feeling I'm missing something very obvious....

I have a payments table and a subform based upon it. My CustomerOrders table can have any number of payments made against each Order.

When the user opens an (unpaid) order, I need to run a query to find the total payments made (so far) against that order number. No problem there, but how do I get that value 'into' my variable 'totalReceived'? I have tried placing the query's SQL into VBA directly, and also tried creating a recordset based upon the query. All to no avail. Am I going about this the wrong way?!

Thanks for your time (again!)

RPE

[TheSmileyCoder]

There are various approaches that can be used. I presume the payments are in a table such as tbl_Payments, and linked by OrderID. If you want it in a variable, you could use DSum.
Dim TotalReceived as double

TotalReceived=Dsum("Amount","tbl_Payments","OrderID=" & me.txtOrderID)

Where txtOrderID is a textbox containing the orderID. You can replace this with a variable if you desire.

[NeoPa]

Alternatively, if you have a subform control that is already designed to show those payments then a control can be set up to count the occurrences of a particular field (which must be associated with a control) from your subform. You may need to ensure the form goes to the last record then back in order to ensure an accurate count, but it saves running a separate query for data you already have access to.

[stonward]

thanx guys.

Didn't think of dsum....and I like domain functions despite what I've read from some, um, purists...!

Life-savers again!

RPE

[NeoPa]

I expect, like me, those (we) purists have to work with larger Access projects sometimes, and have too much experience of how over-use of the Domain Aggregate functions can slow things down unacceptably not to mention the issue RPE, but ultimately you know your project best, so it makes sense to go with what you're happiest with.

I'm happy as long as I've put the warning in, whether you (as the OP) see it as an issue or not. It means it's there for all to see, so no-one can feel they've not been given the whole story if they later run into problems.

[TheSmileyCoder]

While I agree that all the Domain functions should be used with care, I feel that they also certainly have their place. Inside loops however is certainly not one of them.
Often times it will be faster to include the information as part of the original query, if at all possible.

[NeoPa]

Smiley:
While I agree that all the Domain functions should be used with care, I feel that they also certainly have their place.

Absolutely. I agree. I use them myself in many cases.

I was simply trying to get the points across (that many people - even some quite experienced ones don't realise) that :

1. They are not without cost in performance. Considering the work they do that should be expected, but many of our members probably don't have the time or inclination for such consideration. Hence it's worth putting that info out there.
2. Forms and Reports can use aggregation across groups in a way very similar to doing it within a query. As this doesn't cause the underlying query, or something very similar, to be reprocessed, it's a lower cost option.

Nevertheless, everything in context. Many times one needs totals or other aggregation the overheads are so small anyway that adding a simple Domain Aggregate call is going to be totally unnoticeable as far as performance goes. It's only when you're dealing with data of unpredictable size (EG. an application that keeps growing with continued use.) that it matters.

[TheSmileyCoder]

An often overlooked issue in development is upsizing. How well will your database react to upsizing? Its one thing to have a test environment with maybe 10-200 records per table and a single user, but once you go live with 10.000 items or more, what used to have no discernible performance hit is suddenly dragging the database down to a grinding halt.

I will admit that I have had to revisit many functions and improve their speed, and I do find it quite satisfying that I sometimes find a performance increase (in a single function) of a factor of 10. However it can be hard to test this untill you get that many records in your system. But its a very learning experience to see reaction times drop from 5 seconds to 0.5 seconds.


If you do use domain functions ensure that:

1. You do not use them inside a loop with many iterations
2. You use them on indexed fields
3. You do not use them inside a query (with few exceptions)