INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Getting subform data gives #Type errors when no records

Getting subform data gives #Type errors when no records

(OP)
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.

RE: Getting subform data gives #Type errors when no records

There are several ways to do this. One way
http://allenbrowne.com/RecordCountError.html

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

RE: Getting subform data gives #Type errors when no records

(OP)
Ah, great, thanks MajP, that's really helpful.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close