Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Subform #Error (Access XP)

    I have inherited a database which quite nicely shows via several tabs on form a lot about this database.
    On nearly all forms there is a main form and several tabs each with a subform. On the tabs there are controls that refer to the subform on the same tab, eg. where there is a Order number in the subform there is also a control on the subform that refers to this field in the subform. Let's say the control is called lngOrder on subform called 'Parts Subform' and the control on the tab is defined as =[Parts Subform].lngOrder. This is all well and good as the control on the tab shows the correct data as long as there is data in the subform, but when there is no data in the subform the control on the tab shows #Error.

    Is there any way of testing this condition and showing a blank in the control on the tab rather than #Error.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform #Error (Access XP)

    Hi Pat,

    Try something like this (substituting the appropriate names, of course)

    =IIf([Parts Subform].[Form].[RecordsetClone].[RecordCount]=0,Null,[Parts Subform].[Form].[lngOrder])

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Subform #Error (Access XP)

    If you were doing this with Reports, you could use the "HasData" property of the subreport.

    For a form, I would use the isNumeric function.

    =iif(isNumeric([Parts Subform].lngOrder),[Parts Subform].lngOrder,0)

    This tests whether [Parts Subform].lngOrder has got a number in it. If not it displays 0.
    Regards
    John



  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform #Error (Access XP)

    Thanks Hans, that works a treat.

    I didn't know you could use a RecordsetClone in that instance.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform #Error (Access XP)

    Thanks John
    But I also have text fields that need to be displayed, in fact most if not all are text fields, I had made a mistake in my naming of the control, sorry about that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •