Record selector jumps back to first record

[Guest]

I have a from with a subsorm. When I select any record in the subform other
than the first one the message at the bottom left of the scren shows
"calculating" As soon as the message shows "form View" the record selector
jumps back to the first record. I have two other very similar form/subform
screens that act normaly: the record selector stays where it is put after the
message shows "form view". This started within the last 3 days. I have
checked a backup from 3 days ago and the record selector behaves as expected.
The backup from 2 days ago shows abnormal behavior. within the last 6
months I converted all the macros to VBA code. I have been experminting with
DOA code but I have not made any changes to the form with the abnormal
subform (I think)

Any help is appreciated.

Steve S

 

[Marshall Barton]

I have a from with a subsorm. When I select any record in the subform other
than the first one the message at the bottom left of the scren shows
"calculating" As soon as the message shows "form View" the record selector
jumps back to the first record. I have two other very similar form/subform
screens that act normaly: the record selector stays where it is put after the
message shows "form view". This started within the last 3 days. I have
checked a backup from 3 days ago and the record selector behaves as expected.
The backup from 2 days ago shows abnormal behavior. within the last 6
months I converted all the macros to VBA code. I have been experminting with
DOA code but I have not made any changes to the form with the abnormal
subform (I think)

It sounds like you have some code in the form's Current
event that's causing this, probably a Requery.

 

[Guest]

I thought about that but there is only one statement in the On Current event
and that is commented out. There is no event code for the subform, the
underlying Source Object, or any of the fields in the detail record.

Steve

 

[Marshall Barton]

Maybe there's something in the main form??

The "calculating" can come from a complex set of dependent
control source expressions, possibly across both the main
form and subform. The same can be true of a significant
number of Conditional Formatting expressions.

You are not providing enough clues for us to do much more
than guess at what you have or what might be causing this
behavior. About all I can say for sure is that this problem
is definitely not common and all the cases I've seen have
all been caused by an inappropriate Requery in some
procedure.

Try some traditional debugging techniques on a copy of the
forms. Start by removing the code and then adding it back a
little at a time until it stops working.

 

[Guest]

Early this morning I started, as you just suggested, adding code to the most
recent “working backup†until I found what was causing the problem Without
getting into the complex details of application I’ll just say I open several
other form from one master form. The oldest form is the one with the subform
problem. It has worked for years. To get the subform to only display the
correct subset of records I tricked the subform by typing “Contest ID;Student
ID†in the Child/Master link field on the Properties sheet. Student ID is a
field in both Child and Master but Contest ID is a text box (not part of the
underlying master record. I know that child/master link field are supposed
to be in both underlying records but by typing it in I got the subset of
records I wanted. Worked fine-lasted a long time. I swear

The code causing the problem was added to close and reopen the main form to
switch to a different contest for the same student. It’s Open event must
have triggered a requery of the form with the subform.

Solved the problem by using Student ID as the only link field and adding
Contest ID as a criteria to the underlying query.

Lesson learned is: Don’t try to trick Mother Nature(ACCESS)

Thanks for your suggestions

Steve

Maybe there's something in the main form??

The "calculating" can come from a complex set of dependent
control source expressions, possibly across both the main
form and subform. The same can be true of a significant
number of Conditional Formatting expressions.

You are not providing enough clues for us to do much more
than guess at what you have or what might be causing this
behavior. About all I can say for sure is that this problem
is definitely not common and all the cases I've seen have
all been caused by an inappropriate Requery in some
procedure.

Try some traditional debugging techniques on a copy of the
forms. Start by removing the code and then adding it back a
little at a time until it stops working.
--
Marsh
MVP [MS Access]

I thought about that but there is only one statement in the On Current event
and that is commented out. There is no event code for the subform, the
underlying Source Object, or any of the fields in the detail record.

 

[Marshall Barton]

Early this morning I started, as you just suggested, adding code to the most
recent “working backup” until I found what was causing the problem Without
getting into the complex details of application I’ll just say I open several
other form from one master form. The oldest form is the one with the subform
problem. It has worked for years. To get the subform to only display the
correct subset of records I tricked the subform by typing “Contest ID;Student
ID” in the Child/Master link field on the Properties sheet. Student ID is a
field in both Child and Master but Contest ID is a text box (not part of the
underlying master record. I know that child/master link field are supposed
to be in both underlying records but by typing it in I got the subset of
records I wanted. Worked fine-lasted a long time. I swear

The code causing the problem was added to close and reopen the main form to
switch to a different contest for the same student. It’s Open event must
have triggered a requery of the form with the subform.

Solved the problem by using Student ID as the only link field and adding
Contest ID as a criteria to the underlying query.

Lesson learned is: Don’t try to trick Mother Nature(ACCESS)

LOL

No you can't fool Access, at least not very often. However,
this sounds like it might(?) be explainable. The Link
Master property can be a bound or unbound control on the
main form. The Link Child property is the one that must
refer to a field in the subform's record soure table/query.

I don't think that closing/opening some other form has
anything to do with it, unless your link master text box
refers to the other form. However, if you have any code
that resets the text box's value that would trigger a behind
the scenes requery of the subform. Setting the subform's
RecordSource property will also trigger an automatic
requery.

 

[Guest]

Thanks for the explaination. I thought BOTH the master and child links had
to be bound fields. It worked for years because only the child link has this
requirement.

One of the fields that I was using as the master link was an undound field
that NOW does gets updated when the other form changes records. the source
of the unbound field is on the other form. When I say NOW I mean before I
fixed the problem this morning.

thanks again