My subforms jump back to record 1 at random

[Guest]

I have a fairly complex form running Front-Back End config over a network
with about 8 users. They complain that sometimes when they are looking at a
subform record other than the first record, it will jump back to the first
record in the subform on it's own.

It tried to redesign the form, but the new design experiences the same
symptoms.

For the users this is a major issue. Sometimes they think they are typing
prices etc. into record 8 and when they look up the subform is on record 1
and they have just over-typed the wrong field.

 

[Dirk Goldgar]

I have a fairly complex form running Front-Back End config over a
network with about 8 users. They complain that sometimes when they
are looking at a subform record other than the first record, it will
jump back to the first record in the subform on it's own.

It tried to redesign the form, but the new design experiences the same
symptoms.

For the users this is a major issue. Sometimes they think they are
typing prices etc. into record 8 and when they look up the subform is
on record 1 and they have just over-typed the wrong field.

Is there some code, maybe running in a timer event, that requeries the
subform?

 

[Guest]

No, timer events in the entire database. I thought It may be because other
users were requering data and access ran out of room to handle the record
locking.
This is a complete guess and I don't really know how acces handles the
record locking.

 

[Dirk Goldgar]

No, timer events in the entire database. I thought It may be because
other users were requering data and access ran out of room to handle
the record locking.
This is a complete guess and I don't really know how acces handles the
record locking.

I can't say for sure, but it doesn't sound all that plausible to me.
Certainly my users have never reported anything like that, but every
application's usage pattern is different.

Can you narrow down the circumstances when it happens? For example, can
you tell if it happens, say, when two users are working on the same main
record? Is there anything else consistent about it?

Can you make it happen when there is only one user in the database?
That would eliminate any possible issues having to do with multiuser
access..

 

[Guest]

Thanks for your response Dirk

The original database was in Acc97. The form was a sales order form. The
Main form is linked to Customers and Order tables as well as others via a
query.

The Subform is linked to Job and other tables via a query. This form
displayed job data one record at a time, not continuous forms.

The Subform had further subforms to enter BOM and Process routings in a
tabbed panel.

The user enxperienced the problem mainly when the focus was in the subforms
subform.

Eg. The user is looking at itewm 4 of an order. They are entering material
for item for in the subsubform. They look up and the subform has reverted
back to item 1 of the order.

I tried to cure this by changing to Acc200 and get the same results.

I then redesigned the Order screen so it wasn't using SubSubforms. The
flick back happens less but still happens.

Do you think that this could be a rogue code problem, Access clitch,
network problem or operating related.

 

[Dirk Goldgar]

Thanks for your response Dirk

The original database was in Acc97. The form was a sales order form.
The Main form is linked to Customers and Order tables as well as
others via a query.

The Subform is linked to Job and other tables via a query. This form
displayed job data one record at a time, not continuous forms.

The Subform had further subforms to enter BOM and Process routings in
a tabbed panel.

The user enxperienced the problem mainly when the focus was in the
subforms subform.

Eg. The user is looking at itewm 4 of an order. They are entering
material for item for in the subsubform. They look up and the
subform has reverted back to item 1 of the order.

I tried to cure this by changing to Acc200 and get the same results.

I then redesigned the Order screen so it wasn't using SubSubforms.
The flick back happens less but still happens.

Do you think that this could be a rogue code problem, Access clitch,
network problem or operating related.

The only time I've heard of anything like this happening was when there
was code running somwhere in the database that requeried the form(s).
But I can't say whether that's what's happening, or whether it could
have some other cause. Have you searched the entire database for
occurrences of the word "Requery"? There could be any number of
perfectly innocent cases where it occurs, but each would bear
examination to see if it could be causing this problem. Of course, if
the database contains macros, a macro could also be performing a
requery.

You didn't really answer these two questions of mine:

It would be especially helpful to know if the problem occurs when there
is only one user. If that's the case, then the next step in eliminating
problems would be to have one user working in a copy of the front-end
that is linked to a local, non-networked copy of the back-end. That
would eliminate any network issues.

 

[Guest]

I have seen it happen when only one user is using the database, but it
normally happens when the database is in full use by multiple users.

The users are rarely viewing or editting the same order. The same data is
displayed in other forms, but not edited in them. I have a sales order list
form which uses the same table.

I do use requery quite a lot. Mainly to renew combo boxes after the user
has gone to another form and then returned to the current form. I know that
when you requery a form focus returns to the first record, so don't requery
this subform.

Could Requery be having a more global effect? IE A requery happens on a
different form viewing the same job and this then cases the record to jump on
another users computer.

I did search the Forum for Jump, Return and other words that I thought
others may use if they were experiencing the same thing. I didn't try
Requery, but I will.

It is worth mentioning that my Front End database is over 20mb in size. I
started this thing in Acc2.0 and ran into many of its limitations before
switching to Acc97. Even then I have reached the Table limit a few times
before redesigning. I have a feeling that I may be pushing the boundaries
and this is what is causing the record jump.

Thanks in advance, I appreciate the difficulty of debugging without seeing.

 

[Dirk Goldgar]

I have seen it happen when only one user is using the database, but it
normally happens when the database is in full use by multiple users.

I'm not sure what this means. It might be that the odd behavior is
completely unrelated to the presence of multiple users in the database,
but there are usually multiple users in the database, so most of the
time when it happens, that condition exists. If it does happen when
there is only one user in the database, then it clearly isn't *caused*
by multiple users.

The users are rarely viewing or editting the same order. The same
data is displayed in other forms, but not edited in them. I have a
sales order list form which uses the same table.

I wouldn't expect that to have any impact.

I do use requery quite a lot. Mainly to renew combo boxes after the
user has gone to another form and then returned to the current form.
I know that when you requery a form focus returns to the first
record, so don't requery this subform.

Could Requery be having a more global effect? IE A requery happens
on a different form viewing the same job and this then cases the
record to jump on another users computer.

It shouldn't have any such effect. I'd be looking to make sure that
there is no code that does anything that could force the form or subform
to be requeried.

You mention other forms that are opened, presumably to add data to
supporting tables for combo boxes, etc. Could it be that one or more of
those forms requeries the calling form after a record is added, or on
close?

It is worth mentioning that my Front End database is over 20mb in
size. I started this thing in Acc2.0 and ran into many of its
limitations before switching to Acc97. Even then I have reached the
Table limit a few times before redesigning. I have a feeling that I
may be pushing the boundaries and this is what is causing the record
jump.

20MB isn't all that big, although it's a bit fat for a front-end, but I
don't know how many forms, reports, queries, and modules it has. By
"the Table limit", do you mean the maximum number of open tables, or
something else? If you were getting the "can't open any more databases"
message, your design may well be pushing the limits of Access. There
are usually ways to work around this problem, I'm told, though I haven't
actually encountered it myself.

The fact is, though, that I've never heard of forms requerying
themselves, even when that error has occurred. So I would keep looking
for something else that is causing the problem.

Thanks in advance, I appreciate the difficulty of debugging without
seeing.

If your database were smaller, I'd ask you to zip it up and send it to
me, but I can't really accept anything that large in e-mail.

More questions for you:

1. Is the back-end an Access (Jet) database, or is it SQL Server or some
other ODBC database? From what you've said, I have assumed the back-end
is an .mdb file, but I realize i'm not sure.

2. Does the table (or tables) on which this subform is based have a
primary key?

3. Is the form based on a query that joins two or more tables? If so,
is it possible that some process might be changing the field used to
join those tables?

 

[Guest]

1. Is the back-end an Access (Jet) database, or is it SQL Server or some
other ODBC database? From what you've said, I have assumed the back-end
is an .mdb file, but I realize i'm not sure.

Yes the backend is an Access2000 datafile in mdb form.

2. Does the table (or tables) on which this subform is based have a
primary key?

Yes, all the tables in my database have primary keys whether they initially
need them or not. I always use Autonumber and never rely on text keys.

3. Is the form based on a query that joins two or more tables? If so,

is it possible that some process might be changing the field used to
join those tables?

I thnik you may have hit the nail on the head with this one, but I will have
to check it out with the users.

The subform is based on a table called job linked to misc tables and itself.
The Orders and Quotes are stored in the same tables in my database to allow
the user to browse through old orders and quotes at the same time.

The flick-back normally occurs when the user is adding a BOM and process
route to a job from another job. To do this the user opens a Popup form that
displays jobs that match the current job. They can then copy the job and
details from the popup to the current job. If the job they are copying from
is a quote the JobId of that record is copied to JobQuoteJobId in the current
job. This allows the user to link the order to the quote. The query the
subform is based on uses this link to link table Job to Job_1.

This could be the cause, but it doesn't happen every time the copy from a
quote as they do this all the time.

 

[Dirk Goldgar]

3. Is the form based on a query that joins two or more tables? If
so,

I thnik you may have hit the nail on the head with this one, but I
will have to check it out with the users.

Any further results from your investigations?