Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SubForm Field Formatting (A2K)

    I have a subform labelled subPO_Totals which has embedded a field called txtBO_Total. I use this field as part of two calculations in my main form. When there is data in txtBO_Total, all calculations work correctly. However, if there is no data in the field, I get an #Error wherever I try to use it. I
    Cheers,
    Andy

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

    Re: SubForm Field Formatting (A2K)

    Does
    <code>
    =Nz([subPO_Totals]![txtBO_Total],0)
    </code>
    work?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubForm Field Formatting (A2K)

    Unfortunately not.

    I also tried
    =Nz([subPO_Totals].[Form]![txtBO_Total],0).

    I tried that because thought that it was necessary to have [Form] in there when referring to a subForm?
    Cheers,
    Andy

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

    Re: SubForm Field Formatting (A2K)

    No, that is not obligatory.

    We'll have to see the database, I'm afraid.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubForm Field Formatting (A2K)

    OK,

    It'll take me a bit of time to cut this monster back to a deliverable size. I should have it out to you by tomorrow. Thanks for the attention,
    Cheers,
    Andy

  6. #6
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubForm Field Formatting (A2K)

    The way I overcame this problem was to use an intermediary textbox on the main form. The first textbox in the chain is as yours is - it retrieves the total from the subform. If there is no data it gets #Error. Make it invisible. Your next (visible) textbox uses the controlsource =IIf(IsError([FirstTextBox],0,[FirstTextBox]).

    This is from memory as I do not have the original file to refer to at the moment but hopefully it should give you the idea.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubForm Field Formatting (A2K)

    Steve,

    I just love it when a problem can be solved by making it invisible. The final working solution is:

    =IIf(IsError([FirstTextBox]),0,[FirstTextBox])

    Thanks very kindly for jumping in. Not only did you solve my problem but you also saved Hans the time and trouble of going through my pared down database.

    Thank you both,
    Cheers,
    Andy

Posting Permissions

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