Subform Totals Error Handling - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Subform Totals Error Handling

Edward Mastoloni

4 years ago

I have an item form and subform for that item's sales entries which is a subform. The subform has a total field (in the subform footer that sums the qty field) I have a text box back on the main form that grabs that subtotal from the subform. Everything works fine when there is data in the subform. However, when the subform has no data (i.e. no sales history) the text box on the main form just shows #Error or #Type!. If have tried wrapping the item form text box data source using an IIF(IsNull(subformTextBoxName),0,subformTextBoxName) but that didn't work. I have tried using NotIsNull too. I have watched every Subforms video I can find too.

	Kevin Robertson @Reply 4 years ago
NZ Function

	Edward Mastoloni OP @Reply 4 years ago
I tried that already. main form text box/control source =Nz([ItemLedgEntryQSF].[Form]![TotQtyTxt],0) also tried subform (footer section) text box/control source =Nz(Sum([ILEQty]),0) none of these work when there is no data at all in the subform

	Kevin Robertson @Reply 4 years ago
Have you tried something like this? `=IIf(Count([qty])>0,Sum([qty]),0)`

	Edward Mastoloni OP @Reply 4 years ago
Just tried that and nope that didn't work. I assume you meant for what you typed to be in the control source text box in the footer section of the subform.

	Edward Mastoloni OP @Reply 4 years ago
I guess I should have also mentioned that since that subform data is not editable in that specific subform, I using a Recordset Type of Snapshot for that subform. Oh and the subform's record source is a Query not a table.

Kevin Yip @Reply

4 years ago

Does the error occur on the total textbox on both the main and the subform? The main form's total textbox gets its value from the subform's total textbox, so the problem is likely on the subform. #Type! means type conversion error, such as when you try to sum a text field. Also, you can't sum a calculated field, which is an automatic #Error. For instance, if you have a control named MyTextbox with a control source of =[qty]*[price], then Sum([MyTextbox]) will cause #Error, and you must use Sum([qty]*[price]) instead. And of course, make sure all your control sources use names of form controls, not field names of tables and queries.

Edward Mastoloni OP @Reply

4 years ago

Kevin Yip. Yes, the problem is in the subform. The detail section of the subform has a text box named ILEQty and that is also the Control source as it comes in directly from the underlying query. This is a number.

The text box in the footer section (Name=TotQtyTxt) has a Control Source =Sum([ILEQty])

Everything works perfect well, IF THERE IS DATA IN THE SUBFORM. If there is no data in the subform, that when the error happens!

	Kevin Robertson @Reply 4 years ago
Probably going to need to see some screenshots of your form in Design View and the Property Sheet clearly showing the Formula you are using in the Control Source. I've tried several variations in my own database and none of my efforts have produced the errors you are getting.

	Edward Mastoloni OP @Reply 4 years ago

	Edward Mastoloni OP @Reply 4 years ago

	Edward Mastoloni OP @Reply 4 years ago

	Edward Mastoloni OP @Reply 4 years ago
Again, I am ONLY getting the errors when the subforms have NO DATA. Also, these Subforms are ONLY for reading data and NOT for data entry therefore the subforms have Recordset Type set to Snapshot for quicker data calculations.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View