Subforms - What is the Best Approach?

[cascot]

I have a subform object that, depending on user input, will display one of several forms. Two are continuous list-type forms, the others display details of a single record.

My problem is that using the SourceObject, LinkMasterFields and LinkChildFields properties of the subform doesn't seem to be of much use when you need to change those properties on the fly. It appears that as soon as you set the SourceObject property the named form is immediately opened, which means that if the query underlying the form returns 3000 records then that's what's going to happen until the LinkMasterFields and LinkChildFields properties are then set. I thought it would be possible to set the SourceObject, LinkMasterFields and LinkChildFields properties to "" (or Nothing) and then set the LinkMasterFields and LinkChildFields properties before specifying the SourceObject, but it doesn't work like that.

It seems I am going to have to drop the idea of using the LinkMasterFields and LinkChildFields properties and instead set the RecordSource for each form that may appear in the SubForm to return zero records. Then once that form is set as the SubForm's SourceObject I will need to call a procedure local to the form that will amend the forms RecordSource with the criteria pointing to the relevant field on the Parent form. But such an approach doesn't seem that efficient.

Does anyone have any thoughts or advice on this aspect of using a subform object for displaying multiple forms?

 

[alvechurchdata]

Could you have three sub-forms and flip their Visible properties so as you only see the right one?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[cascot]

alvechurchdata,

Thanks for your reply.

I'm not sure quite yet, but I think that approach may still suffer from some of the same problems. For example when a subform is hidden would it already have it's SourceObject defined? If it did then there would be the problem of what to set the subform's RecordSource to until such times as it was due to be used (i.e. until there was a criteria on which to filter it's underlying recordset). So it would be necessary to set the RecordSource to a Null resordset (a RecordSource that would return zero records), otherwise with the lack of any criteria you would again be exposing yourself to the possibility of initially opening a Recordset with 3000 (or whatever) records.

If each Subform DIDN'T already have it's SourceObject defined then in some ways you would be back to square one. Initially (I think) you could preset the LinkMasterFields and LinkChildFields properties and then set the SourceObject just before making the Subform visible, but even if that did work what do you do when you want to move to another subform and then back to this one? I think similar problems to those I mentioned in my initial posting would come into play.

It just doesn't strike me as that efficient an approach, but then there don't seem to be too many alternatives.

 

[LittleSmudge]

Have three sub forms and toggle the .Visible property.

Set the LinkParent/Child in each one as appropriate at design time and leave well alone.
Also set the RecordSource of each subform to ""

Then along with setting the .Visible property, you also set or blank out the RecordSource property on each subform.


'ope-that'-elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[cascot]

I couldn'd find any alternative approaches that involved sticking with the use of the LinkMasterFields and LinkChildFields properties, so I ditched those entirely and went with the following...

- I still have only one Subform object

- The RowSource of each form that will be opened within the Subform is initially set to a Null query (one that contains all the required fieldnames but returns zero records)

- After amending the SourceObject of the Subform from the Parent form I call a procedure within the child form which then sets the child form's RowSource

It appears to work well and is faster than the original approach. It also guarantees that I am not opening larger recordsets than required before the LinkMasterFields/LinkChildFields criteria kick in.