Calculate subtotal on subform ad display on upper subform (1 Viewer)

  • Thread starter Deleted member 146202
  • Start date
D

Deleted member 146202

Guest
Hi all,

I'm having trouble getting a subform to calculate another subforms total and display it as it keeps coming up with an '#ERROR'.

See attached a simplified version on my database. I've followed instructions from tutorials and forum posts I've seen online... I'm pretty sure my parent and child fields and correct, and I've coded what I think SHOULD fix the issue.

Any help would be massively appreciated, I've been stuck on this for a couple of days :(

Thanks,
 

Attachments

  • DB for Show.accdb
    824 KB · Views: 38

isladogs

MVP / VIP
Local time
Today, 11:12
Joined
Jan 14, 2017
Messages
18,186
Welcome to AWF

See attached which I believe does what you want.
I changed the record source for the subform and it now works
I've also ensured the subform is still editable

A simplified version of the DSum is now part of the subform record source
Not ideal but a lot better than you had before

NOTE: your original formula was
Code:
=IIf(IsNull([Order Number]),Null,DSum("Cases","OrderItems","Order Number=" & [Order Number]))
This basically says if Order Number is null then answer is null otherwise use DSum.
But Order Number can't be null in this subform so that can be simplified to
Code:
=DSum("Cases","OrderItems","[COLOR="Red"][[/COLOR]Order Number[COLOR="red"]][/COLOR]=" & [Order Number])

Can you see one other ESSENTIAL difference - added in RED


HTH
 

Attachments

  • DB for Show - UPDATED.zip
    52 KB · Views: 41
Last edited:
D

Deleted member 146202

Guest
Hi ridders,

That's working perfect! Thanks very much.

A question though, I copied the Dsum you corrected into the textbox of my larger DB I'm working on first, and that's sorted the problem out.

Is there a reason you put it into the record source too? and also, why would it not be ideal? I'm still learning as you can see so this would help me not make these same mistakes elsewhere.

Thanks,
 

isladogs

MVP / VIP
Local time
Today, 11:12
Joined
Jan 14, 2017
Messages
18,186
You don't need it in the form record source and the control.
Just use it in the form record source as I did.

I said not ideal as domain functions such as DSum can be slow if you have a large number of records. However, I doubt you are going to notice any delay
 
D

Deleted member 146202

Guest
Ah brilliant, I didn't notice you'd done it that way.

That's a lot better, my control source for 'Total cases' can stay as the table, and it's quicker than using Dsum in the textbox too. You star.
 
D

Deleted member 146202

Guest
Well I've got this working for both cases & pallets on my database now which is great.

But, any idea why it's not saving these total figures to the table? The table is the control source for both textboxes so shouldn't that do it?

They're both 0 in the table no matter what is shown in the form.

I apologise for the questions, still learning and this is helping greatly.
 

mike60smart

Registered User.
Local time
Today, 11:12
Joined
Aug 6, 2017
Messages
1,899
Hi

It is recommended that these values are just calculated on the Fly and NOT stored in the Table(s).
 
D

Deleted member 146202

Guest
hmm ok, I think I can work around it and calculate them in the queries/forms I was wanting them for. I'm not really understanding why that may be best practice though?
 

mike60smart

Registered User.
Local time
Today, 11:12
Joined
Aug 6, 2017
Messages
1,899
Hi

Yes This is what Colin Did for you - He created the calculations in the Query.

This is just for display on the Form but NOT for storing in the Table.
 

isladogs

MVP / VIP
Local time
Today, 11:12
Joined
Jan 14, 2017
Messages
18,186
Calculated values should not normally be saved in tables as they may change.
You can delete the field in the table

If you really want to save the calculated values, an update query could be used to do so.
The risk with that is keeping the totals up to date which is why its not recommended practice
 
D

Deleted member 146202

Guest
So looking into it I'm fine with not storing these details. However, I do then want to calculate the 'Grand Total of Pallets' in the main form from adding together to number of pallets on each order.

Going one level up again if you like. But as these values are not stored anywhere, and the 'load' and 'items' don't share any fields due to there being 'Orders' in the way, I'm not sure how I could do this.

I've tried making the control source of 'grand total pallets' = [Forms]![Master]![Orders].[Form].[Total Pallets] But this is giving me a '#Name?' error.

I'm loving access and am working like an addict on this work project all easter weekend... but it sure is a pain in the .... when you can't get around things :)
 

isladogs

MVP / VIP
Local time
Today, 11:12
Joined
Jan 14, 2017
Messages
18,186
Attached is your second Easter egg for the weekend:

I've modified both the main form & top subform to show total pallets.
Query qryTotalCasesByOrder updated with additional fields

To prevent confusion, I've also removed the calculated total cases & Pallets fields from the tables Master & Orders.
All calculations in the query are based on values in table OrderItems

I've also added Option Explicit to each code module and compiled the project - you should ALWAYS add this declaration in each module
 

Attachments

  • DB for Show - UPDATED - v2 CR.zip
    49 KB · Views: 44
D

Deleted member 146202

Guest
Hi Colin,

Not even opened it yet and I'll say thank you very much for your help.

I realise it looks like I'm just here for other people to do my work for me... that's not the case, and you're all very much appreciated, I hope to help some beginners too one day :)

EDIT: Looked it over and it works great. More importantly, I understand what you've done and have learnt something.
 
Last edited by a moderator:

isladogs

MVP / VIP
Local time
Today, 11:12
Joined
Jan 14, 2017
Messages
18,186
Glad to have helped.
Hopefully you'll be able to do the next stages of your project OK
good luck
 

Users who are viewing this thread

Top Bottom