An Orders form allows purchase items to be entered into a subform.
A footer field in the subform totals the order item Prices and I show the total in an unbound field on the main form.
This is all okay for existing orders but when creating a new one with no records I get a #Type errors. I tried setting the footer data source to =Nz(Sum([Price],0) but error still get the error. There are other subforms for Credits and Deposits and each need their totals to be displayed too, to feed into further calculation fields.
I could create new queries to count purchases, credits and deposits but this would mean recreating each of their SQL sources as fixed queries with criteria OrderID, then using sources in the main form fields like
= IIF(DCount(*.*,"qryCountRecords")>0,sfmOrderItems.Form.Sum(TotalPrice),0)
Is there a better way of coping with no records? I need to do the same thing for other forms in the database.
A footer field in the subform totals the order item Prices and I show the total in an unbound field on the main form.
This is all okay for existing orders but when creating a new one with no records I get a #Type errors. I tried setting the footer data source to =Nz(Sum([Price],0) but error still get the error. There are other subforms for Credits and Deposits and each need their totals to be displayed too, to feed into further calculation fields.
I could create new queries to count purchases, credits and deposits but this would mean recreating each of their SQL sources as fixed queries with criteria OrderID, then using sources in the main form fields like
= IIF(DCount(*.*,"qryCountRecords")>0,sfmOrderItems.Form.Sum(TotalPrice),0)
Is there a better way of coping with no records? I need to do the same thing for other forms in the database.