Query appears to be locked - cannot edit or enter data 1

[Shinken]

I originally posted this in a different forum, believing it to be a form problem rather than a query problem. The query was working fine for several months, and then one of the rows in the Customers table became corrupted.

I did a compact & repair on the table, but now, although everything is properly displayed, I cannot enter data into either the query or a form based on the query (and of course, the form also displays properly).

I can enter data directly into either of the tables included in the query, but not into the query or the form itself. Other queries and forms are fine.

The New Record navigation button (rt arrow w/asterisk) is greyed out on the form and the query, and my New Record button on the form yields an error message that "You can't go to the specified record".

The query appears to be locked, although no record locks are set. In any case, this DBMS isn't networked, it's just a little DBMS I use to keep track of my clients and tech support calls.


Here's the query:

SELECT ServiceRecords.ServiceRecordID, ServiceRecords.ServiceDate, ServiceRecords.ProblemDescription, ServiceRecords.LaborHours, ServiceRecords.EstimatedCost, ServiceRecords.ActualCost, ServiceRecords.Comments, ServiceRecords.NextServiceDate, ServiceRecords.DateCompleted, ServiceRecords.Closed, ServiceRecords.CustomerID AS [Service Records_CustomerID], ServiceRecords.[Service Description], Customers.CustomerID AS Customers_CustomerID, Customers.FirstName, Customers.LastName, Customers.Address, Customers.City, Customers.State, Customers.ZIP, Customers.PhoneNumber, Customers.EmailAddress
FROM Customers INNER JOIN ServiceRecords ON Customers.CustomerID = ServiceRecords.CustomerID;

If I take the query and remove the join, so that it's only selecting columns from the Customers table, or only columns from the ServiceRecords table, everything is fine and I can enter or change data in the query. But when I add the JOIN, the query is displayed properly in datasheet view, but I can't enter or change data.

I've even tried reducing the SQL statement to it's barest essentials; including only the key & related columns and a single non-key/non-related data column from each table. The datasheet view of the query remains display-only.

One last thing, the related columns (CustomerID) are whole numbers (1, 24, etc) and there are no invalid references - that is to say, all CustomerID numbers listed in the ServiceRecords.CustomerID column are valid in the Customers.CustomerID column.

I've tried recreating the query from scratch, with the same results. Can anyone shed light on this problem?

Thanks,

S

 

[PHV]

Have you checked the RelationShips ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shinken]

PHV - aaarrrrrggggghhhhHHHHHH!!!!!

When the Customers table was repaired, the CustomerID column was dropped as the primary key. I reset it as the primary key and all is fine now.

Great call.

Thanks,

S