Subform Cascade Delete

[gdgonzal]

Hello All:

I embedded a subform within a main form by dragging it and then using the Link Master Fields ID from the main form's tbl to the Link Child Fields in the subform's FK. So, as I preceive it, the form itself is maintaining the relationship. I've included a screenhot so you can see what I mean. My problem is that when I delete the main form using a delete macro my subform is leaving orphaned records. As this is a Access Services Web Database and I cannot maintain relationships in the normal fashion I was wondering how do accomplish a proper cascading delete?

 

[MStef]

Send a short example of your MDB, (Access2000 or 2002-2003).

 

[gdgonzal]

It's a Access 2010 .accdw...I suppose I could save as a local .accbd...would this work for you?

 

[KenHigg]

I'm thinking you need to set a up a relationship for the tables (outside the form) and configure the cascade delete option.

 

[gdgonzal]

Thanks Ken,

This was exactly my initial thought; but when I added the lookup from my subform's child FK all that did was create a drop-down. So now I have an unpopulated drop-down field...how do I get it to auto-populate so the delete cascades? I cannot expect my users to maintain the FK value from the drop-down in my form so I am not sure how to get passed this.

 

[KenHigg]

I'm still not following this entirely but maybe you just need to refresh or requery the drop-down after the record is deleted in the main table?

 

[gdgonzal]

Ken,

Thanks for your reply, this isssue is specific to how Access Services and Web Databases maintain relationships. It's not like normal, you cannot link to tbls together using the Relationship button...in fact, this is disabled. What you can do is create a lookup from one tbl to another by adding a lookup column. Now if you do this all you're really accomplishing is creating a drop-down with whatever value(s) you've based your lookup from on the other tbl. And this where I am stuck; ok so I cannot create a lookup using relationships in the normal fashion. All I can do is relate tbls together using these look-ups...what now?

 

[gdgonzal]

OK I got it...I see what I did wrong. When I first created my child tbl I used a a Number field to house my FK. What I should have done was used a lookup instead to my main tbl's parent id. So the sub-form's Master & Child field now auto-populate the lookup FK.
Well I suppose this is what I get, for years I have been using InfoPath as my frontend and Crystal Reports for my reporting. So I never really learned how to use Access reports or forms and now I am paying the price for my bias.
Thanks All...for making me think about what I was doing.