473,378 Members | 1,370 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

VBA to set Source Object for a subreport

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
8 13170
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Etienne Charland | last post by:
I have a DataSource and a DataMember (like with standard bindings). Now, I want to write a function that gets the source object from them. So, let's say DataSource is a dataset and DataMember is...
0
by: christopher diggins | last post by:
Greetings All, I have commenced work on the Object Oriented Template Library (OOTL) which is an open-source library which seeks to provide a more object-oriented alternative to the STL and...
1
by: Stephan Gehrlein | last post by:
Hello, I am using an access 2000 project with sql srv 2000 as an administrative tool to edit table data. I select the table names from a combobox in an access form and use myform.SourceObject =...
3
by: CSDunn | last post by:
Hello, I currently have an Access 2003 ADP Report/Subreport set up in which I have 12 subreports in a single main report that are located in a group header called 'PermnumHeader' (Permnum would be...
0
by: Vince Zhang | last post by:
Hi, all I wanna make dso that implements OLEDBSimpleProvider interface to interact mshtml.dll. but meet problem in doing it. Could anybody help me? I do the following tasks: 1. use VS.NET IDE...
5
by: wxnut | last post by:
In VB 2005 I have six seperately named picture boxes (arranged in a table layout control) in which I plot different X/Y data lines. I would like to be able to right-click on a specific picture box...
5
by: sparks | last post by:
I have a form that is broken down into 24 sub forms. these are all tied to one subform, they are filtered based on other things. But I need to change the sub forms source object based on a...
5
by: =?ISO-8859-1?Q?Norbert_P=FCrringer?= | last post by:
Hello, does anyone have for me a sample how to create an object, which has access to any form of data and can be used as a data source for data bound data grids. In my case I've got a com...
3
by: Simon van Beek | last post by:
Dear reader, How to change the RecordSource for a subReport. For forms the syntaxes is:
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.