Query not updateable

[terrytek]

Query:

SELECT tblTesting.TestID, tblTesting.StudentID, tblTesting.TestDate, tblTestType.TestType, tblTesting.Form, tblTesting.SS, tblTesting.GE, tblTesting.NextForm, tblTesting.Memo
FROM tblTestType INNER JOIN tblTesting ON tblTestType.TestTypeID = tblTesting.TestTypeID
ORDER BY tblTesting.StudentID, tblTesting.TestDate DESC;

This pretty simple query does not allow data entry in datasheet view; why?
I looked at http://allenbrowne.com/ser-61.html
but still cannot figure out the problem. tblTesting.TestID and tblTestType.TestTypeID are primary keys and are indexed to allow no duplicates. tblTesting.TestTypeID is a Number data type. If I remove the join between the tables, the query is then updateable, but I need tblTestType.TestType in the query for the form that is based on the query. Query recordset property is set to Dynaset. I must be missing something simple?

 

[MarkK]

This is why there is a ComboBox control. Bind the form to the tblTesting table only, and then use a ComboBox on the tblTesting.TestTypeID field. The combo can look up the values in the tblTestType table, and when edited, supply the value to that field.

Properties of the combo you want to be concerned with are ColumnCount, ColumnWidths, ControlSource, RowSource, BoundColumn.

 

[sneuberg]

I agree with MarkK but it looks like that query should be updatable and it is in the attached database where I tried it. Maybe you can look at it and see what's different in your situation.

 

[terrytek]

Found the problem: I added tblTesting.TestTypeID to the query, and now it is updateable. I did not see the error listed in the status bar before: "Cannot add record(s); join key of table 'tblTesting' not in recordset."

 

[terrytek]

I agree with MarkK but it looks like that query should be updatable and it is in the attached database where I tried it. Maybe you can look at it and see what's different in your situation.

When I save a version of your db to my computer (to remove the read-only provision), your query is not updateable either.

 

[sneuberg]

I made the the query a record source in a form and I think I see what you mean. I can edit records but I can't add records. If this is what you mean by not updateable then it isn't.

If you stick to one table per form things work better.

 

[terrytek]

I made the the query a record source in a form and I think I see what you mean. I can edit records but I can't add records. If this is what you mean by not updateable then it isn't.

If you stick to one table per form things work better.

Yes, I should have made that clear; I needed the query to be able to add new records to its underlying table via form.