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