[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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?
1 Solution
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now