SubForm / Trigger Problem in Access XP ADP
Posted on 2004-11-11
I'm running Windows XP Pro and Office XP Pro with ALL software updates.
I have a table tblChild with the following columns ChildID int primary key, ParentID int (foreign key), ... , UpdatedCounter int. The update trigger increments the value of UpdatedCounter. BTW, I've found that I also have to use SET NOCOUNT ON in the trigger or Access doesn't see the updated value. I have an unbound main form frmContainer with 2 bound continuous subforms called fsfrParent and fsfrChild. The main form has a text box called txtParentID with its control source set to =fsfrParent.form!txtParentID. This is my mechanism so that fsfrChild is requeried each time I move to another row in fsfrParent.
I have problems with all 3 of the following variations for the record source of for fsfrChild:
The recordsource of fsfrChild is an SQL statement that is dynamically created in the Form_Current event of fsfrParent, e.g. SELECT * FROM tblChild WHERE ParentID=1 ORDER BY ...". In this case whenever I insert a new row, the new row "disappears" on the form and I get the error message about the data having been changed by someone else. If I requery the subform the inserted row appears
The recordsource of fsfrChild is a parameterized SQL statement, e.g. SELECT * FROM tblChild WHERE ParentID=? ORDER BY ..." The InputParameters property is set to ParentID INT = Forms!frmContainer!txtParentID. Inserts now work properly but updates don't. Although updates actually occur the value of the text box txtUpdatedCounter which is bound to the UpdatedCounter problem does not show the updated value. That leaves me with the problem of Access thinking the data has been updated by someone else.
The recordsource of fsfrChild is a stored procedure procfsfrChild. The InputParameters property is set to @ParentID INT = Forms!frmContainer!txtParentID. Although inserts actually occur, the row that is displayed shows different values than I entered. Requerying the subform shows the inserted row with the correct values. Updates work properly.
I never have this problem with main forms, it only occurs with subform, but not on all my subforms. This problem is driving me nuts and I've now wasted 3 days trying to find a solution. Anyone have any ideas?