To Link or not to Link subforms

[mmitchell]

I am converting my MDB app to an ADP and MSSQL back end and am rethinking my position on subforms. Most of my forms have tabs, generally 3-5, on them containing subforms. And the user generally selects the record they want to work with on the header form or on the first tab.

My question is/are this/these:

Which is better, by better I think I mean faster to the end user (but not limited to that, I guess):

1. To have each subfrom using Parent and Child links
or
2. Set all the subforms on all the subsequent tabs to SQL strings or sp with parameters (there is another question up for debate) in the After Update event of the combobox on the Header form or on the first tab which ever the case may be.
A. If so, should I set all the subforms at once or when they click on the specifc tab? An/or will that cause binding/late binding issues?

My only thought is that with using the Parent/Child way, I almost have to bring over all the underlying records and if I use the SP or String way then I only bring the records I need over, but will that time be equalled in the time it takes to run all the individual SP or Strings every time the user selects a tab (oh, there is another question, how can I not fire the SP or String again and again if the user is just going from tab to tab but not selecting a new record from the combobox?!)

I am open for any thoughts and insights on this subject.

 

[WayneRyan]

Mitch,

I'd use the Master-Child relationships if possible. Why not let Access do
all of the work? The only reason to stray from that would be if there were
so many child records that there was a noticable lag in processing.

Then you'd have to streamline the subset of child data with a query
or SP.

Wayne

 

[KenHigg]

Just a couple concearns.

1. Do all your subforms display in continuous forms mode?

2. Will pop up forms work for some of your data?

???

 

[mmitchell]

They all are in continuous mode

Pop up would work, I'd have to change ALL of my forms for the most part and add the code to pop them up and remove ALL the tabs from ALL of my forms. That would not be pleasant.

 

[KenHigg]

Oh well...

I did a db once that had a main form with a bunch of subforms, calculated controls, etc., trying to make it an all inclusive type form, and it was a real dog, performance wise, when we tried put it on the network. And it didn't have that many records in the main table...

 

[mmitchell]

I'm not sure I understand what your point is, but I think you are saying not to have any data on any form "pre-loaded" execept for whatever the user is currently looking at. Is that correct?

 

[KenHigg]

We just over did it. We must have had 30-40 tables all related every which way. Then we tried to get the db to do all the work off one form that had several pages with subforms everywhere. We should have had multilple sets of related forms that could be used for different areas of the busniess process. And we could have de-normaized a bit...