Calculated Control blinking

  • Thread starter Thread starter Mark A. Sam
  • Start date Start date
M

Mark A. Sam

I have an app which used SQL Server as the backend. Since changing over I
have had an annoying problem. On a particular form I have 4 subforms.
Products, Materials, Task, and Subcontractors.

Materials, Tasks, and Subcontractors are related to Products by an unbounf
textbox which is populated using the Current event of Products.

The issue is this. Each subform has a calulated control using the Sum()
funtion to total the cost. On the products form on some of the records, the
this textbox displays #Error and flickers as if something is processing or
calculation. This also slows the screen display.

Clicking on the subform stops this and causes the correct value to display.
I tried Me.Recalc in routines which change to new records. This corrects
the issue in come records, but not others. If I remove it, the other
records will be ok, but not the ones that were corrected.

I also tried requerying the subforms in the Current event of the main
record. This worked when I first tested it, but failed when I thought it
was solved and tested again. Sometimes a record will be ok for the products,
but then the total in the Materials subform will act up.

I have tried replacing controls as well as subforms, and also placing a new
textbox side by side of the existion textbox. I also tried different
agregate function to see if that was the problem, with no success. I can't
identity any condition to account for this behavior. It never occured on
these partuclar forms when I used an Access backend, but as soon as I
changed to SQL Server. With Access, I could always solve this issue by
replacing the control, but this doesn't seem to be corruption related.

Any help is appreciated.

God Bless,

Mark A.Sam
 
Mark,

Nothing specific (target area too large and info too small), just a couple
of thoughts ...

Since the linkage is via an unbound control, I guess at some point in the
Current event process you assign a value to the control. (You may have found
that it is unnecessary to requery the subforms.)

I would initially focus on two possibilities - first, that there is more
than one instance of assignment to the linkage control and second, that
there might be some kind of data (linkage) error that impacts on only some
records.

On the first candidate, Access sometimes surprises me in what it considers a
notable change. For example, if you have newly arrived at a record and
encounter the command
Me.txtCustName = Me.txtCustName
you will notice that Me.Dirty is set True as a side-effect. In other words,
setting a field/control to its present value is as much of a change as
setting it to another value. Equally, Me.Recordsource = Me.Recordsource
effectively requeries the form. In the same vein, it may well be that - if
the Current event of any of your subforms contains an adjustment of the
linkage control in the parent form, then it might be that an implicit loop
has been established. In this scenario, I guess it's possible that some
timing distinction between the Jet-based version and the SS based version
results in the condition you describe.

On the second candidate, is it possible that in the migration from Jet to SS
some of the relationships got mangled - or perhaps you took the opportunity
to introduce some minor schema changes? Is it worth while running a few
queries on the SS dataset to ensure that all relationships are as you would
want them?

Litmus paper: If the observed condition is occurring apparently randomly,
then a "timing-based" pseudo-random cause such as candidate 1 is indicated.
If the observed condition _never_ happens on (say) records 1,2,4,5,8... but
_always_ happens on records 3,6,7,9... then it could be a data-based
problem.

Final thought: divide and conquer. Your phrasing suggests that you suspect
the Sum()'d fields as being contributary or the cause of the problem. By way
of investigation, how about removing the ControlSource property from the
relevant subforms' controls - just to see if the problem remains or goes
away. If it goes away, you have a tight focus on the problem area.

Good luck.
CD
 
Chadlon said:
Mark,

Nothing specific (target area too large and info too small), just a couple
of thoughts ...
Click to expand...

No kidding ;)

Since the linkage is via an unbound control, I guess at some point in the
Current event process you assign a value to the control. (You may have found
that it is unnecessary to requery the subforms.)
Click to expand...

Yes I assign the value via the Current Event of the Products subform. I
don't believe that the linkage (between the subforms) is an issue. I only
mentioned it for information.
I would initially focus on two possibilities - first, that there is more
than one instance of assignment to the linkage control and second, that
there might be some kind of data (linkage) error that impacts on only some
records.
Click to expand...

Again this isn't the issue. That also isn't the case.
On the first candidate, Access sometimes surprises me in what it considers a
notable change. For example, if you have newly arrived at a record and
encounter the command
Me.txtCustName = Me.txtCustName
you will notice that Me.Dirty is set True as a side-effect. In other words,
setting a field/control to its present value is as much of a change as
setting it to another value. Equally, Me.Recordsource = Me.Recordsource
effectively requeries the form. In the same vein, it may well be that - if
the Current event of any of your subforms contains an adjustment of the
linkage control in the parent form, then it might be that an implicit loop
has been established. In this scenario, I guess it's possible that some
timing distinction between the Jet-based version and the SS based version
results in the condition you describe.
Click to expand...

N/A ;)
On the second candidate, is it possible that in the migration from Jet to SS
some of the relationships got mangled - or perhaps you took the opportunity
to introduce some minor schema changes? Is it worth while running a few
queries on the SS dataset to ensure that all relationships are as you would
want them?
Click to expand...

The tables in the form and subform are linked and there are no relationships
between tables.
Litmus paper: If the observed condition is occurring apparently randomly,
then a "timing-based" pseudo-random cause such as candidate 1 is
Click to expand...
indicated.

I have considered this, but can't understand how this could be.
If the observed condition _never_ happens on (say) records 1,2,4,5,8... but
_always_ happens on records 3,6,7,9... then it could be a data-based
problem.
Click to expand...

Yes, and when I correct the situation by placing me.recalc after a find
method on the main form, then 3,6,7,9 are solved, but 1,2,4,5,8 display the
behavior. Maybe not precisely, but in some way. IT seems to be a data based
issue, but I have no idea what. This is a very complexed for with many
subforms and subforms withing subforms. Even given the complexity, the
problem never occured until I linked to SQL Server. Then the application
became a nightmare. Issues popped up left and right that I had never seen
before. This is the last issue I need to address, and thought there might
be an easily identifable solution. I think I may just remove the Aggregate
function and code in a static value which is update via code also.
Final thought: divide and conquer. Your phrasing suggests that you suspect
the Sum()'d fields as being contributary or the cause of the problem.
Click to expand...

I don't think it is the cause, but rather the victim.

By way
of investigation, how about removing the ControlSource property from the
relevant subforms' controls - just to see if the problem remains or goes
away. If it goes away, you have a tight focus on the problem area.
Click to expand...

That doesn't work becuase the value aren't coming from the Controls, but the
recordsetclone of the form. So if I remove the ControlSource from the
Contol, the Agregate function still pulls the value from the Field. If I
remove the field from the RecordSource (query) then I get a #Error in the
field.

You did however give me an idea to replace the RecordSource from a query to
the table, which didn't work...lol.

I also deleted the table link for the Table, imported an table definition
from the old Access backend, relinked, and transferred the data to the local
table. With the Local table the problem was resolved. This puts me back to
where I was that the linked SQL Table is the issue but why?

All of this has reinforced that the theories of Evolution and Natural
Selection are only nonsense. Microsoft Products, designed by many brilliant
people working together are degrading over time, so how could life as we
know it have improved over time into such a complex state with no brilliant
minds working on it? I guess that shakes up Atheism. ;)

God Bless,

Mark
 
I now understand you to say that the aggregate function is contained within
the recordset of the subform, rather than as the Controlsource of a control
in the subform. I am not sure that I would continue to describe it as a
"Calculated Control".

If there is linkage between the form and the subform, then presumably the
LinkChildField is a bound control. It is not clear to me how you manage that
link if - as you say - the parent form's LinkMasterField is being set from
the Current event of the Product subform. Surely the linkage management
should come from the Current event of the main form? Otherwise the implicit
looping I referred to previously potentially comes back into the picture.

Setting that aside for the moment ...

With the subform recordset being SS-based, you get the problem. With the
recordset being Jet-based, you don't get the problem.

Does that suggest that there could be some SS-incompatible (or at least
SS-misleading) construct within the subform recordset definer - query/SQL
statement - and that the "data-related evidence" is a red herring? Would it
be productive to let the SQL gurus see the statement you are using.

CD
 
Chadlon said:
I now understand you to say that the aggregate function is contained within
the recordset of the subform, rather than as the Controlsource of a control
in the subform. I am not sure that I would continue to describe it as a
"Calculated Control".
Click to expand...

It is a calculated control. The ControlSource = Sum([detExt])
What I meant is that the Function draws is data from the subform recordset
rather than a Textbox whose Controlsource = [detExt].


If there is linkage between the form and the subform, then presumably the
LinkChildField is a bound control. It is not clear to me how you manage that
link if - as you say - the parent form's LinkMasterField is being set from
the Current event of the Product subform. Surely the linkage management
should come from the Current event of the main form? Otherwise the implicit
looping I referred to previously potentially comes back into the picture.
Click to expand...

Here is an example: There the Mainform = [Order Entry Header]. The
(Master) subform = [Products]. The (Slave) subform = [Materials]. So each
product there are multiple materials. It is a One to Many Relationship.

On the the Mainform, {OrderEntry Header] is an unbound control, [ordDetId]
which I am using as the Masterlink field.

The LinkMasterFields and Linkhildfields properties on [Materials] =
[ordDetId].

In the current event of [Products], I have an expression, [ordDetId] =
[ordDetId]

[ordDetId] is a field in the Products table and links the products table
with [Materials].

Ad you change from record to record in [Products], the value of the unbound
control, [ordDetId] changes and thus the records displaying in the
[Materials] subform.

Some people who like two subforms would take the unbound control and make a
calculated control with the Controlsource as [ordDetId] =
Products.Form![ordDetId], but that is too unstable. My method is trouble
free.

I hope that makes sense to you.

Setting that aside for the moment ...

With the subform recordset being SS-based, you get the problem. With the
recordset being Jet-based, you don't get the problem.
Click to expand...

If you think that linked tables are SS based then Yes, but I believe they
are Jet based as with any linked table.
Does that suggest that there could be some SS-incompatible (or at least
SS-misleading) construct within the subform recordset definer - query/SQL
statement - and that the "data-related evidence" is a red herring? Would it
be productive to let the SQL gurus see the statement you are using.
Click to expand...

No I think I solved it by requerying all of the subforms in any procedure
which causes a recordchange on the main form. This seems to be working now.
I can't spend any more time on this.

Thanks for your time and help.

God Bless,

Mark
 
Back
Top