subform datasource

  • Thread starter Thread starter Michael S. Montoya
  • Start date Start date
M

Michael S. Montoya

I have a main form that has multiple instances of the same subform (each is
a different day of the week)

I named each subform Day1Subform, Day2Subform...Day7Subform. The main form
is frmWeeklyGlance

I was trying to set the recordsources with a do loop, but can't figure out
the syntax

I tried...
Me("frmWeeklyGlance.Day" & intLoop &
"Subform.Form").RecordSource=strSQL
-and-
Forms("frmWeeklyGlance.Day" & intLoop & "Subform").RecordSource =
strSQL


the following code does work, but I was hoping to make the code more
clean...
' Select Case intLoop
' Case 0
' Forms!frmWeeklyGlance.Day1Subform.Form.RecordSource =
strSQL
' Case 1
' Forms!frmWeeklyGlance.Day2Subform.Form.RecordSource =
strSQL
' End Select


help!!! :) :)
 
Michael S. Montoya said:
I have a main form that has multiple instances of the same subform (each is
a different day of the week)

I named each subform Day1Subform, Day2Subform...Day7Subform. The main form
is frmWeeklyGlance

I was trying to set the recordsources with a do loop, but can't figure out
the syntax

I tried...
Me("frmWeeklyGlance.Day" & intLoop &
"Subform.Form").RecordSource=strSQL
-and-
Forms("frmWeeklyGlance.Day" & intLoop & "Subform").RecordSource =
strSQL


the following code does work, but I was hoping to make the code more
clean...
' Select Case intLoop
' Case 0
' Forms!frmWeeklyGlance.Day1Subform.Form.RecordSource =
strSQL
' Case 1
' Forms!frmWeeklyGlance.Day2Subform.Form.RecordSource =
strSQL
' End Select
Click to expand...

You have multiple subform *controls* all pointing to a *single* form. That
single form can have exactly one RecordSource when you change it several times
in a loop all of them will end up with the last entry in the loop because they
are all the same form.

The only way that I know to have multiple instances of the same subform show
different data is to use the MasterLink and ChildLinks properties. Since those
are properties of the subform control (not of the form itself) they can each be
different.
 
Michael,

Did you name the subform controls Day1Subform, Day2Subform, etc? Note that the
name of the subform control may or may not be the same as the name of the
subform it holds.

Then try this:
Forms("frmWeeklyGlance!Day" & intLoop & "Subform").RecordSource = strSQL

where this uses the Bang instead of the Dot.
 
No, I was able to change the recordsource for each subform using the Select
Case, but future changes would be more difficult than changing a single
line.
 
Yes, I named the subform controls differently. They all refer to the same
subform named "frmWeeklyGlanceSubform". I have 7 controls, each control
named Day1Subform, Day2Subform, etc...

I tried using the "bangs" instead of "dots". I get the message:

Can't find the form frmWeeklyGlance!Day1Subform

Again, the code: Forms!frmWeeklyGlance.Day1Subform.Form.RecordSource =
strSQL
did work, but I was hoping to get it in a loop instead of naming each
subform control
 
Michael S. Montoya said:
No, I was able to change the recordsource for each subform using the Select
Case, but future changes would be more difficult than changing a single
line.
Click to expand...

I just did some testing and it appears one can apply a different RecordSource
property to multiple Subforms referencing a common form *within limits*. If the
differences involved only the WHERE clause then Access allowed it (even after a
requery which surprised me). However; if the RecordSource was significantly
different such as pulling data from completely different tables, then it did not
work. As one would move away from these two extremes I don't know where the
point would be reached where you would have problems.
 
Thank you for your research on this. That makes sense I guess. If the
subforms are so different in content, it would proably make more sense to
create different subforms.

However, in my case, it is just the Where clause that changes (where date =
....). But the problem I am still having is how to reference it with a Me()
or Form() statement so I can use it in a loop.
 
Michael S. Montoya said:
Thank you for your research on this. That makes sense I guess. If the
subforms are so different in content, it would proably make more sense to
create different subforms.
Click to expand...

Actually after looking again I see that it works even when pulling from
different tables. My first test didn't have the same field names so obviously
the subform choked on that. A subsequent test with identical field names worked
ok.

Your loop code should work if you put the dot-form outside of the parenthesis.

Instead of...
Me("frmWeeklyGlance.Day" & intLoop & "Subform.Form").RecordSource=strSQL

Try...
Me("frmWeeklyGlance.Day" & intLoop & "Subform").Form.RecordSource=strSQL
 
got it...I found it in someone else's post regarding report subforms.... I
needed to reference the control itself...

Forms("frmWeeklyGlance").Controls("Day" & intLoop &
"Subform").Form.RecordSource = strSQL

Thanks all for your help
 
Back
Top