#Error in main form control

On one of my Access 2000 forms (AuctionForm), I am getting an #Error in a textbox control. I'm not sure what’s causing it, since I’m used to getting event errors that let you debug; but this just quietly shows up as #Error, right in the control. Also, it’s not consistent. If I activate and view another form, and then go back to the AuctionForm, the #Error is sometimes gone, replaced by the properly calculated value.

The textbox where the #Error appears (called TotalPaid) has a Control Source property set to =[AuctionPmt Subform].[Form]![Text10].[Value]. Text10 is a control in the subform that adds up all the payment values in that subform. Whatever value appears in Text10 should appear in TotalPaid.

The AuctionForm (where TotalPaid is) has an OnActivate event procedure:
*************************
Private Sub Form_Activate()
Dim auPK As String
auPK = Me!EbayNum
If Me.Dirty = True Then
Me.Dirty = False
End If
Me.Requery
Me.GotoAuction auPK
End Sub

This event is used to refresh the AuctionForm each time it is viewed.

Any reason why the #Error?? I am unable to tell if the #Error is in the subform.Text10 control, or if it is occurring BETWEEN Text10 and the TotalPaid control.

--Galisteo8
LVL 8
Galisteo8Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
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?
0
Galisteo8Author Commented:
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.
0
Galisteo8Author Commented:
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.)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Galisteo8Author Commented:
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.
0
harfangCommented:
Well, I don't see a problem. Sum() should work...
You can try one thing: rename the control [Amount] to something else, say [txtAmount]. This way, Access will make a difference between the field (named "Amount") and the control displaying it.
Sorry I have no better idea. Let's hope some other experts drops in...
Else, I might have to take a look at the database, if that is possible.
Cheers:)
0
Galisteo8Author Commented:
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?)
0
Galisteo8Author Commented:
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??
0
harfangCommented:
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:)
0
Galisteo8Author Commented:
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?
0
harfangCommented:
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:)
0
Galisteo8Author Commented:
I sent it to the email in your profile.
0
harfangCommented:
And I received it...
There is absolutely no problem with the form, except perhaps what you suspected yourself: the Form_Activate() event procedure.
What it does:
1) store the current EbayNum in a string variable
    [this produces an error if the form is on the "New" record, btw]
2) requery the form, probably to make certain that the data is up-to-date
3) jump back to the stored last EbayNum (the "Me.GotoAuction auPK")

If I am to find a solution to the problem: comment out this event procedure and everything might be all right.

To replace the feature you wish (keep the data in the form up-to-date), you need to worry only about the addition and deletion of new records. The method I use for that is the following (this if for another database, but you will get the idea)

In basGlobal, containing all global variables and constants, and a few functions, I basically assign a number to each main object type in the database. I then create a simple signalling matrix that will contain all possible update information from object A to object B...

-----------------------------------------------------------------------------------
Global Const oAll                   As Byte = 0
Global Const oCurrent               As Byte = 1
Global Const oProcedure             As Byte = 2
Global Const oProcess               As Byte = 3
Global Const oEmployee              As Byte = 4
' etc...
Global Const oAction                As Byte = 10
' etc...
Global Const oDocumentControls      As Byte = 17

' current record for main objects:
Global Const oMinCurrent            As Byte = oProcedure
Global Const oMaxCurrent            As Byte = oDocumentControls
Global gCurrent(oMinCurrent To oMaxCurrent)

' Refresh signaling matrix (some flags are unused...)
Global Const oMinSignal             As Byte = oAll
Global Const oMaxSignal             As Byte = oDocumentControls
Global gfSignal _
    (oMinSignal To oMaxSignal, _
    oMinSignal To oMaxSignal)       As Boolean  ' i.e. From, To...


' Manages the refresh of hidden forms:
' Signaling of a change in one form (for all others)
Public Sub SignalAll(bytFrom As Byte)
    Dim intSignal
    For intSignal = oMinSignal To oMaxSignal
        gfSignal(bytFrom, intSignal) = True
    Next intSignal
End Sub

' Signal read by a form :)
Public Sub SignalClear(bytTo As Byte)
    Dim intSignal
    For intSignal = oMinSignal To oMaxSignal
        gfSignal(intSignal, bytTo) = False
    Next intSignal
End Sub
-----------------------------------------------------------------------------------

In this database, it means that the "Employee" form can "signal" that it's been heavily updated (employee added or deleted, initials or name changed). All other forms can now detect that and update subforms, combo boxes or other tools using employees only if needed.

For example, in frmEmployees:

Private Sub Form_BeforeUpdate(intCancel As Integer)
    If txtPID <> txtPID.OldValue Then _
        SignalAll oEmployee
End Sub

Then the form frmRoles can use that as in:

Private Sub Form_Activate()
On Error Resume Next
    If gfSignal(oEmployee, oRole) Then
        cboSelectEmployees.Requery
        lstEmployees.Requery
    End If
    If gfSignal(oProcedure, oRole) Then lstProcedures.Requery
    SignalClear oRole
   
    If Not IsNull(gCurrent(oRole)) Then
        ' jump to the record, much like in your code and
        gCurrent(oRole) = Null
    End If
End Sub

Private Sub Form_Load()
    ' no refreshing needed for a newly opened form... :)
    SignalClear oRole
End Sub

Now the question is: can other forms actually add or delete EBay numbers? If yes, you could use (let's imagine two forms are used to edit employees):

    If gfSignal(oEmployee, oEmployee) Then Me.Requery

Notice that this will happen only in one case: when the form employee is reactivated after some heavy editing has been done in another form or through code, and that the signal has been given: SignallAll oEmployee

In your case, you requery all the time, even if you are freshly opening it (useless), which might be the source of Access' confusion creating the error...

Now if this doesn't work (basically removing the Me.Requery when not needed), there is yet another technique.

In Form_Activate, set the TimerInterval to say 500 milliseconds
In Form_Deactivate, set the same to 0 (make sure the form is not doing stuff in the background)
In Form_Timer do the following:

1) Check if the total is an error
2) requery the subform if needed...

Function CtlHasError(pctlControl As Control) As Boolean
Dim varDummy
On Error Resume Next
    varDummy = pctlControl.Value
    CtlHasError = Err.Number
    Err.Clear
End Function

Private Sub Form_Timer()
    If CtlHasError([AuctionPmt Subform].Form!TotalAmount) Then _
        [AuctionPmt Subform].Requery
    Me.TimerIntervall = 0
End Sub


Well, I know this is a lot of work, but I have seen from your sample that you are up to it.

I hope this will help you solve the problem!
Good luck:)
0
Galisteo8Author Commented:
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.  :)
0
harfangCommented:
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!
0
Galisteo8Author Commented:
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
0
Galisteo8Author Commented:
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.)
0
Galisteo8Author Commented:
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.
0
ragoranCommented:
Hi,

I typically get an #Error value in a similar context when the subfor does not have any records.  With ADO, timing issues maybe a problem as well.

Recommandation from Harfang to give distinct name to controls (not same as field from recordset) is important.  The Count() in text10 should use the recordset field name, but this value must be reference in a control in the detail section to ensure it is "loaded" in memory (this is based on my experience).  Having the text10 visible forces Acces to refresh its value more often, thus maybe reducing the timing problem.

You could keep the control and footer visible but only 1 pixel high, so the user won't notice it.

A different approach will be to add a property in the subform to compute the total correctly and have the TotalAmount control refer to that property.  This is an example of such code from one of my application.  It uses DAO recordset but I believe it should work fine with an ADO recordset.  You will need to add the correct field.  You may also want to validate the error numbers I am testing.  This code gows in the subform


Public Property Get BigTotal() As Double

   Dim oRs As Recordset
   Dim wTotal As Double
   
   On Error GoTo proc_error
   
   wTotal = 0
   Set oRs = Me.RecordsetClone
   oRs.MoveFirst
   Do Until oRs.EOF
      wTotal = wTotal + oRs!HoursTotal
      oRs.MoveNext
   Loop
   
proc_exit:
   Set oRs = Nothing
     
   BigTotal = wTotal
   
   Exit Sub
   
proc_error:
   If Err.Number = 7951 Or Err.Number = 3021 Then
      'no record, stop processing
      Resume proc_exit
   Else
      'problem...
      MsgBox Err.DESCRIPTION
      Resume proc_exit
   End If
   Resume  'for debuging purposes only
   
End Property


---

then the control source property of the TotalPaid textbox should be:

=[AuctionPmt Subform].[Form].[BigTotal]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Galisteo8Author Commented:
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.
0
Galisteo8Author Commented:
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?

0
ragoranCommented:
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.

0
Galisteo8Author Commented:
Thansk, Ragoran. Sorry for the delayed response... but that worked out. I was trying to make it too hard.  :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.