Update query not working.

[Whizzo]

Hi all;
Can anybody tell me why the following doesn't work in my update query? I've had a good look around online but there's not much help out there:

nz(DSum("[AcctBalance]","[tblAccounts]","[tblCustomers]![CustID]=[tblAccounts]![CustID]"))

It's in the "Update To:" part of an existing field in the Access query grid. I click the "Run Query" button and nothing happens, the fields aren't updated and there's not even an error message. I even tried this string as a calculated field in a select query (to try it out) and again, blank fields and no error message.

Any ideas?
Thanks!

[NeoPa]

It has no way of determining what you mean by "[tblCustomers]![CustID]" within the DSum() function call.

[Whizzo]

I'd have thought it would interpret "[tblCustomers]![CustID]" as referring to the CustID field in the tblCustomers table, no? Both the tables I'm working on are pulled up in the top half of the query builder, and there's a one-to-many link in place between the two by CustID.

Sometimes it seems to me that Access has stuffed its fingers in its ears and turned the other way!

[NeoPa]

Sometimes it seems to me that Access has stuffed its fingers in its ears and turned the other way!

I'm afraid that says more about your understanding at this stage than Access's shortcomings. I do appreciate that things can get complicated in database work, but this behaviour is perfectly logical.

The place where it falls down is within a DSum() function call. Access (within the DSum() function) cannot reasonably be expected to realise that the parameters passed should be cross referenced with any SQL from near the calling code.

The second parameter is the Domain, and this defines to the function, the scope of where it can reference data. If you need more than one recordset to be included (available) then you need a QueryDef defined that incorporates both, and pass that as the Domain parameter.

I hope this helps you understand the issue a little better.

[ChipR]

If that's in your Update To field, and you aren't getting any results or any errors, what are you selecting the records to update based on?

[Whizzo]

I was hoping that the fields would be updated to the figure returned by the Dsum function but no luck. The exact same string on the On Current event in a form sets the value of a control perfectly, simply using me!control = ... in VB. I was under the inpression that:

DSum("[AcctBalance]","[tblAccounts]","[tblCustomers]![CustID]=[tblAccounts]![CustID]")

in the Update To bit of the design grid would perform at least roughly the same function. i.e. "Add up the AcctBalance figures in tblAccounts where the CustID number is the same as the CustID of this target field." I think I'll just have to scroll through all 35,000 records in the form and let the code in the form do the work! Can anyone recommend a decent book on the subject? Everything else out there seems to be either for pros or simpletons, is there anything that covers the middle ground?

[ChipR]

If you want to use an update query, try the pure SQL approach rather than using DSUM. Make a Group By query on tblAccounts grouped on CustID with CustID and Sum(AcctBalance ). Then write your query to update your other table to the totals in that query.

Your other option of using code would be done much easier by running a function once that opened a recordset and stepped through it, updating each record, rather than having to scroll through all the records in a form.

Come to think of it, why are you storing this calculated value in a table, rather than using the result of the group by query so that it is up-to-date when you need it?

I found Beginning Access 2000 VBA (wrox) to be very helpful starting out, but I'm not sure exactly what subject you are looking for.

[Whizzo]

I'm not calculating this on the fly because the data in the DB is only a snapshot, read-only. The findings will be used to make updates straight to the mainframe somewhere in Denmark. I'm trying to populate a flat table which will generate a report, rather than relying on queries to come up with report data. I found a kind of alternative to scrolling through the records in the end, I put GoToRecord,next on the On Timer event of the form and set it to 20ms. The computer is grunting and straining and the other monitor is a bit of a blur but it seems to be working!

I'll order that book I think, maybe there's one on SQL too. These forums are great for solving immediate problems but to gain a broader understanding it's going to have to be books, books, books!