Loading Subforms Problem

[Ecohouse]

I have a main form with two subforms. The second subform has a combo
box whose recordsource is set based on a field in the first subform.

I tried putting some code in the first subform to do this. This is the
code in the Form Current section:

strSQL = "SELECT * FROM ModuleComponent s WHERE TrainingModuleT opicSK =
" & subTrainingModu le.Form!Trainin gModuleTopicSK & ";"


submodulecompon ents.Form!cboMo duleComponentTo picsSK.RowSourc e = strSQL

This wasn't working because the subforms load before the main form.
So I went to the main form and for each subform I removed the
Sourceobject. Then I set the sourceobjects for the subforms through
code. This is the code in Form Load:

With Me
..subTrainingMo dule.SourceObje ct = "subTrainingMod ule"
..subModuleComp onents.SourceOb ject = "subModuleCompo nents"
End With

I don't get an error message now but the subforms come up empty. So
now I'm stuck. Does anybody have any ideas where I'm going wrong with
this?

Thanks in advance for the help.

[CDMAPoster@FortuneJames.com]

Re: Loading Subforms Problem


Ecohouse wrote:

I have a main form with two subforms. The second subform has a combo
box whose recordsource is set based on a field in the first subform.
>
I tried putting some code in the first subform to do this. This is the
code in the Form Current section:
>
strSQL = "SELECT * FROM ModuleComponent s WHERE TrainingModuleT opicSK =
" & subTrainingModu le.Form!Trainin gModuleTopicSK & ";"
>
>
submodulecompon ents.Form!cboMo duleComponentTo picsSK.RowSourc e = strSQL
>
This wasn't working because the subforms load before the main form.
So I went to the main form and for each subform I removed the
Sourceobject. Then I set the sourceobjects for the subforms through
code. This is the code in Form Load:
>
With Me
.subTrainingMod ule.SourceObjec t = "subTrainingMod ule"
.subModuleCompo nents.SourceObj ect = "subModuleCompo nents"
End With
>
I don't get an error message now but the subforms come up empty. So
now I'm stuck. Does anybody have any ideas where I'm going wrong with
this?
>
Thanks in advance for the help.

Here's a way I do dynamic source objects:

NameOfSubformCo ntrol.SourceObj ect = NameOfSumForm

E.g.,

SubformIDO.Sour ceObject = "frmSubIDO"
SubformIDO.Form .RecordSource = "SELECT IDOID, ... FROM tblIDO;"
DoEvents

I believe that simply setting the SourceObject will cause the
recordsource to requery. In cases where the recordsource will be
changing I remove the recordsource from the subform.

When two subforms are involved I use the following:

http://groups.google.com/group/comp.databases.ms-access/msg/1f5cd3c3b6ad010d?hl=en&

I haven't thought much about the best way to do that.

James A. Fortune
CDMAPoster@Fort uneJames.com