Jumping records

[Jack at Relaxed]

I have a stand-alone database used by a several users on different machines
but only by 1 person at once (hence no need to split). One of the users will
enter data into fields on a form and then when she moves to another field to
enter data using the mouse or tab key the record jumps to the next one (i.e.
the next ID) but taking with it, the data she has just entered thereby
attachiing it to the wrong record. This only happens intermittantly however
and months can go by without the fault occuring. There have been lots of
changes such as new machines, a new server, upgrades etc but each time we
think the problem has vanished, it re-appears. The form is based on a query
that contains several tables that have one-to-many relationships as well as
look ups etc! Please can anyone help!! Thank you

 

[John W. Vinson]

The form is based on a query
that contains several tables that have one-to-many relationships as well as
look ups etc! Please can anyone help!! Thank you

That may be the problem. Multitable queries *can* be updated but it's tricky
and rarely the best approach.

Consider using a Form (for the "one" side table) with one or more Subforms
(for the "many" side tables); remove ALL your table lookup fields (see
http://www.mvps.org/access/lookupfields.htm for a critique of what many of us
consider a misfeature), and use Combo Boxes on forms instead.

 

[Jack at Relaxed]

John - many thanks for this. Each record in my form is based on a Contact who
is being assigned an organisation (which in turn has been assigned an
address) and a job type (one organisation can have many addresses which is
turn can be assigned to many contacts and one job type can be also assigned
to many contacts). I do use combo boxes as a way of assigning these
organisations and job types to the contact. I mis-lead you by the term
look-up - I don't use look-ups in tables, I create separate tables with the
values and then create a realtionship, so my tbl_Contact has a 1-many
relationship with my tbl_Address which then has a -many relationship with
tbl_Organisation etc. I have built many databases for may users that follow
this exact structure and have never come across this problme before!
I heop thsi has made it a bit clearer now. Thank you in advance.

 

[IMA]

Have you ever looked into "tab order"?
At the last field in the tab order llist,
user press down a tab key,
next record will appear.

AND, there is an option of Access,
You can choice the behavior,
When user pressed down "ENTER" key, which action should be done.

 

[Jack at Relaxed]

Hi, yes, I had the same thought so have stopped the tab going to to the next
record by choosing Current Record under Other/ Cycle. The return key works
like the tab in that it goes from field to field and infact eventually goes
to a command button for a new record and so gives you a new record!
I wonder whether there may be a connection issue here between the database
and the server on which it sits???
Many thanks for the responses so far!!

 

[John W. Vinson]

John - many thanks for this. Each record in my form is based on a Contact who
is being assigned an organisation (which in turn has been assigned an
address) and a job type (one organisation can have many addresses which is
turn can be assigned to many contacts and one job type can be also assigned
to many contacts). I do use combo boxes as a way of assigning these
organisations and job types to the contact. I mis-lead you by the term
look-up - I don't use look-ups in tables, I create separate tables with the
values and then create a realtionship, so my tbl_Contact has a 1-many
relationship with my tbl_Address which then has a -many relationship with
tbl_Organisation etc. I have built many databases for may users that follow
this exact structure and have never come across this problme before!
I heop thsi has made it a bit clearer now. Thank you in advance.

IMA's suggestion may be on target - try setting the Form's Cycle property to
"Current Record" so that tabbing around will stay on the same record.

It could also be form corruption of course; has this user's database been
compacted and repaired? Do you have any plans to split the app? Doing so would
be cheap insurance inasmuch as you could have a "clean" frontend backup
available and needn't worry so much about losing your data to corruption.

 

[Jack at Relaxed]

Thanks John, yep, the Current record option done as per previous thread -
pleased that we are all thinking the same thing here - I agree that the form
could be corrupted. The really weird thing is that the data just entered in
one record gets assigned to another which is why I wonder of there is a
connection issue here.

 

[John W. Vinson]

Thanks John, yep, the Current record option done as per previous thread -
pleased that we are all thinking the same thing here - I agree that the form
could be corrupted. The really weird thing is that the data just entered in
one record gets assigned to another which is why I wonder of there is a
connection issue here.

It's just barely possible that you have a damaged Index on the table. Try
opening the table in design view; removing all the indexes including the
Primary Key; compact the database; recreate the indexes (and any
relationships). May be overkill but that should clean up any bollixed indexes.

 

[Jack at Relaxed]

Thanks John - I will try this for definite and see how it goes!
Many thanks for the advice
Jack