Can't update subform - field cannot be updated Error 3164

[MSG56]

I'm creating a db for recipes. The main form (Recipes) uses a subform to display the ingredients. I have a button that allows the user to change the number of servings. It actually lets them multiply the original number of servings, as well as the Quantity of each ingredient by a factor. When they click this button it opens the Multiply Dialog form. Once the user enters their factor and hits the ok button I save the factor to a hidden, unbound text box on the Recipes form for later use, and I close the Multiply Dialog form.

Now that I have the factor saved I use a recordset to go through all the ingredients in the subform and update their quantities. After that I also update the # of Servings field in the main form. Because I don't want to permanently store the updated quantities I use the Form Before Update event (with a new recordset) and use the saved factor to divide all the values that I multiplied earlier to reset them back to their original values. (I also reset the saved factor to 1.) All this works great. But....

Now, that I've done all this work I can no longer enter new items in the subform. When I try I get a MsgBox telling me "Field cannot be updated." If I click OK I can then add something to that field, but I can't select from the list of items and I can't update other fields. If I click help I get "The field cannot be updated because another user or process has locked the corresponding record or table. (Error 3164)"



I can't help but think maybe I'm not doing enought house cleaning after using the recordsets. Anyone have any ideas? (I'm new at this so it may be an obvious mistake to you pros.)

This is what the code looks like that updates the ingredients:

Private Sub ChangeNumberOfServings_Click()

Dim rst As Recordset
Dim dblFactor As Double

On Error GoTo DataAccessError
DoCmd.OpenForm "Multiply Dialog", , , , , acDialog

DoCmd.Hourglass True
dblFactor = Forms![Recipes]![SavedFactor]
Set rst = Me![RecipeIngredients Query subform].Form.RecordsetClone
rst.MoveFirst

'Move through the recordset, updating each record
Do Until rst.EOF
rst.Edit
rst![Quantity] = rst![Quantity] * dblFactor
rst.Update
rst.MoveNext

Loop
Me.SetFocus
Me![Number of Servings] = Forms![Recipes]![Number of Servings] * dblFactor
rst.Close

DoCmd.Hourglass False
Exit Sub
DataAccessError:
MsgBox Err.Description
End Sub

This is what the code looks like that resets the values when moving to a new record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Forms![Recipes]![SavedFactor] <> 1 Then

Dim rst As Recordset
Dim dblFactor As Double

dblFactor = Forms![Recipes]![SavedFactor]
Set rst = Me![RecipeIngredients Query subform].Form.RecordsetClone
rst.MoveFirst

'Move through the recordset, looking at each record
Do Until rst.EOF
rst.Edit
rst![Quantity] = rst![Quantity] / dblFactor
rst.Update
rst.MoveNext

Loop
Me.SetFocus
Me![Number of Servings] = Forms![Recipes]![Number of Servings] / dblFactor

'reset the SavedFactor to 1
Me![SavedFactor] = 1
rst.Close

End If
End Sub

Attachment 17454

[ItsMe]

The query object RecipeIngredients Query subform is based on is probably not an updateable query. Try opening the query object in Datasheet view and editing the Quantity field.

[MSG56]

The query object RecipeIngredients Query subform is based on is probably not an updateable query. Try opening the query object in Datasheet view and editing the Quantity field.

You are right, I can't add new items in the query in Datasheet view. This is weird, because this was working fine before.


Anyhow, originally I was using a subform not based on a query and someone suggested using a query, so I did. And, it was working fine, but something has obviously changed. How would you suggest I proceed?

[ItsMe]

If your subform used to be based on a table then it would have been updateable. If the query is not updateable it is because of one of its joins (probably only has one JOIN).

Two options...

Adjust the query so it is updateable (maybe index the Foreign Key Field and adjust the JOIN as an Outer Join Left or Right).

Use code behind the form to update the field (maybe DAO or SQL or combination of both.)

[MSG56]

If your subform used to be based on a table then it would have been updateable. If the query is not updateable it is because of one of its joins (probably only has one JOIN).

Two options...

Adjust the query so it is updateable (maybe index the Foreign Key Field and adjust the JOIN as an Outer Join Left or Right).

Use code behind the form to update the field (maybe DAO or SQL or combination of both.)

Thanks - I think I'll try ditching the query and going with a subform based on the table the way I originally had it.

[ItsMe]

Maybe you can change
Set rst = Me![RecipeIngredients Query subform].Form.RecordsetClone

to
Set rst = CurrentDB.OpenRecordset("tablename", dbopendyanset)

[ItsMe]

Make sure you are working in a copy as you test and develop!