Bug in Sorting/Filtering SubForms

  • Thread starter Thread starter Larry F
  • Start date Start date
L

Larry F

When a user right-clicks in a subform (particularly if it
is in continuous or datasheet view) and chooses a Sort or
Filter option from the popup menu, Access reports an
incorrect and confusing error message: "You must use the
same number of fields when you set the LinkChildFields
and LinkMasterFields properties." After the bewildered
user clicks the OK button, the subform is appropriately
sorted or filtered. Afterwards, just displaying the main
form brings the same message.

This bug has been around for many years, since Access 95,
and it's surprising that Microsoft has not yet solved it.
It has improved, though, since in Access 97 this action
caused the application to crash and usually corrupted the
data.

My only solution has been to programmatically rewrite the
recordsource of the subform to sort or filter, but this
means all data must be fetched again, a performance hit.

Does anyone have any other ideas?
 
I couldn't replicate this. Are you sure you have the same number of fields
in the LCF & LMF?

Damon
 
Larry, there are many bugs in how Access implements filters.
Some are listed here:
http://allenbrowne.com/bug-02.html
Haven't seen the message about the number of link fields though.

What exactly is in your LinkMasterFields and LinkChildFields properties?
Do these names refer to table fields? unbound controls? calculated fields?
Is there anything these names could clash with (e.g. Section, Name, Date)?

Are you reassigning the RecordSource (or Recordset) of the main form or
subform in such as way that these may no longer be available?
 
Allen:
Thanks for responding. I just started programming in
Access 2002 (like you, I also began with version 1). I've
used an old trick since Access 95 in which an unbound
text control on the main form, with its ControlSource
property set to a constant expression, becomes the LMFs
second field. This mirrors an actual field found in the
SF (and that field is set as second in the LCF property).
For example, there is a one table Teams and a many table
People, with Team ID # as Key. People contains a
field 'Type' which has 'P' for parent, 'C' for child. I
create a main form Teams, put on it a tab control with 2
tabs (Parents, Children). I create a SF People, and put 2
instances of it on Teams - 1 on Parents tab, 1 on
Children tab. On Parents tab I create a text control
ParentsTx and set CS to "=P". The LMF for this SF is Team
ID #;ParentsTX. The LCF is Team ID #;Type. Not only does
this filter the subform to display only parents, but
entering a new record in that SF will set 'Type' in the
new record to 'P'.
On Children tab I create a text control ChildrenTx an set
CS to "=C". The LMF for this 2nd SF is Team ID
#;ChildrenTx . The LCF is Team ID #;Type. This instance
of the SF will show only children. But right-clicking
either SF and choosing a Sort/Filter option displays the
error msg, then correctly sorts/filters. (In Access 97,
this would cause a GPF, so I disallowed sorting/filtering
in SFs, but had hoped I could do this in 2002.)
In the database on which I am currently working, I tried
this with several complicated forms. Afterwords, just
opening the main form produced the message since the Sort
By or Filter properties for the SF had been saved without
confirmation.
I've removed this 2nd LMF,LCF from those forms and set to
just 1 field, the key, saved. I deleted SortBy or Filter
settings in SF and saved. I still get the error if I
Sort/Filter the SF. Even if I create a totally new main
and subform on those tables, and Sort or Filter on the
SF, I get that or similar message. If I create main/SF on
other tables and sort/filter I get no message.
I'm guessing that some internal table property for the
one and many tables has been set or corrupted. I've tried
Compact/Repair and Importing all objects into a new
database, but the errors persist.
Any ideas?
Thanks Again
 
Hi Larry.

Makes perfect sense. In fact, the whole idea of storing similar data in the
one table and splitting them up in the interface is often a good way to
handle relational data. You should be able to work with Access in the way
you describe. In practice, though, the filters have been very buggy in all
32-bit versions.

If you have a field named "Team ID #" named in the LinkChildFields, did you
try placing square brackets around it, i.e.:
[Team ID #];[Type]

Presumably you have the quote marks within ControlSource of the ParentsTx,
i.e.:
="P"
and there is no field or anything else in the main form named ParentsTx.

Since the Type field in the subform is text, there seems no possibility of
Access misunderstanding the data type. Some objects do have a Type property,
but I doubt this is causing a problem.

You may be able to work around the problem by removing the 2nd field from
the LinkMasterFields/LinkChildFields properties, and using a query as the
source for each subform. The query returns the relevent type. To assign a
value to the Type field for new records, include a hidden text box for the
Type field on each subform, and set its Default Value. We often this this is
more efficient and less buggy than nominating extra fields in the
LinkMasterFields/LinkChildFields.

Hope that workaround helps.

(If you are trying to reuse the same form as the SourceObject in both the
subform controls, you can use the Open event of the main form to assign the
RowSource and Type.DefaultValue in the subform instances.)
 
Allen:
Thanks again for your time.

I removed the duplicated subform control, the unbound
text box, and set the LMF and LCF properties to just the
Key field. I still get the error, but I prevented the
user from right-clicking to view the popup menu, so the
user won't get the message (and the quality of the
interface is vastly diminished).
However, I began to add code to the Event Procedures of
some of the subform controls, and whenever one fires I
get another error:"The expression After Update you
entered as the event property setting produced the
following error:A problem occurred while MS Access was
communicating with the OLE Server or ActiveX control"
(there are no ActiveX controls in this .MDB). I had
sometimes gotten that message before with the first
problem I described. I put a breakpoint on the first line
of the event procedure code, but it never got that far (I
have compiled, saved, compacted and repaired this MDB).
These forms/subforms are very complicated (not like the
simple example I gave you in way of explaination). I
really believe this database and / or these objects must
be corrupt.
Any suggestions as to how to repair them? (I've tried
creating a new DB and importing all items).
Also, I noticed in the (code) References list that Visual
Basic for Applications is listed 6 times (1 is checked at
the top).
Thanks,
Frustrated
 
Allen:
Please ignore my last post.
I removed from Rererences MS ActiveX Data Objects 2.5
Library, recompiled, and all those errors have
disappeared (at least on the form and subform on which I
am working). I don't know if the References were screwed
up (that happened a lot in Access 97), or if there was
some other conflict.

Since you may have used Access 2002 more than I, can we
still call a Public Sub or Function from a subform Event
Procedure? e.g. Me.Parent.PublicFuncInParentClassMod

Thanks again for all of your help :)
 
I suspect these errors stem from either:

bugs with the "edit subform in place" - after editing and
saving the SF only a white rectangle appears for the SF
control. I will try not to use this feature (open the SF
directly from the DB container)

my SF is set as datasheet with embedded (another) subform
control, so clicking the "+" on the SFs datasheet record
shows the (sub)subform in continuous forms view. This
also is a new feature, so I suspect its stability.

Later, when I completed this rush project, I will have
time to test the theories and investigate further.
 
Back
Top