Microsoft JET database engine ERROR - Please help

[sacheson]

I am receiving an error that reads: &quot;The Microsoft Jet database engine can't find a record in the table 'tblA' with key matching field(s) 'tblB.name_id'.&quot;<br><br>I am receiving this message when I attempt to update tblB field name_id with a form that references a query with tblA and tblB.<br><br>I am receiving this message when I have a relationship between tblA - name_id and tblB - name_id, and after I have dissolved the relationship.<br><br>I can supply more info if needed ... thanks in advance for any help.<br><br>

 

[sacheson]

OK, I have figured out the source of the problem ... tblB has 4 fields that are Primary Keys - Indexed Duplicates OK (so duplicate combinations of the 4 fields are not entered).&nbsp;&nbsp;When I change these fields to no PRIMARY KEY and NO INDEXING, the update works.<br><br>Any thoughts on getting this to work while retaining the INDEXING?&nbsp;&nbsp;OR is there another way I can be sure to not have multiple combinations of the 4 fields?

 

[MelF]

I'm having this EXACT same problem, except it's only involving two tables. I removed the indexing from the first table, but can't from the second table because it's the primary key that's indexed. Does anyone have any idea why I'm getting this message, and what I can do to correct it? Thanks!!!

 

[sacheson]

MelF,

I experienced this problem a while ago - solved it and forgot about it. I hope I am supplying the correct information here ... [disclaimer: if I am not, don't hold me responsible!].

If I remember correctly, I had a form with indexed tables I was trying to update via the form. I was experiencing the problem because Access updates the data when you exit the entry screen on the form (by either exiting or going to the next screen) - NOT when you enter the data. The internal validation of the data was conflicting with my data entry and causing the error.

I think when you remove the indexing the validation is limited and allows the update.

You should be able to remove the indexing if the table does not have a relationship with any other tables (because of this, I had to go through my whole database and redo all relationships - talk about a pain in the butt!).

Good luck.

Sam

 

[PaulF]

don't know if this will solve the problem or not, but you might change the Form's Recordset Type to Dynaset(Inconsistent Update). This has cleared up some problems with recordsets that were not updateable.

PaulF

 

[MelF]

SACHESON - Yes, my the table is related to another table. Thanks for the suggestion though!

PAULF - I changed it to Dynaset - Inconsistent, and the problem is still on-going. The form has always updated fine - As in a new record always pops up (and the new record is also relating to the main form's record). It's just after I enter all of the info. it's giving me this message. I don't know what could be going on!!

 

[MelF]

Something else I'm thinking about. It seems as though the info. entered on Form I has not been saved yet by the time I'm into Form II. And as the CustomerID appears on both forms (and is the field I'm asking Form II to match with so that they'll be on the same record) maybe the record from Form I has not yet made it to the table, so by the time I get to Form II - it can't find a matching field?? I have a good feeling this may be the problem, so I'm trying to find a way to get the form to save for the first time immediately after the CustomerID is autopopulated (It's autonumber). I wrote a Save macro and tried putting it in a few places in the Event properties for the CustomerID on my main form. It's not working!!!