Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting subform data gives #Type errors when no records 1

Status
Not open for further replies.

TrekBiker

Technical User
Nov 26, 2010
330
GB
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.
 
There are several ways to do this. One way

Or you could make a procedure on the forms on current event to set the control source. Something like

if me.recordset.recordcount > 0 then
me.myfield.controlsource = "=(Sum([Price],0)"
else
me.myfield.controlsource = ""
me.myfield.value = "0"
end if
 
Ah, great, thanks MajP, that's really helpful.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top