SubForm / Trigger Problem in Access XP ADP

Posted on 2004-11-11
Last Modified: 2006-11-17
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?
Question by:rmk
    LVL 9

    Expert Comment

    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.

    Author Comment

    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.
    LVL 58

    Expert Comment

    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
    LVL 58

    Expert Comment

    Addendum: the field txtDummyParent is used as LinkChildField in this case...

    Author Comment

    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.

    Accepted Solution

    PAQed with points refunded (500)

    Community Support Moderator

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now