SubForm / Trigger Problem in Access XP ADP

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:

Variation #1
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

Variation #2
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.

Variation #3
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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Howabout a combination of Variation #1 and #3?

Use the recordsource from var #3 and on the on_current just pass a .requery command to the subform.

Seems like a cheap fix, but if you said that requerying the subform works, this might be a viable workaround to a problem that as you have mentioned, has already taken 3 days to resolve.
rmkAuthor Commented:
Tried that too and no luck. I've been trying to mimic my problem with a minimal database, minimal tables, and minimal columns and can't duplicate the problem. So, now I'm going to try a form re-design using datasheets and sub datasheets in the hope that the problem will somehow disappear; the downside is that I lose all my buttons in the form headers /footers.
Access forms have a non-exposed mechanism that stores the logical relations between controls, in order to perform the refresh of  dependant controls while you edit. This mechanism is exposed in Excel (trace dependant cells) but not in Access because it's a little more complex.

When this doesn't work, you have to use .Requery or .Recalc to obtain the desired result...

To help Access understand the relationships between your objects, you need to use the object names *on the form* and not in the underlying queries. this is what you do with "=fsfrParent.form!txtParentID". As the name of the subform control is there, Access will take over the refresh of the text box whenever something changes in the subform...

To do the same thing with a subform, the only place to put information is to add "txtParentID" in the LinkMasterField of the subform. Of course, the subform needs a corresponding field to put in LinkChildField. Now, whenever fsfrParent changes its cursor, txtParentID is recalculated and then fsfrChild is requeried.

The funny thing is that this works also with dummy fields. Let's imagine that the query in fsfrChild does not contain a corresponding field as such, because the relationship is rather more complex... You can still use this by adding a new column to the query with, for example "txtDummyParent: Parent!txtParentID". Silly, I know, but this will still force the requery whenever txtParentID is recalculated...

Just another trick :)

Good Luck
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Addendum: the field txtDummyParent is used as LinkChildField in this case...
rmkAuthor Commented:
The LinkMasterField and LinkChildFields didnt help.

Datasheets and subdatasheets didn't work either. In fact they produced different problems. So, I've gone back to my original desing of multiple continuous subfroms side by side. The critical factor seems to be a combination of two things, i.e. if the form is based on joined tables and has combo boxes, that's when problems start to occur; otherwise everything seems to work fine. So, if my subform uses joined tables and combo boxes I've got it working by doing the following things:

- set the recordsource to a stored procedure
- in the Form_AfterInsert event, execute a me.requery. This still leaves me with a cosmetic issue if the subform has more rows than can be seen in the window. For example, if the window has room for 10 rows and I'm trying to insert the 14th row, I will be inserting in the 4th row from the top of the window. When I requery, it shows up at the top of the window instead of in the 4th row.
PAQed with points refunded (500)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.