#Error in main form control

From the look of it, the error happens in the subform. Try displaying Text10 to see what happens:
* if the subform is a continuous form, make the form footer visible while debugging
* if the subform is a datasheet, allow switching to form view and manually switch to form view while debugging.

You might have something simple like using a conversion function on Null or the like.
What is the control source of Text10, and is it placed in the subform's footer?

Text10 is in the subform's Foot section. It's control source is:
=Sum([Amount])

Amount is a control in the subform's Detail section.

And what do you mean "allow switching to form view"? I sthat a property of the subform?  (The subform appears on the main form as a datasheet.)

Oh wait........ I just set it to Form View, and re-opened the main form.  I flipped around between the main AuctionForm and another Access form until #Error appeared, and yes it does appear in the Text10 control of the AuctionPmt subform.

I have tried re-naming the field -- no effect. I have also tried changing the sum control's source to
=Sum(Nz([Amount],0)) to avoid NULL values. No effect -- actually, I think the incidence of #Error increased.

Here's some other stuff I determined.
I went back to an earlier version of the AuctionForm that does NOT have an OnActivate event forcing a requery. I had never noticed the #Error on that older version, but today I looked and there it was. The interesting thing was that clicking off the AuctionForm to another form and then back again did NOT cause the #Error to rectify itself at all. Why? Because the AuctionForm wasn't being requeried each time it was activated.  So... clearly the #Error occurs when the form (and subform) loads, and is not caused by the presence of the Requery in AuctionForm's OnActivate event.

It rather appears to me that the #Error occurs as if the form loads too quickly (???). That is, if I click from record to record in AuctionForm I am much less likely to see the #Error occur in the subform than if I just press the > button to "scroll" through the records -- as the AuctionForm records flash by one after another, I'll see the #Error flash by in the subform.

But you are right -- there is NOTHING in the calculations to cause this problem. That's why it's inconsistent -- it seems to be with the app itself or the way the form loads. The #Error is inconsistent.

Problem remains unsolved. Anyone else ever seen this before????

(P.S. Harfang, did you want to take a look at the db?)

btw, is there any way to TEST for a #Error value?  I've tried IF statements to determine if Text10 is erroring out, so I can force it to recalc itself.... I've tried:

If IsError(text10)...
If text10.value = "#Error"...
If text10 = "#Error"...
If text10 = #Error...

None of these do anything. Is there no way for VB to determine if a control has #Error??

Hmm, you can but it's complex. I think I will look at the database.
1) make a copy
2) in the copy, remove all confidential information
3) compact and repair
4) check that the error is still happening
5) zip and make available somewhere
    (if you can't, then e-mail me)

Cheers:)

Welll... This is an Access DB Project -- it's an Access front-end (where the problem occurs) with a SQL Server back-end. What would you need: just the ADP file from access, or the large SQL backup as well?

Oops, no. I will not be able to test that here... sorry.
I could take a look at just the form and the subform, though, but the chances I find something seem very slight. This is the kind of things that need to be debugged with actual data. Basically, the recordset operations in an ADP project are quite different, so that I believe you are right when you say: "It rather appears to me that the #Error occurs as if the form loads too quickly (???)."
I will not need the entire front-end. Just copy the form and subform into a new file and send (or make available) just that. I will take a look, but again, I do not have high hopes... but let's do it!
Cheers:)

I sent it to the email in your profile.

Harfang,
Wow, that's a big post!  I am very interested in the way that you have your forms updating. However:
1) Unfortunately, I am not at liberty to re-code the majority of events in the Access forms at this time; and
2) I have already tried the form without the automatic Requery each time it's opened. The #Error still occurs in the subform.
But if I could streamline the way that the forms "talk" to eachother, that would be a good thing to do later on.

So, moving on to your second suggestion: the Timer...

1) The CtlHasError function goes into what code module?
3) You refer to Private Sub Form_Timer -- Is Timer a form property?

I'm at home and not sitting in front of my project, so I hope my questions don't seem too silly.  I'd like to get started on this first thing in the morning.  :)

1) The function CtlHasError() can be in the form's module (make it a Private Function in that case) or in a global module if you might want to use it in several forms.
3) The Timer event is one of the last in the list. It is run everytime the form's timer reaches zero. You set TimerIntervall to a number of milliseconds and the form starts ticking. This way, you can have a form repeatedly doing something. In this case, we want that thing to happen only once, so the Timer event itself sets the TimerIntervall back to 0...

I'm thinking about another thing. You can try to add generous amounts of "DoEvents" in your code. This single-word instruction lets the VB engine release priority so that Access, the JetEngine or other VB processes can get more CPU time. Good practice would be to insert a DoEvent after any important command with side effects (such as moving to another record, requerying a subform, etc) and also to include it once in any long loop that might otherwise freeze the screen updating... This could help as well.

Good luck, I hope you'll find a solution!

I have tried the Timer solution you suggested, but I do not see that it makes any difference.  When does the OnTimer event run? That's where the error trap is at, and I put a MsgBox in the event so I would know if it was running.  Then I got an #Error result on the form, and the MsgBox never showed up -- I don't think the OnTimer event ran, so the #Error was not caught.

For the subform....

I set OnActivate event to: -----------------------
    Me.TimerIntervall = 500

I set OnDeactivate event to: ------------
    Me.TimerIntervall = 0

I set OnTimer event to: -------------------------
    MsgBox ("On Timer event running...")
    If CtlHasError([AuctionPmt Subform].Form!TotalAmount) Then _
        [AuctionPmt Subform].Requery
    Me.TimerIntervall = 0

And the CtlHasError function is: ---------------------
Function CtlHasError(pctlControl As Control) As Boolean
Dim varDummy
On Error Resume Next
    varDummy = pctlControl.Value
    CtlHasError = Err.Number
    Err.Clear
End Function

Correction - I misspelled Interval. It had 2 L's in my code. So I fixed that, and still got an #Error.
In an effort to see what was going on, I put the subform into Single View rather than Datasheet so that I could see the hidden footer where the TotalAmount control is located (where the #Error occurs). With the footer visible, the #Error seems MUCH less likely to occur.    ?????  Why would that be?

(By the way, I can see the effect of the Timer, in that the TotalAmount control doesn't calculate for a half-second. It lags behind the rest of the subform and mainform.)

Oh, wait -- I have two issues, not just one:

1) With the subform footer visible, the #Error doesn't seem to occur as often, if at all. Why might that be? Any thoughts?
2) If I open the subform directly (that is, from the Database Window), it gets caught in a Timer loop. This doesn't happen when it opens as a subform on the main form.

Thanks, guys!

I ultimately utilized Ragoran's event code suggestion to simply do an end-run around the buggy SUM formula. I also retained the Timer events on my subform thar Harfang suggested, and I will employ a better naming convention to avoid confusion between controls and field values. So far, I have not seen the #Error recur.

If anyone is still watching this thread...
Is there a way to adapt the solution I Accepted above to a situation involving a report that has no subform?

The report can have one-to-many records listed on it, and I am wanting a control to display a dollar total. When the code runs, however, I get a Compile Error on this line:   Set oRs = Me.RecordsetClone.
The error is: Method or data member not found.

Sure enough, RecordsetClone does not appear to be an option for [Me] when coding events in a report. So I tried Set oRs = Me.RecordSource, but that gave me another Compile Error: Type mismatch.

What is the proper "method" for establishing a recordset in a report?

If the report does not have a subreport, then I am assuming that you want to sum the value from a field in the current recordset.  In a report, this is easy.  In a group or in the report footer, simply put a text box bound to the expression =sum(FieldName) where you should replace FieldName with the actual name of the field to be summed.  Look at the "Format" topic in the help text.  It explains how to hide a value if it is zero or null.

Thansk, Ragoran. Sorry for the delayed response... but that worked out. I was trying to make it too hard.  :)