Cannot get DSUM function to display and then save to textbox in main form. HELP!

[Micron]

I would like to have the sum result stored in the [tblOrders] table's "TotalLaborTime"

I'm surprised that no one has commented on how storing calculated values in a table is generally considered to be very bad practice. In spite of that and my inability to concentrate hard enough on all the comments, if one needs to have a main form control calculate the sum of a subform recordset field, two ways come to mind. 1) DSum the table that is providing the subform records, using the same criteria for the DSum that the subform is using. 2)maybe the original issue is incorrect syntax to refer to the subform? To get the subform recordset count, you'd use

Forms("MainFormName").Controls("subformControlName").Form.Recordset.Recordcount

, so I suspect there's a way to DSum this recordset, though I've never tried.
Maybe

DSum("Forms("MainFormName").Controls("subformControlName").Form.Fields(0)","Forms("MainFormName").Controls("subformControlName").Form.Recordset")

A WHERE clause would not be needed methinks.

I would say the most important thing if trying to get the sum from a subform would be the syntax required to drill down to that subform, BUT I rank the notion that one should NOT store values in a table as more important.

[ssanfu]

I agree with Micron about saving calculated numbers. There are times when it is warranted, but most of the time it is not a standard practice.

I downloaded ver 1.1 of your dB and see that the update command in form "frmLabor" is commented out. The line is

     dbs.Execute sSQL, dbFailOnError

Ensure the line above is not commented out.
Next, I think I missed an "s" in this line : " Forms![frmOrder].Refresh"
The line should be: " Forms![frmOrders].Refresh"


I overlooked another reserved word in two tables: "Description". It is in "tblLabor" and "tblItems". I would use "LaborDesc" and "ItemDesc".

I have been looking at your dB for several hours. It looks to me that you need to take a look at your table structures.

You have:



I would have:

but that creates a lot of other changes. Note that I use a suffix of "_PK" for primary key fields and "_FK" for foreign key fields.

I never use calculated field in tables.
You should really have two fields for client name. (storing multiple "things" in one field violates 1st normal form)
You might have another table for the additional contacts (storing multiple "things" in one field violates 1st normal form)
My tables all have autonumber fields as PK fields.
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

I also have queries for my form record sources, never tables.
If you want to see what changes I made to your db, let me know......

[Micron]

I also have queries for my form record sources, never tables.

This has never resulted in having a query behind the form which was not updatable? As you know doubt know, the list of reasons for this possibility are numerous (here's one of my favourite links on the subject).

I would agree that most of the time it makes sense to use a query, even when the form is based on only one table. Why load all of the fields in a form if you only need a couple of them? I could have included "and all the records" in that statement, but arguably one could apply a filter to that table when opening the form. I see no reason to not at least sometimes base a form on a table but I would welcome anyone's take on why not since I don't pretend to know everything about Access. Take db system settings such as user data for example. This might be a case where you want all of those fields for all 20 of the users (ergo, a small data set) so I see no reason to add a query layer to the table edit process. Be that as it may, if one dismisses what can be thought of as simply designer preference, we're left with the notion that you have never had a situation where the query was not updatable. While no doubt true, I thought it prudent to mention the possibility for the benefit of the OP and others.

[Johnw]

Wow you guys, thanks so much for spending the time to analyze my db and suggest things to fix!
I have been spending a lot of time trying to resolve the issue. There were quite a few things that I missed but I think that I have corrected many of them.
I will go through your suggestions asap but it might take me a while, learning as I go...

The total labor time now shows in the main Orders form! Yeah! One of the simple but stupid things that I corrected was that the Dsum expression worked fine when running VBA in step-by-step but it still would not display in the main form's TotalLaborTime text box. I later discovered that the Dsum values had been stored in the tblOrders table but that the data source in my text box on my frmOrders had to be set to tblOrders.TotalLaborTime, not just TotalLaborTime.
discovered that I had assigned the text box's data source to a field of that name located in the main form.

One of the other things that I corrected were incorrect relationship links (I believe). Previously I had primary key fields for each of my tables tblLabor, tblItems, tblClients & tblContracts that were linked to fields with the same name in my tblOrders. These were set as one-to-many but without referential integrity. I changed these relationships by deleting their previous primary fields and adding an OrderID field to each of the tblLabor, tblItems, tblClients & tblContracts tables. I did not set these new OrderID fields as primary keys. I then created new one-to-many relationships with referential integrity between each of the OrderID fields in the tblLabor, tblItems, tblClients & tblContracts and the OrderID field in the tblOrders table.

There were other things that I corrected to the db using some of the suggestions by each of you. I still have many things to complete and will most likely be asking for your help again.Thank you so much.
Attached is my latest work in progress

[Johnw]

Oops, in my last response I reversed the following comment:

"I later discovered that the Dsum values had been stored in the tblOrders table but that the data source in my text box on my frmOrders had to be set to tblOrders.TotalLaborTime, not just TotalLaborTime."

Was supposed to be:

"I later discovered that the Dsum values had been stored in the tblOrders table but that the data source in my text box on my frmOrders had to be set to TotalLaborTime, instead of tblOrders.TotalLaborTime."

See who you dealing with? Ha Ha!

[Johnw]

Steve, I'd love to see the changes that you made. I had just started making all of the changes you suggested to a copy of db, but during reading through your response, I saw your offer!
Thanks again

[ssanfu]

@Micron,

Yes, I have had a couple of times where a query was not updatable. But I was able to make changes the query to make it updatable.
But I try and follow the "one form, one table (query)" rule.

I use queries mostly because I never use calculated fields in tables. (I also never use look up FIELDS nor multi value fields)
I do my calculations in queries so that the calcs are always "up to date" in the form/subform.

If I have a form that is ONLY data entry, I might use a table as the record source because all of the table records will not be looked at - records are only being added.

So, yes, it is simply a designer preference (mine) mostly because I had a lot of trouble with forms in A95/A97/A2K.
Or it could of been I wasn't very experienced back then.
Or maybe both......


I converted a couple of dBs to SQL Server Express and because I never use calculated fields (in tables), look up FIELDS nor multi value fields, there were very few structural changes to the tables required. And very little changes to the FE.


In any case, you are correct; it is one of my personal preferences/my style I have developed over the years. It may change in the future, but it works for me now.

[ssanfu]

@Johnw

Here is the dB I changed:

I added a control to sum the labor (mostly grayed out font), the used a reference to that subform control for the total labor hours
(see the "Labor rate X Total Labor Time (Hrs)" )

And look at the "frmLabor" form