I'm making a simple mistake somewhere

[Kraj]

I have a table that gets records appended to it while leaving one field, Date_Field, blank. I have a query that grabs all the records in the table where Date_Field Is Null. Then I made a form on the query and I want to input a value for the Date_Field but it tells me recordset is not updateable. I tried changing the control source to the field. It said the form is read-only. I know I'm overlooking something simple but it's just not clicking. Anyone care to expose my ignorance? I'd appreciate it.

 

[shacket]

Queries are not updatable. You cannot add or remove info from them. What you need to do is create an update query to update all records in that table where Date_Field is null to the date you type in. You would not even need a form to do this if you used a parameter in your update query. But you cannot update the query itself. You need to update the table upon which the query is based.

 

[Kraj]

Problem is that not all the records need to be updated. I have to select which records to update so that the others can be updated later. Thanks for trying, though.

********************************************

I did, however, think of a way to solve the problem for anyone interested. I did away with the query and instead created a form on the table itself. I then added a subform on the table I had been using the query to link to and changed the default view to datasheet. The drawback is all the records are shown, but if I simply go to the last record, I'll see all the recent records that need to be updated. The information I needed from the query is provided by clicking on the subdatasheet. Not perfect, but it works.

 

[Pat Hartman]

shaket's blanket statement that queries are NOT updateable is incorrect. In fact, quite the contrary, most recordsets produced by queries ARE updateable, even those that use multi-table joins. When you update/delete/add rows in a recordset produced by a query, you are actually making permanent changes to the underlying table.

Some common problems,
1. Underlying table does not have a primary key.
2. The primary key is not included in the select clause of the query.
3. The query uses aggregate functions such as Sum(), Min(), Avg(), etc or includes a Group By clause.

It is good practice to base all your forms and reports on queries rather than on the tables directly.

To see if something about the form is causing the problem rather than the query, run the query by itself and see if it is updateable.

[This message has been edited by Pat Hartman (edited 09-02-2001).]