Subform causing #error on main form when referencing controls

  • Thread starter Thread starter Andrew Backer
  • Start date Start date
A

Andrew Backer

Hello,

I have a pretty simple form/subform setup.

In the subform I have a footer that includes controls to total up all
items. I reference these controls in the main form to show the totals.
The subform's recordsource is set to a query which does some pretty
simple filtering to remove 'deleted' records.

The problem is that when I apply a filter (filter,filterOn) I get
'#error' in the main form where it should show the values from teh sub
form.

I get around this error by setting the control source of one of my
controls to itself, reseting it in a way. I have 3 controls, but if I
reset one the other 2 are also fixed.

The Control Source :
=IIf([subform].Form.Recordset.RecordCount=0,0,nz([subform].Form!txtSum,0))

The code looks something like this :

Me.subForm.Form.Filter = "..."
Me.subForm.Form.FilterOn = true
Me.someControl.controlSource = me.someControl.ControlSource

I got around this obviously, but it just doesn't seem right. Cany
anyone explain?

-[ Andrew Backer / abacker .@. comcast .dot. net ]-
 
Andrew said:
Hello,

I have a pretty simple form/subform setup.

In the subform I have a footer that includes controls to total up all
items. I reference these controls in the main form to show the totals.
The subform's recordsource is set to a query which does some pretty
simple filtering to remove 'deleted' records.

The problem is that when I apply a filter (filter,filterOn) I get
'#error' in the main form where it should show the values from teh sub
form.

I get around this error by setting the control source of one of my
controls to itself, reseting it in a way. I have 3 controls, but if I
reset one the other 2 are also fixed.

The Control Source :
=IIf([subform].Form.Recordset.RecordCount=0,0,nz([subform].Form!txtSum,0))

The code looks something like this :

Me.subForm.Form.Filter = "..."
Me.subForm.Form.FilterOn = true
Me.someControl.controlSource = me.someControl.ControlSource

I got around this obviously, but it just doesn't seem right. Cany
anyone explain?
Click to expand...


I don't know, but it sort of sounds like a timing issue. If
so, you **might** be able to use:

Me.subForm.Form.Filter = "..."
Me.subForm.Form.FilterOn = true
DoEvents ' might or might not help???
Me.Recalc

but as long as you have an explanatory comment, what you
already have is as good or better.

As an aside, I have had no end of trouble using the filter
property and avoid it like the plague. The way I do it is
to reconstruct the subform's record source query:

strSQL = "SELECT . . . FROM . . . " _
& "WHERE " & ...
Me.subForm.Form.RecordSource = strSQL

I've never seen the issue you're seeing, but maybe I've just
been lucky.
 
Back
Top