By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,488 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

VBA to set Source Object for a subreport

P: n/a
I have a report with a subreport. The source object for this subreport
varies according to the value of a field in a table. I am trying to
programmatically set the object source for the subreport as part of the
detail format section of the report. I have been playing around with things
like:

Me.SubReport.SourceObject = Report.sub5O5L

but nothing seems to work. Can I achieve what I want to do and if so, can
anyone give me the syntax I need to reset the source object of a subreport.

dixie
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Just set the SourceObject property to the name of your subreport:

Me.SubReport.SourceObject="sub505L"

Don't forget to reset your LinkMasterFields and LinkChildFields properties
after you change the SourceObject property.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:zA***************@nnrp1.ozemail.com.au...
I have a report with a subreport. The source object for this subreport
varies according to the value of a field in a table. I am trying to
programmatically set the object source for the subreport as part of the
detail format section of the report. I have been playing around with things like:

Me.SubReport.SourceObject = Report.sub5O5L

but nothing seems to work. Can I achieve what I want to do and if so, can
anyone give me the syntax I need to reset the source object of a subreport.
dixie

Nov 12 '05 #2

P: n/a
OK, I was so close - didn't put the quote marks in. However, I've just
tried this and I get error 2199 - You cant set the source object property
after printing has started. The code was in the Detail_Format section. I
guess I can try to do it in the open event. Whoops can't go there either.
Any ideas? The code I have now is:

If [PrintType] = "05W04" Then
Me.SubOutcomes.SourceObject = "sub5O5L"
Me.SubOutcomes.LinkMasterFields = "EntryNumber"
Me.SubOutcomes.LinkChildFields = "EntryNumber"
End If

But, I can't find the event to run it in - I have tried most possibilities.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:Zk*******************@newsread3.news.atl.eart hlink.net...
Just set the SourceObject property to the name of your subreport:

Me.SubReport.SourceObject="sub505L"

Don't forget to reset your LinkMasterFields and LinkChildFields properties
after you change the SourceObject property.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:zA***************@nnrp1.ozemail.com.au...
I have a report with a subreport. The source object for this subreport
varies according to the value of a field in a table. I am trying to
programmatically set the object source for the subreport as part of the
detail format section of the report. I have been playing around with

things
like:

Me.SubReport.SourceObject = Report.sub5O5L

but nothing seems to work. Can I achieve what I want to do and if so, can anyone give me the syntax I need to reset the source object of a

subreport.

dixie


Nov 12 '05 #3

P: n/a
Sorry -
I had overlooked the fact that you were using a report, not a form.

It looks as if you want to be able to show different subreports for
different records.
One work-around for this problem would be to put both subreports on the
report in design view (on top of each other). Be sure you set their CanGrow
and CanShrink properties to True.
Then in your Detail_Format event procedure, you can make the one you want
visible and the other invisible.

This may run more slowly, but it should give you the result you want.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:1C****************@nnrp1.ozemail.com.au...
OK, I was so close - didn't put the quote marks in. However, I've just
tried this and I get error 2199 - You cant set the source object property
after printing has started. The code was in the Detail_Format section. I
guess I can try to do it in the open event. Whoops can't go there either.
Any ideas? The code I have now is:

If [PrintType] = "05W04" Then
Me.SubOutcomes.SourceObject = "sub5O5L"
Me.SubOutcomes.LinkMasterFields = "EntryNumber"
Me.SubOutcomes.LinkChildFields = "EntryNumber"
End If

But, I can't find the event to run it in - I have tried most possibilities.
dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:Zk*******************@newsread3.news.atl.eart hlink.net...
Just set the SourceObject property to the name of your subreport:

Me.SubReport.SourceObject="sub505L"

Don't forget to reset your LinkMasterFields and LinkChildFields properties
after you change the SourceObject property.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:zA***************@nnrp1.ozemail.com.au...
I have a report with a subreport. The source object for this subreport varies according to the value of a field in a table. I am trying to
programmatically set the object source for the subreport as part of the detail format section of the report. I have been playing around with

things
like:

Me.SubReport.SourceObject = Report.sub5O5L

but nothing seems to work. Can I achieve what I want to do and if so,

can anyone give me the syntax I need to reset the source object of a

subreport.

dixie



Nov 12 '05 #4

P: n/a
Actually, that is how I currently do it, but there are 11 subreports, and
even though only one is visible at a time for each record, the other 10
populate with data and it makes the printing of the reports a very memory
intensive and slow procedure. I was hoping to use this method to just swap
each subreport in and out of the main report to speed things up. Oh well,
it seemed like a good idea at the time. Thanks anyway Turtle.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:5o*****************@newsread1.news.atl.earthl ink.net...
Sorry -
I had overlooked the fact that you were using a report, not a form.

It looks as if you want to be able to show different subreports for
different records.
One work-around for this problem would be to put both subreports on the
report in design view (on top of each other). Be sure you set their CanGrow and CanShrink properties to True.
Then in your Detail_Format event procedure, you can make the one you want
visible and the other invisible.

This may run more slowly, but it should give you the result you want.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:1C****************@nnrp1.ozemail.com.au...
OK, I was so close - didn't put the quote marks in. However, I've just
tried this and I get error 2199 - You cant set the source object property
after printing has started. The code was in the Detail_Format section. I guess I can try to do it in the open event. Whoops can't go there either. Any ideas? The code I have now is:

If [PrintType] = "05W04" Then
Me.SubOutcomes.SourceObject = "sub5O5L"
Me.SubOutcomes.LinkMasterFields = "EntryNumber"
Me.SubOutcomes.LinkChildFields = "EntryNumber"
End If

But, I can't find the event to run it in - I have tried most

possibilities.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:Zk*******************@newsread3.news.atl.eart hlink.net...
Just set the SourceObject property to the name of your subreport:

Me.SubReport.SourceObject="sub505L"

Don't forget to reset your LinkMasterFields and LinkChildFields properties after you change the SourceObject property.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:zA***************@nnrp1.ozemail.com.au...
> I have a report with a subreport. The source object for this subreport > varies according to the value of a field in a table. I am trying to
> programmatically set the object source for the subreport as part of the > detail format section of the report. I have been playing around with things
> like:
>
> Me.SubReport.SourceObject = Report.sub5O5L
>
> but nothing seems to work. Can I achieve what I want to do and if

so, can
> anyone give me the syntax I need to reset the source object of a
subreport.
>
> dixie
>
>



Nov 12 '05 #5

P: n/a
What about this idea. Can I copy a subreport to a new name (which would be
a generic one in the report) for a record, then delete it and copy another
subreport to the same name for the next report, etc.? Just asking for an
opinion.

I have played around with this for a while, but the problem was that I can
rename the report to the generic subreport name, but then, I can't delete
the renamed report for the next renaming because it is being held open by
the report on which it is a subreport.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:5o*****************@newsread1.news.atl.earthl ink.net...
Sorry -
I had overlooked the fact that you were using a report, not a form.

It looks as if you want to be able to show different subreports for
different records.
One work-around for this problem would be to put both subreports on the
report in design view (on top of each other). Be sure you set their CanGrow and CanShrink properties to True.
Then in your Detail_Format event procedure, you can make the one you want
visible and the other invisible.

This may run more slowly, but it should give you the result you want.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:1C****************@nnrp1.ozemail.com.au...
OK, I was so close - didn't put the quote marks in. However, I've just
tried this and I get error 2199 - You cant set the source object property
after printing has started. The code was in the Detail_Format section. I guess I can try to do it in the open event. Whoops can't go there either. Any ideas? The code I have now is:

If [PrintType] = "05W04" Then
Me.SubOutcomes.SourceObject = "sub5O5L"
Me.SubOutcomes.LinkMasterFields = "EntryNumber"
Me.SubOutcomes.LinkChildFields = "EntryNumber"
End If

But, I can't find the event to run it in - I have tried most

possibilities.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:Zk*******************@newsread3.news.atl.eart hlink.net...
Just set the SourceObject property to the name of your subreport:

Me.SubReport.SourceObject="sub505L"

Don't forget to reset your LinkMasterFields and LinkChildFields properties after you change the SourceObject property.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:zA***************@nnrp1.ozemail.com.au...
> I have a report with a subreport. The source object for this subreport > varies according to the value of a field in a table. I am trying to
> programmatically set the object source for the subreport as part of the > detail format section of the report. I have been playing around with things
> like:
>
> Me.SubReport.SourceObject = Report.sub5O5L
>
> but nothing seems to work. Can I achieve what I want to do and if

so, can
> anyone give me the syntax I need to reset the source object of a
subreport.
>
> dixie
>
>




Nov 12 '05 #6

P: n/a
I would tend to agree that this won't work well.

How different are the subreports?

- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:hP*****************@nnrp1.ozemail.com.au...
What about this idea. Can I copy a subreport to a new name (which would be a generic one in the report) for a record, then delete it and copy another
subreport to the same name for the next report, etc.? Just asking for an
opinion.

I have played around with this for a while, but the problem was that I can
rename the report to the generic subreport name, but then, I can't delete
the renamed report for the next renaming because it is being held open by
the report on which it is a subreport.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:5o*****************@newsread1.news.atl.earthl ink.net...
Sorry -
I had overlooked the fact that you were using a report, not a form.

It looks as if you want to be able to show different subreports for
different records.
One work-around for this problem would be to put both subreports on the
report in design view (on top of each other). Be sure you set their CanGrow
and CanShrink properties to True.
Then in your Detail_Format event procedure, you can make the one you want
visible and the other invisible.

This may run more slowly, but it should give you the result you want.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:1C****************@nnrp1.ozemail.com.au...
OK, I was so close - didn't put the quote marks in. However, I've just tried this and I get error 2199 - You cant set the source object property after printing has started. The code was in the Detail_Format section. I
guess I can try to do it in the open event. Whoops can't go there either. Any ideas? The code I have now is:

If [PrintType] = "05W04" Then
Me.SubOutcomes.SourceObject = "sub5O5L"
Me.SubOutcomes.LinkMasterFields = "EntryNumber"
Me.SubOutcomes.LinkChildFields = "EntryNumber"
End If

But, I can't find the event to run it in - I have tried most

possibilities.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:Zk*******************@newsread3.news.atl.eart hlink.net...
> Just set the SourceObject property to the name of your subreport:
>
> Me.SubReport.SourceObject="sub505L"
>
> Don't forget to reset your LinkMasterFields and LinkChildFields

properties
> after you change the SourceObject property.
>
> HTH
> - Turtle
>
> "dixie" <di****@dogmail.com> wrote in message
> news:zA***************@nnrp1.ozemail.com.au...
> > I have a report with a subreport. The source object for this

subreport
> > varies according to the value of a field in a table. I am trying
to > > programmatically set the object source for the subreport as part

of the
> > detail format section of the report. I have been playing around

with > things
> > like:
> >
> > Me.SubReport.SourceObject = Report.sub5O5L
> >
> > but nothing seems to work. Can I achieve what I want to do and if so, can
> > anyone give me the syntax I need to reset the source object of a
> subreport.
> >
> > dixie
> >
> >
>
>



Nov 12 '05 #7

P: n/a
I know I didn't word that last message very well, but I think you get the
gist of it. The idea was to have only one generically named subreport and
then to delete it and copy another one of the subreports to the same generic
name on a record by record needs basis. The idea was pretty simple, but the
problem was in deleting the last used generic report as it was being held
open by the main report. I tried to close it in code, but this didn't work.
It looks like the idea of toggling the visible property of the various
subreports will need to stay and it will just need to be sent to the printer
by a machine with lots of RAM. The speed is fine here (probably because of
the gigabyte of RAM). The subreports themselves are not a lot different to
each other - similar data, but the difference is actually in the lines that
surround each of the pieces of data - they are structured like a table built
from lines and each of the subreports has a different number of lines in
that table. This is purely for appearance, but that is an important part of
it.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:E8**************@newsread3.news.atl.earthlink .net...
I would tend to agree that this won't work well.

How different are the subreports?

- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:hP*****************@nnrp1.ozemail.com.au...
What about this idea. Can I copy a subreport to a new name (which would be
a generic one in the report) for a record, then delete it and copy another
subreport to the same name for the next report, etc.? Just asking for an opinion.

I have played around with this for a while, but the problem was that I can rename the report to the generic subreport name, but then, I can't delete the renamed report for the next renaming because it is being held open by the report on which it is a subreport.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:5o*****************@newsread1.news.atl.earthl ink.net...
Sorry -
I had overlooked the fact that you were using a report, not a form.
It looks as if you want to be able to show different subreports for
different records.
One work-around for this problem would be to put both subreports on the report in design view (on top of each other). Be sure you set their

CanGrow
and CanShrink properties to True.
Then in your Detail_Format event procedure, you can make the one you want visible and the other invisible.

This may run more slowly, but it should give you the result you want.

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:1C****************@nnrp1.ozemail.com.au...
> OK, I was so close - didn't put the quote marks in. However, I've just > tried this and I get error 2199 - You cant set the source object

property
> after printing has started. The code was in the Detail_Format section.
I
> guess I can try to do it in the open event. Whoops can't go there

either.
> Any ideas? The code I have now is:
>
> If [PrintType] = "05W04" Then
> Me.SubOutcomes.SourceObject = "sub5O5L"
> Me.SubOutcomes.LinkMasterFields = "EntryNumber"
> Me.SubOutcomes.LinkChildFields = "EntryNumber"
> End If
>
> But, I can't find the event to run it in - I have tried most
possibilities.
>
> dixie
>
> "MacDermott" <ma********@nospam.com> wrote in message
> news:Zk*******************@newsread3.news.atl.eart hlink.net...
> > Just set the SourceObject property to the name of your subreport:
> >
> > Me.SubReport.SourceObject="sub505L"
> >
> > Don't forget to reset your LinkMasterFields and LinkChildFields
properties
> > after you change the SourceObject property.
> >
> > HTH
> > - Turtle
> >
> > "dixie" <di****@dogmail.com> wrote in message
> > news:zA***************@nnrp1.ozemail.com.au...
> > > I have a report with a subreport. The source object for this
subreport
> > > varies according to the value of a field in a table. I am

trying to > > > programmatically set the object source for the subreport as part of the
> > > detail format section of the report. I have been playing around

with
> > things
> > > like:
> > >
> > > Me.SubReport.SourceObject = Report.sub5O5L
> > >
> > > but nothing seems to work. Can I achieve what I want to do and

if so,
> can
> > > anyone give me the syntax I need to reset the source object of a
> > subreport.
> > >
> > > dixie
> > >
> > >
> >
> >
>
>



Nov 12 '05 #8

P: n/a
It sounds to me as if there might be a good chance you could use a single
subreport, changing out some of these "lines that surround the data".
I'm sure you realize you haven't given me enough information to make any
more specific suggestions...

HTH
- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:zs*****************@nnrp1.ozemail.com.au...
I know I didn't word that last message very well, but I think you get the
gist of it. The idea was to have only one generically named subreport and
then to delete it and copy another one of the subreports to the same generic name on a record by record needs basis. The idea was pretty simple, but the problem was in deleting the last used generic report as it was being held
open by the main report. I tried to close it in code, but this didn't work. It looks like the idea of toggling the visible property of the various
subreports will need to stay and it will just need to be sent to the printer by a machine with lots of RAM. The speed is fine here (probably because of the gigabyte of RAM). The subreports themselves are not a lot different to each other - similar data, but the difference is actually in the lines that surround each of the pieces of data - they are structured like a table built from lines and each of the subreports has a different number of lines in
that table. This is purely for appearance, but that is an important part of it.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:E8**************@newsread3.news.atl.earthlink .net...
I would tend to agree that this won't work well.

How different are the subreports?

- Turtle

"dixie" <di****@dogmail.com> wrote in message
news:hP*****************@nnrp1.ozemail.com.au...
What about this idea. Can I copy a subreport to a new name (which would
be
a generic one in the report) for a record, then delete it and copy another subreport to the same name for the next report, etc.? Just asking for an opinion.

I have played around with this for a while, but the problem was that I can rename the report to the generic subreport name, but then, I can't delete the renamed report for the next renaming because it is being held open by the report on which it is a subreport.

dixie

"MacDermott" <ma********@nospam.com> wrote in message
news:5o*****************@newsread1.news.atl.earthl ink.net...
> Sorry -
> I had overlooked the fact that you were using a report, not a form. >
> It looks as if you want to be able to show different subreports for
> different records.
> One work-around for this problem would be to put both subreports on the > report in design view (on top of each other). Be sure you set their
CanGrow
> and CanShrink properties to True.
> Then in your Detail_Format event procedure, you can make the one you want
> visible and the other invisible.
>
> This may run more slowly, but it should give you the result you
want. >
> HTH
> - Turtle
>
> "dixie" <di****@dogmail.com> wrote in message
> news:1C****************@nnrp1.ozemail.com.au...
> > OK, I was so close - didn't put the quote marks in. However, I've

just
> > tried this and I get error 2199 - You cant set the source object
property
> > after printing has started. The code was in the Detail_Format

section.
I
> > guess I can try to do it in the open event. Whoops can't go there
either.
> > Any ideas? The code I have now is:
> >
> > If [PrintType] = "05W04" Then
> > Me.SubOutcomes.SourceObject = "sub5O5L"
> > Me.SubOutcomes.LinkMasterFields = "EntryNumber"
> > Me.SubOutcomes.LinkChildFields = "EntryNumber"
> > End If
> >
> > But, I can't find the event to run it in - I have tried most
> possibilities.
> >
> > dixie
> >
> > "MacDermott" <ma********@nospam.com> wrote in message
> > news:Zk*******************@newsread3.news.atl.eart hlink.net...
> > > Just set the SourceObject property to the name of your subreport: > > >
> > > Me.SubReport.SourceObject="sub505L"
> > >
> > > Don't forget to reset your LinkMasterFields and LinkChildFields
> properties
> > > after you change the SourceObject property.
> > >
> > > HTH
> > > - Turtle
> > >
> > > "dixie" <di****@dogmail.com> wrote in message
> > > news:zA***************@nnrp1.ozemail.com.au...
> > > > I have a report with a subreport. The source object for this
> subreport
> > > > varies according to the value of a field in a table. I am trying
to
> > > > programmatically set the object source for the subreport as part of
> the
> > > > detail format section of the report. I have been playing

around with
> > > things
> > > > like:
> > > >
> > > > Me.SubReport.SourceObject = Report.sub5O5L
> > > >
> > > > but nothing seems to work. Can I achieve what I want to do and if so,
> > can
> > > > anyone give me the syntax I need to reset the source object of

a > > > subreport.
> > > >
> > > > dixie
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.