Heenan, Lambert
Lambert.Heenan at chartisinsurance.com
Wed Jan 11 09:43:36 CST 2012
Sounds like the table LotControl has become corrupted. Have you tried running a make table query to create a copy of LotControl? After that make sure the copy still has a PK setting for the LotID field in the copy, then rename the two tables so the new one winds up being called "LotControl". Might work. Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Wednesday, January 11, 2012 9:55 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Sleuthing - query has become non-updateable More info: In the References diagram there is no join between the PK LotID of the LotControl table and the FK LotID of the LotDetail table. When I try to create one with referential integrity enforced I get a message No unique index found for the referenced field of the primary table. However, LotID in the primary table - LotControl - is a PK and autonumber. A quick summation query with count of LotID shows each LotID only occurs once. Any clues there? TIA Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Wednesday, January 11, 2012 6:35 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Sleuthing - query has become non-updateable Dear List: Yesterday a client sent me a back end that had become corrupted - front end would not start because the record source for the opening form referenced a corrupted table. In one of the tables I found a record with #Deleted in all of the fields. The autonumber ID seemed to indicate that there was another record missing - the record before the #Deleted record was 2 less than the record after. But I deleted that record and all seemed well. However, a query for another another sub-form now does not allow a record to be added or edited - there's no empty line at the end of the query and the >* is grayed out. And an attempt to change any record gives me that annoying ding. I ran this query in an older copy of the back end and it works. So I know there's some data problem in the current back end that I'm trying to repair. There are three tables in the query and each in table view allows adding and editing of records. But if I structure a query of just two of them - a LotControl table (LotID is PK) and a LotControlDetail table (LotID is FK) the query is not updateable with any of the three join types. I haven't a clue as to where to look next but I suspect, of course, that the LotID is hosed in one or the other table. But inspecting the LotID visually shows nothing out of the ordinary. Any ideas on where to look next to identify the offending record(s)? MTIA Rocky Smolin Beach Access Software 858-259-4334 www.bchacc.com <http://www.bchacc.com/> www.e-z-mrp.com <http://www.e-z-mrp.com/> Skype: rocky.smolin -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com