Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    24

    Question Unanswered: #Error in form control

    I have a subform on one of my Access 2000 forms that is used to display payments on an account. In the footer of the subform is a control (called Text10) that sums up the payment amounts. The control source for Text10 is =Sum([Amount]). THe Amount control is in the Detail section of the subform.

    Here's the problem: Sometimes Text10 produces #Error instead of the properly calculated amount. The appearance of this #Error is inconsistent -- If I open the main form (called AuctionForm) that has this subform on it, I might see #Error in the subform; then, if I click to another form and then back to AuctionForm again, the #Error might be gone. Or it might not... AND, if it IS gone, if I click to another form once more, and then return again to AuctionForm, the #Error might come be back! Or it might not...

    Can someone help me solve this dilemma? I can provide you with any other specific info you might need.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    What may perhaps be happening is that [Amount] contains a null value every once in a while.

    For the heck of it, try this: =SUM(Nz([Amount],0))


  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi whill96205

    A tip just for the sake of clarity IF someone else is to maintain your database. Please try to name your controls meaningfully. If I were to see Text10, it wouldn't tell me a thing about itself. However, IF you called it txtTotalAmount or something along that line THAT would mean something. This will mainly apply in the Report. If you have the Formatting Toolbar showing, there is a little box that shows ALL the FieldNames in your Report. It sure makes it easy to see a name with meaning than having to scour around to find Text10.

    Just trying to help out a little
    have a nice one,
    BUD

  4. #4
    Join Date
    Jun 2004
    Posts
    24
    Quote Originally Posted by CyberLynx
    What may perhaps be happening is that [Amount] contains a null value every once in a while.
    For the heck of it, try this: =SUM(Nz([Amount],0))
    Using Nz seemed to make it produce #Error ALL the time...
    I could understand that Amount might contain a NULL every now and then... but clicking from AuctionForm to another form and then back to AuctionForm wouldn't ADD a NULL to AuctionForm's subform.... But that simple back-and-forth click is all that is necessary to see the #Error appear or disappear (or re-appear) -- without having to change AuctionForm to display a new record.

    Edit -- Actually, the reason the #Error comes and goes is due to the presence of an OnActivate event on the AuctionForm which includes a requery. Without that event, the #Error doesn't go away if you click off the AuctionForm and then back -- it just stays there. So the #Error happens when the subform loads; the requery might make it go away as the form loads again. Or vice-versa - a form with no #Error could get one during the reload. Anyway, my point is that the #Error is inherent in the subform and occurs (or doesn't occur) when it loads; it has nothing to do with the AuctionForm's OnActivate event.

    I'm still stumped: why does the #Error occur at ALL??? I need to fix it.
    Last edited by whill96205; 12-14-04 at 13:00.

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi whill96205,

    Would it be possible for you to make a copy of your database and Zip and Post it here so that some of us might be able to help you better? It seems you're getting no help this way and maybe someone seeing it can better resolve it for you.

    BUD

  6. #6
    Join Date
    Jun 2004
    Posts
    24
    Quote Originally Posted by Bud
    Hi whill96205,
    Would it be possible for you to make a copy of your database and Zip and Post it here so that some of us might be able to help you better? It seems you're getting no help this way and maybe someone seeing it can better resolve it for you.
    BUD
    I would love for another pair of eyes to take a look. This is an Access ADP -- that is, Access front-end, SQL Server back-end. What would I need to post, and how would I post it?

Posting Permissions

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