Form continually refreshes... why???

[Max Moor]

Hi All,

Background...

I'm using Access 2002. I have a main form with four subforms. The
main form has no record source.

One subform is the "selection" subform. A textbox on the main form
refers to field in it. The other three subforms are linked to this text box.

I do all this (rather than just driving the main form with a listbox)
so that I can also edit the data in the "selection" subform.

The Issue...

The form appears to be constantly refreshing. When it's open the
cursor cycles between normal and the hourglass. I've put breaks in all the
code, and gotten nothing, so I assume that something about the form's
underlying design is bad.

Any ideas?

Regards,
Max

 

[Allen Browne]

Max, it's not difficult to trigger an endless cycle like that.

The usual connection between a main form and subform is handled through the
LinkMasterFields/LinkChildFields properties. In this case, the "selection
subform" (let's call it Sub1) should have nothing in its
LinkMasterFields/LinkChildFields.

Now what's in Sub2's LinkMasterFields?
Does it refer to a text box on the main form?
Or does it use an expression such as:
=[Parent]![Sub1].[Form]![Text0]

Is there anything in the Current event of any of the forms that could cause
anything to reload?

 

[Max Moor]

Max, it's not difficult to trigger an endless cycle like that.

The usual connection between a main form and subform is handled through
the LinkMasterFields/LinkChildFields properties. In this case, the
"selection subform" (let's call it Sub1) should have nothing in its
LinkMasterFields/LinkChildFields.

Now what's in Sub2's LinkMasterFields?
Does it refer to a text box on the main form?
Or does it use an expression such as:
=[Parent]![Sub1].[Form]![Text0]

Is there anything in the Current event of any of the forms that could
cause anything to reload?

Hi Allen,

Sub1 (fsub_Listings) has nothing in its link fields. The text box
(txtListing) on the main form is "hooked" to sub1 via the its control
source:

=fsub_Listings.Form!ListingID

Sub2 is linked via the text box:

LinkChild = ListingID
LinkMaster = txtListing

FYI, there are actually two subforms attached to the textbox, one is a
continuous forms and one is a single forms (if that matters).

There is a third subform (other than the selection one, sub1). It's the
messiest of the group. It refers to a different textbox (txtItemID) on the
main form whose control source is:

=DLookUp("[ItemID]","tblItemsInListings","ListingID = " &
fsub_Listings.Form!ListingID)

That subform links via:

LinkChild = ItemID
LinkMaster = txtItemID

That's the whole story. I appreciate the help (I think you've bailed me
out a few times over the years). You and the other MVPs have been great
teachers, and real life savers.

Regards,
Max

 

[Allen Browne]

That 3rd one - the DLookup() - is suspect.
If you remove the Control Source from that text box, does it solve the
problem?

I'm thinking that Access sees it as dependent on the calculated control in
the main form, and so recalcs the main form, which reloads the subform,
which repeats the cycle.

If it's the culprit, is there another way to get it to update without
triggering the sequence? Perhaps leaving it unbound, and using the Current
event of the subform to assign a value?

Your comments re the MVPs who volunteer their time here are appreciated too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Max, it's not difficult to trigger an endless cycle like that.

The usual connection between a main form and subform is handled through
the LinkMasterFields/LinkChildFields properties. In this case, the
"selection subform" (let's call it Sub1) should have nothing in its
LinkMasterFields/LinkChildFields.

Now what's in Sub2's LinkMasterFields?
Does it refer to a text box on the main form?
Or does it use an expression such as:
=[Parent]![Sub1].[Form]![Text0]

Is there anything in the Current event of any of the forms that could
cause anything to reload?

Hi Allen,

Sub1 (fsub_Listings) has nothing in its link fields. The text box
(txtListing) on the main form is "hooked" to sub1 via the its control
source:

=fsub_Listings.Form!ListingID

Sub2 is linked via the text box:

LinkChild = ListingID
LinkMaster = txtListing

FYI, there are actually two subforms attached to the textbox, one is a
continuous forms and one is a single forms (if that matters).

There is a third subform (other than the selection one, sub1). It's the
messiest of the group. It refers to a different textbox (txtItemID) on the
main form whose control source is:

=DLookUp("[ItemID]","tblItemsInListings","ListingID = " &
fsub_Listings.Form!ListingID)

That subform links via:

LinkChild = ItemID
LinkMaster = txtItemID

That's the whole story. I appreciate the help (I think you've bailed me
out a few times over the years). You and the other MVPs have been great
teachers, and real life savers.

Regards,
Max

 

[Max Moor]

That 3rd one - the DLookup() - is suspect.
If you remove the Control Source from that text box, does it solve the
problem?

I'm thinking that Access sees it as dependent on the calculated control
in the main form, and so recalcs the main form, which reloads the
subform, which repeats the cycle.

If it's the culprit, is there another way to get it to update without
triggering the sequence? Perhaps leaving it unbound, and using the
Current event of the subform to assign a value?

Your comments re the MVPs who volunteer their time here are appreciated
too.

Hi Again,

I tried pulling that, and still had the problem. I put it back, and
cleared the first text box (=fsub_Listings.Form!ListingID) and the problem
went away.

Just for fun, I changed that control source to '=fsub_Listings!
ListingID' (removed the .Form). Everything still works, but the refresh
loop is still there.

 

[Allen Browne]

Not sure this will work, but you could try setting the 3rd subform's
LinkMasterFields to the text box on the selection subform instead of the
calculated text box on the main form:
=[Parent]![fsub_Listings].Form![ListingID]

 

[Max Moor]

[fsub_Listings].Form![ListingID]

Hi Allen,

I tried it, but to no avail.

I have another question, though. In the text box, I was initially
advised to use '[fsub_Listings].Form![ListingID]' as the control source to
"link' it to the 1st subform.

I found that '[fsub_Listings]![ListingID]' works as well. What is the
difference with or without the '.Form'?

Max

 

[Allen Browne]

Like all MS products, Access tries to be too clever and figure out what you
meant for you. Sometimes this does what you intend; other times it does not.

In this case, fsbu_Listings is the name of a subform *control*. That control
may contain a form (or it may not if SourceObject is blank, and the form may
or may not have the same name as the subform control. To see the difference,
open the form in design view, open the Immediate Window (Ctrl+G), and enter:
? TypeName(Forms!Form1!fsub_Listings)
substituting your main form name for Form1.
It will tell you that this is a control of type Subform.

Then try:
? TypeName(Forms!Form1!fsub_Listings.Form)
It will tell you that this is a class of type Form_fsub_Listings.
That is, you are referring to the form in the subform control, as distinct
from the subform control.

Another way to see the difference is to try:
? Forms!Form1!fsub_Listings.Properties.Count
It will tell you a subform control has around 50 properties.
? Forms!Form1!fsub_Listings.Form.Properties.Count
This time it tells you a form has over 200 properties.

If you really want to take this further, try:
? TypeName(Forms!Form1!fsub_Listings.Form.ListingID)
? TypeName(Forms!Form1!fsub_Listings!ListingID)
They appear to be the same, so ask if they are the same thing:
? Forms!Form1!fsub_Listings.Form.ListingID Is
Forms!Form1!fsub_Listings!ListingID
It's true: they identical ways of referring the the control in the subform.

Now try:
? TypeName(Forms!Form1!fsub_Listings.ListingID)
That will probably error: you cannot use the dot like that (without the
..Form part), but you can use the bang.

So which should you use? Over the years, there's been a great deal of
discussion of bang verses dot and the difference between them.
Unfortunately, even the most respected Access books have got this wrong and
given faulty explanations and rules-of-thumb. The best explanation I can
suggest is this one by Andy Baron:
http://my.advisor.com/articles.nsf/aid/05352

Beyond that (and ignoring the .Form part for now), the ProductCode in this
line:
If ProductCode = "xyz" Then
could be interpreted as any of these:
a) a control (e.g. text box) named ProductCode.
b) a field in the form's Fields (even if there is no control by that name)
c) a property or method of the form (not built-in)
d) a variable or constant declared in the form's module
e) an undeclared variable or constant in the code
f) a global name in Access itself.

In this particular case, (f) is very likely.
If you don't know what I mean, just open the Immediate Window and enter:
? ProductCode
(It's actually a member of the Access Application object.)

That's just an illustration of why it's important to use the narrowest
reference you can. Include the "Me" in your code. Include the .Form. And be
very clear about whether you are referring to a control on the form, or a
field in the form's RecordSource: they are different.

Hope that's helpful without being too much.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

[fsub_Listings].Form![ListingID]

Hi Allen,

I tried it, but to no avail.

I have another question, though. In the text box, I was initially
advised to use '[fsub_Listings].Form![ListingID]' as the control source to
"link' it to the 1st subform.

I found that '[fsub_Listings]![ListingID]' works as well. What is the
difference with or without the '.Form'?

Max

 

[Max Moor]

? TypeName(Forms!Form1!fsub_Listings.Form)

About all I can say is "Wow." Just about the time I start thinking I'm
getting a handle on this thing, a drawer gets opened and I find a whole pile
of new stuff to learn. I appreciate your taking the time to open this
drawer. (I think

Using the narrowest reference possible is good advice. I'm firmware coder
from way back. I know to never trust the compiler.

I'll go read the Baron article. Thanks!

- Max