Sorting within subforms problem

[Ray Pixley]

I'm using Access 2003. The situation I have occurs when there is a subform
on a form where there are two data columns being matched on the parent and
child forms.

When I request a subform be sorted, Access _sometimes_ takes whatever is in
the first record of the query or table that the subform is using and changes
the entries in that record's columns to what the form is asking the subform
to display.

This "undocumented feature" embarrassed me earlier today when I was asked
to sort a subform by a meeting attendee, and the sort unexpectedly added a
record to the presentation. On investigating further, I noted that a record
in the table/query that would not have shown up on the subform was missing.
But entries in the other columns in the "new" record matched the entries in
the missing columns except for the entries used to match it with the parent
form. Thus, a record was changed. But that effect makes the whole database
unreliable.

My understanding of a sort is that it is only suppose to sort. It is not to
make any changes in the tables being sorted. And, of course, I'm not very
happy that this may end up ruining my career.

Why does this happen? What can be done?

 

[Allen Browne]

More info please.

How are you sorting your subform?
Are you setting its OrderBy and OrderByOn properties?
Are you changing the RecordSource to the subform?
Alterning its source query?
Please post the code that generates this problem.

There are some bugs associated with setting the OrderBy and Filter
properties of forms/subforms, but it does not sound like your symptoms. Some
are listed in this article:
Incorrect filtering of forms and reports
at:
http://members.iinet.net.au/~allenbrowne/bug-02.html

If you are changing the RecordSource, you may find that Access reassigns
what it *thinks* you want to the LinkMasterFields and LinkChildFields, so
you may need to reassign these again to get the right result.

More importantly though, none of these approaches should modify the data in
any way, so there is something wrong.

 

[Ray Pixley]

Thanks for replying so quickly. I'll have to try some of your suggestions
Monday when I get back to work, but can answer some of your questions now.

How are you sorting your subform?

I highlighted the column on the subform to be sorted and clicked on the A-Z
sort button on the toolbar at the top of the Access window.

Are you setting its OrderBy and OrderByOn properties?
No.

Are you changing the RecordSource to the subform?
No.

Alterning its source query?
No.

Please post the code that generates this problem.

There is no code to post.

There are some bugs associated with setting the OrderBy and Filter
properties of forms/subforms, but it does not sound like your symptoms. Some
are listed in this article: Incorrect filtering of forms and reports
at: http://members.iinet.net.au/~allenbrowne/bug-02.html

I'll take a look anyway.

If you are changing the RecordSource, you may find that Access reassigns
what it *thinks* you want to the LinkMasterFields and LinkChildFields, so
you may need to reassign these again to get the right result.

I'm not changing the record source. I was just sorting it to make it more
readable during the discussion.

More importantly though, none of these approaches should modify the data in
any way, so there is something wrong.

Which is why, after spending hours trying to figure out what I did wrong, I
posted the problem.

(FYI, I have a second problem with forms that is probably unrelated to this
one and plan to post that question as a separate thread after this one is
hopefully resolved. Fortunately, that problem did not surface at an
inappropriate moment, but probably will later.)

Ray Pixley

-----------------------------------------------------------

 

[Allen Browne]

Okay, Ray. No code. Just right-click.

There's no way that is dirtying the record or creating a new one, so the
problem must be related to something else. Is there any code in this
subform's events, e.g. Current?

Display the RecordSelector (at the left of the form). Is it showing that the
record is dirty before the sort begins?

Is the subform based on a query or table? If query, is this a calculated
field? Or is the ControlSource of this control set to an expression?

 

[Guest]

I'm at work now (U.S. EST (East Coast)).

Okay, Ray. No code. Just right-click.

FYI, I double checked again for no code. Nothing shows up in the macro
editor (VBA). If SQL is consided code, then I'm not sure there is no code.

Is there any code in this subform's events, e.g. Current?
No.

Display the RecordSelector (at the left of the form). Is it showing that the
record is dirty before the sort begins?

I see no indication of a dirty record. What I do see are two solid filled
triangles pointing to the right. One appears asssociated with the main form
and one appears associated with a record on the subform. Also, I'm not sure
what a dirty record indicator will look like.

Is the subform based on a query or table?
If query, is this a calculated field?

In support of this thread, I create a form this morning using a table and
got the same behavior. I've seen this behavior when using either a table or
a query. The queries created for use in subforms do not have calculated
fields.

Or is the ControlSource of this control set to an expression?

As there are no expressions, I assume this is a moot question.

One additional note, up to now the record being changed always appeared to
be the record with the lowest ID autonumber. When I tried the subform sort
this morning, it changed a middle record instead of a low ID autonumberd
record.

 

[Allen Browne]

Okay, so you are seeing this in a simple subform bound directly to a table,
with no code. That's very odd.

Sounds like you have created a small sample that reproduces the issue. I'll
take a look if you want to zip it and attach to an email. The address is
spelled out in the signature below. Be sure to include this thread in the
email.

Regarding the icon in the Record Selector, the solid triangle indicates the
record is not dirty. The icon becomes a pencil when it's dirty (i.e. an
uncommitted edit).

You could try putting some code into the BeforeUpdate event of the form so
you catch the moment when the record is changed. Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = True
MsgBox "Attempt to update record ID " & Me.[ID] & " at " & Now() & _
vbCrLf & "Press <Esc> to undo."
End Sub

 

[Guest]

Sounds like you have created a small sample that reproduces the issue. I'll
take a look if you want to zip it and attach to an email. The address is
spelled out in the signature below. Be sure to include this thread in the
email.

Done.

 

[Allen Browne]

Okay: you have a main form and a subform both bound to the same table. The
combos in the main form are bound to fields in this table. When you select a
record in the main form, it is therefore writing the change to the table.

Remove the Control Source of the combos in the main form. In fact, remove
the RecordSource of the main form also. The subform still filters as you
expect because of the LinkMasterFields/LinkChildFields, but selecting
records in the main form's combos will no longer be changing data in the
tabele.

 

[Guest]

Remove the Control Source of the combos in the main form. In fact, remove
the RecordSource of the main form also. The subform still filters as you
expect because of the LinkMasterFields/LinkChildFields, but selecting
records in the main form's combos will no longer be changing data in the
table.

I did that and it worked ... mostly. Apparently the same thing has to be
done for queries as well as tables. And the query used for filtering the
table is now being used by the combo box to do the filtering. But there are
some unintended consequences, as the example I sent you is only a part of the
database I'm using. After studying it some, I'll post anther thread as it
could be a different issue. Thank you very much for your help.