Record on screen isn't the record that Access thinks it is - HELP! 1

[aexley]

Firstly, my apologies for the misleading thread name on my previous post, I've resubmitted in the hope that a more accurate thread name will elicit a response. Thanks for your understanding.

I have a F-E form that is based on a query that looks at my B-E tables. Occasionally if I change information in the record on this form (directly or by code from a linked form) Access doesn't change the information in this record but does in an apparently unrelated record several hundred ID's away. This is VERY, VERY frustrating and confusing. I really need some pointers as to what might make this happen so that I can at least start some intelligent investigation. Below are a number of points that I believe are relevant:

1) I can (because of Admin permissions) go into my B-E table and update the information there with no problem.

2) The link is consistent i.e. if Access jumps to a particular record from the record I'm working on it will always jump to that record.

3) The error carries between linked forms. I have a linked form that has a subform, one of the fields of which should display the ID number of my record. What it displays is the ID number of the incorrect record.

4) If you move to the next record when at the incorrect record Access jumps back to original, correct record + 1. Just as if you had moved to the next record while at the original.

This is really urgent.

Absolutely outstanding thanks to anyone who can help with this.

aexley: "Fortune vomits on my eiderdown again." - Cpt. Edmund Blackadder

 

[grnzbra]

I've seen something like this happen when the fields being used as key fields weren't set to unique and the key values were entered manually. A value was entered that had already been used and any changes that one attempted to make in the second record always occurred in the first.

 

[aexley]

Interesting point.

I've just checked my Primary key fields on my B-E tables and the ones that don't (for my sins) use Autonumber are set to 'Indexed (No Duplicates)'. However the queries that access these tables (and on which my forms are based on)don't have their unique properties set.

I didn't feel it was necessary as I figured the 'No duplicates' property would still apply anyway in much the same way as permissions do. Am I correct in assuming this?

Maybe I should run a 'locate duplicates query'.

aexley

 

[grnzbra]

My problem was due to one of the table's key field having a duplicate value, so that's not your problem.

Also, don't think the query's Unique property is used that way. My understanding is that if you want a list of employees last names and don't want 25 Smiths and 15 Jones you would set the query to unique and would only get one. However, along that line, if you have two similar records, say two Smiths and you want to change one of their hourly rates, and the other smith is getting the change, you may not be looking at the record you think you are looking at. See if there is any similarity between the two records that might confuse the form (Or check the record before and the record after ON THE FORM to be sure you are changing the record you think you're changing. Put the key field on the form and make it visible and then look in the table after the change and see what the key was.

 

[Garridon]

This is probably a really odd suggestion, but it won't hurt to do it. Maybe the database index is corrupted. I have to deal daily with database corruption, and I can tell you it causes some really strange problems. I had a linked form like yours, and when it was about to corrupt, it would enter the contents of the linked form into a different record than what it was supposed to be associated with. On a subform for a many-to-many relationship, it even added records on its own! Try running compact and repair on the back end (probably the front end as well). You might also try importing the back end into a new blank database.
Linda Adams
Linda Adams/Emory Hackman Official Web site

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[aexley]

*!!!* !*!*!!** Blinkin' flippin' 'eck!

Ok, (deep breaths) I followed Garridons suggestions and everything was working fine. Until today. Annoying as this problem is I have been able to narrow the problem down to just one control or field. My NextCallDate field.

I don't think it's the code that's behind the control as that appears to be completely innocuous (See below)

Private Sub NEXT_CALL_DATE_BeforeUpdate(Cancel As Integer)

If (IsNull(Me![NEXT CALL DATE])) Then
Exit Sub
ElseIf Me![NEXT CALL DATE].Value > DateAdd("m", 6,_ Date) Then
If (MsgBox("You have entered a date that is greater than six months away." & " Is this correct?",_ vbYesNo, "Please confirm date.&quot = 7) Then
Cancel = True
End If
End If
End Sub


Pretty innocuous, eh? So I figure it's not the code. I don't think it's the B-E tables as I imported all the tables into a fresh DB. So I'm left with the queries on the F-E that my forms run off. Could it be those causing the problem? Should I import my front end into a new DB? I'm a bit loathe to do this as it means recreating the replicated F-E's, updating shortcuts and generally a bit of faffing around. However, if it's the only suggestion then I'll give it a go.

Many thanks in advance for your help.

aexley

P.S. It jumps 500 records - sometimes up, sometimes down

 

[aexley]

Note: Just imported all DB objects into a new, clean DB. Problem still there. Am at wits end.

aexley

:-(

 

[KenReay]

Hi

Which version of Access are you using?

There is (was?) a bug in Access 97 which under some conditions cuased the wrong record to be displayed, when using the bookmark property.

I think it was fixed by a service pack.

Are you using A97, and .bookmark property?

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[aexley]

Thanks KenReay.

I'm using Access 2000 and although I do use 'bookmark' it's on a completely different control. Also I removed the Event Procedure from the 'NextCallDate' control and the error still occurs.

aexley

 

[aexley]

Ok. Update on current problem:

On my main form one of my controls is a text box whose source is the Autonumber ID for the current record. If I click in this box before I change anything nothing happens, however if I change something, anything, on the current record and then click in the text box the ID number changes to the ID number of erroneous record as described above BUT, and I feel this is important, the rest of the screen DOES NOT CHANGE.

I really, really, still need help with this issue. I think that with Tek-tips help I am beginning to close in on the problem. (he says hopefully)

I STILL REALLY NEED SUGGESTIONS. ANY HELP/THOUGHTS ON THIS WOULD BE EXTREMELY APPRECIATED.

I'll do your ironing for a month, wash your car for a year, (desperation is setting in).

Thanks in advance for any help.

aexley