Design is as follows:
A form for the user to input criteria to be used as the where part of
the report's recordsource. This includes a multiselect list box as part
of the criteria selection.
The report is generated from a button on this form. In the button's
onclick event a where clause is built and assigned to a public variable.
Because of the multiselect listbox I use IN as part of the where
clause, for example, sqlstr = " [DocItem] IN(selected item 1, selected
item 2, etc...)".
In the OnOpen event of the report the recordsource is set using the
public variable "sqlstr" i.e. myreport.recordsource = "Select * from
reportquery where " & sqlstr.
No problem so far.
The report has a subreport in which its recordsource also needs to be
filtered by the selected criteria. I have tried using a stored
procedure as the recordsource for the subreport. I pass a string which
is a sql select statement including the where clause to the stored
procedure. The stored procedure uses the execute command to return the
rows in the select statement.
The problem is that even though the subreport returns the correct data
the parent and child link fields do not work.
I would appreciate any ideas on how to create a subreport with a
recordsource which is filtered by user input?
9  2804 
There is one way to make a subreport in an ADP using sotred procedure
arguments to link to the parent form, and it's undocument, FWICS (I read it in
the Cipman & Barron book). Make a control on the parent report that has the
same name (not including the leading @ sign) as the parameter to the stored
procedure the subreport is based on. Do not specify master/child link fields
in the subreport control properties. I think that will do what you're looking
for.
On Sun, 25 Jul 2004 14:40:29 -0500, Downstreamer <do**************@yahoo.com>
wrote: Design is as follows:
A form for the user to input criteria to be used as the where part of
the report's recordsource. This includes a multiselect list box as part
of the criteria selection.
The report is generated from a button on this form. In the button's
onclick event a where clause is built and assigned to a public variable.
Because of the multiselect listbox I use IN as part of the where
clause, for example, sqlstr = " [DocItem] IN(selected item 1, selected
item 2, etc...)".
In the OnOpen event of the report the recordsource is set using the
public variable "sqlstr" i.e. myreport.recordsource = "Select * from
reportquery where " & sqlstr.
No problem so far.
The report has a subreport in which its recordsource also needs to be
filtered by the selected criteria. I have tried using a stored
procedure as the recordsource for the subreport. I pass a string which
is a sql select statement including the where clause to the stored
procedure. The stored procedure uses the execute command to return the
rows in the select statement.
The problem is that even though the subreport returns the correct data
the parent and child link fields do not work.
I would appreciate any ideas on how to create a subreport with a
recordsource which is filtered by user input?
I think you may have complicated your task by adding a stored procedure into
the mix. Try using straight SQL... no stored procedure... as the
RecordSource for the Report embedded in the Subreport Control. I am not sure
that after modifying the RecordSource of the Report embedded in the
Subreport Control that the LinkMasterFields and LinkChildFields will,
indeed, still work... add that criteria to the SQL you are using to replace
the RecordSource if it does not.
Larry Linson
Microsoft Access MVP
"Downstreamer" <do**************@yahoo.com> wrote in message
news:H-********************@got.net... Design is as follows:
A form for the user to input criteria to be used as the where part of
the report's recordsource. This includes a multiselect list box as part
of the criteria selection.
The report is generated from a button on this form. In the button's
onclick event a where clause is built and assigned to a public variable.
Because of the multiselect listbox I use IN as part of the where
clause, for example, sqlstr = " [DocItem] IN(selected item 1, selected
item 2, etc...)".
In the OnOpen event of the report the recordsource is set using the
public variable "sqlstr" i.e. myreport.recordsource = "Select * from
reportquery where " & sqlstr.
No problem so far.
The report has a subreport in which its recordsource also needs to be
filtered by the selected criteria. I have tried using a stored
procedure as the recordsource for the subreport. I pass a string which
is a sql select statement including the where clause to the stored
procedure. The stored procedure uses the execute command to return the
rows in the select statement.
The problem is that even though the subreport returns the correct data
the parent and child link fields do not work.
I would appreciate any ideas on how to create a subreport with a
recordsource which is filtered by user input?
Larry Linson wrote: I think you may have complicated your task by adding a stored procedure into
the mix. Try using straight SQL... no stored procedure... as the
RecordSource for the Report embedded in the Subreport Control. I am not sure
that after modifying the RecordSource of the Report embedded in the
Subreport Control that the LinkMasterFields and LinkChildFields will,
indeed, still work... add that criteria to the SQL you are using to replace
the RecordSource if it does not.
Larry Linson
Microsoft Access MVP
"Downstreamer" <do**************@yahoo.com> wrote in message
news:H-********************@got.net...
Design is as follows:
A form for the user to input criteria to be used as the where part of
the report's recordsource. This includes a multiselect list box as part
of the criteria selection.
The report is generated from a button on this form. In the button's
onclick event a where clause is built and assigned to a public variable.
Because of the multiselect listbox I use IN as part of the where
clause, for example, sqlstr = " [DocItem] IN(selected item 1, selected
item 2, etc...)".
In the OnOpen event of the report the recordsource is set using the
public variable "sqlstr" i.e. myreport.recordsource = "Select * from
reportquery where " & sqlstr.
No problem so far.
The report has a subreport in which its recordsource also needs to be
filtered by the selected criteria. I have tried using a stored
procedure as the recordsource for the subreport. I pass a string which
is a sql select statement including the where clause to the stored
procedure. The stored procedure uses the execute command to return the
rows in the select statement.
The problem is that even though the subreport returns the correct data
the parent and child link fields do not work.
I would appreciate any ideas on how to create a subreport with a
recordsource which is filtered by user input?
The reason I resorted to a stored procedure was that no matter where I
tried to set the recordsource of the subreport I would get run-time
error 2191 "You can't set the Record Source property in print preview or
after printing has started." I tried in the open event of the parent
and the open event of the child with the same results. BTW I forgot to
mention that I am using Access 2003. Question: Is is possible to set
the recordsource of a subreport using code in a .adp. If so where do
you put the code so as to not get a run-time error.
Steve Jorgensen wrote: There is one way to make a subreport in an ADP using sotred procedure
arguments to link to the parent form, and it's undocument, FWICS (I read it in
the Cipman & Barron book). Make a control on the parent report that has the
same name (not including the leading @ sign) as the parameter to the stored
procedure the subreport is based on. Do not specify master/child link fields
in the subreport control properties. I think that will do what you're looking
for.
On Sun, 25 Jul 2004 14:40:29 -0500, Downstreamer <do**************@yahoo.com>
wrote:
Design is as follows:
A form for the user to input criteria to be used as the where part of
the report's recordsource. This includes a multiselect list box as part
of the criteria selection.
The report is generated from a button on this form. In the button's
onclick event a where clause is built and assigned to a public variable.
Because of the multiselect listbox I use IN as part of the where
clause, for example, sqlstr = " [DocItem] IN(selected item 1, selected
item 2, etc...)".
In the OnOpen event of the report the recordsource is set using the
public variable "sqlstr" i.e. myreport.recordsource = "Select * from
reportquery where " & sqlstr.
No problem so far.
The report has a subreport in which its recordsource also needs to be
filtered by the selected criteria. I have tried using a stored
procedure as the recordsource for the subreport. I pass a string which
is a sql select statement including the where clause to the stored
procedure. The stored procedure uses the execute command to return the
rows in the select statement.
The problem is that even though the subreport returns the correct data
the parent and child link fields do not work.
I would appreciate any ideas on how to create a subreport with a
recordsource which is filtered by user input?
Steve,
I don't quite understand. Is there a particular kind of control that I
use; just an unbound textbox with the same name as the sp argument? Do
you remember the name of the Cipman & Barron book? Thanks for your reply.
"Downstreamer" wrote The reason I resorted to a stored
procedure was that no matter where I
tried to set the recordsource of the
subreport I would get run-time
error 2191 "You can't set the Record
Source property in print preview or
after printing has started." I tried in
the open event of the parent
and the open event of the child with the
same results. BTW I forgot to
mention that I am using Access 2003.
Question: Is is possible to set the record-
source of a subreport using code in a .adp.
If so where do you put the code so as
to not get a run-time error.
The only place where you can set the RecordSource of a Report at runtime is
in its own Open event. I have not tried setting the RecordSource of a Report
embedded in a Subreport Control (strictly speaking, there is no "Subreport"
object in Access, but it's a commonly used, and sometimes misleading,
"shorthand"). The sequence of Open events may be such that what I suggested
is not possible. If so, I apologize.
What are you trying to accomplish with this approach? If we can understand
_that_, perhaps we can offer some suggestions that would be more helpful.
If you use the Subreport control as it was primarily intended, that is, to
display related records on the many side of a one-to-many or many-to-many
relationship, the LinkMasterFields and LinkChildFields usually do the trick
nicely. Generally, when someone has trouble with this area, they are trying
to use the Subreport control in some other way -- it's not necessarily wrong
to use it in another way, but it's not always possible to use it in the ways
that some users want to.
Larry Linson
Microsoft Access MVP
On Sun, 25 Jul 2004 17:31:30 -0500, Downstreamer <do**************@yahoo.com>
wrote: Steve Jorgensen wrote:
There is one way to make a subreport in an ADP using sotred procedure
arguments to link to the parent form, and it's undocument, FWICS (I read it in
the Cipman & Barron book). Make a control on the parent report that has the
same name (not including the leading @ sign) as the parameter to the stored
procedure the subreport is based on. Do not specify master/child link fields
in the subreport control properties. I think that will do what you're looking
for.
On Sun, 25 Jul 2004 14:40:29 -0500, Downstreamer <do**************@yahoo.com>
wrote:
Design is as follows:
A form for the user to input criteria to be used as the where part of
the report's recordsource. This includes a multiselect list box as part
of the criteria selection.
The report is generated from a button on this form. In the button's
onclick event a where clause is built and assigned to a public variable.
Because of the multiselect listbox I use IN as part of the where
clause, for example, sqlstr = " [DocItem] IN(selected item 1, selected
item 2, etc...)".
In the OnOpen event of the report the recordsource is set using the
public variable "sqlstr" i.e. myreport.recordsource = "Select * from
reportquery where " & sqlstr.
No problem so far.
The report has a subreport in which its recordsource also needs to be
filtered by the selected criteria. I have tried using a stored
procedure as the recordsource for the subreport. I pass a string which
is a sql select statement including the where clause to the stored
procedure. The stored procedure uses the execute command to return the
rows in the select statement.
The problem is that even though the subreport returns the correct data
the parent and child link fields do not work.
I would appreciate any ideas on how to create a subreport with a
recordsource which is filtered by user input?
Steve,
I don't quite understand. Is there a particular kind of control that I
use; just an unbound textbox with the same name as the sp argument? Do
you remember the name of the Cipman & Barron book? Thanks for your reply.
Well, it would usually be a bound textbox if the point is to have the
subreport linked to the current record on the master report via the procedure
parameter.
> The report has a subreport in which its recordsource also needs to be filtered by the selected criteria. I have tried using a stored
procedure as the recordsource for the subreport. I pass a string which
is a sql select statement including the where clause to the stored
procedure. The stored procedure uses the execute command to return the
rows in the select statement.
The problem is that even though the subreport returns the correct data
the parent and child link fields do not work.
I would appreciate any ideas on how to create a subreport with a
recordsource which is filtered by user input?
Link/Master fields work for most subreports, but in some cases -- for
example if you use the TOP function -- it just doesn't run the
subreport query properly. Here's my workaround:
Run a stored procedure for the main report recordsource and pass in
variables from the user's form as normal.
Then, for the subreport, create another stored procedure with a
variable that is the link field. Do NOT include anything in the "Link
Child Fields" and "Link Master Fields" in the subreport property page
on the main report.
Here's the wierd part: On the property page of the subreport itself,
do not put anything in "Input Parameters."
Example:
Report SP is:
ALTER PROCEDURE dbo.spStatusByDate
(@begdate smalldatetime,
@enddate smalldatetime)
AS SELECT dbo.Files.FileID, dbo.Files.File_Received,
dbo.Client.Client_Name
FROM dbo.Files INNER JOIN
dbo.Client ON dbo.Files.ClientID =
dbo.Client.ClientID
WHERE (dbo.Files.File_Received BETWEEN @begdate AND @enddate)
Input Parameters for main report are set as:
@begdate = Forms!frmMyForm.txtbegdate
@enddate = Forms!frmMyForm.txtenddate
Subreport SP is:
SELECT TOP 2 NotesID, FileID, Notes
FROM dbo.Notes
WHERE (FileID = @fileid)
ORDER BY NoteDate DESC
Input Parameters for subreport are set to be blank.
HTH
Nicole wrote: The report has a subreport in which its recordsource also needs to be
filtered by the selected criteria. I have tried using a stored
procedure as the recordsource for the subreport. I pass a string which
is a sql select statement including the where clause to the stored
procedure. The stored procedure uses the execute command to return the
rows in the select statement.
The problem is that even though the subreport returns the correct data
the parent and child link fields do not work.
I would appreciate any ideas on how to create a subreport with a
recordsource which is filtered by user input?
Link/Master fields work for most subreports, but in some cases -- for
example if you use the TOP function -- it just doesn't run the
subreport query properly. Here's my workaround:
Run a stored procedure for the main report recordsource and pass in
variables from the user's form as normal.
Then, for the subreport, create another stored procedure with a
variable that is the link field. Do NOT include anything in the "Link
Child Fields" and "Link Master Fields" in the subreport property page
on the main report.
Here's the wierd part: On the property page of the subreport itself,
do not put anything in "Input Parameters."
Example:
Report SP is:
ALTER PROCEDURE dbo.spStatusByDate
(@begdate smalldatetime,
@enddate smalldatetime)
AS SELECT dbo.Files.FileID, dbo.Files.File_Received,
dbo.Client.Client_Name
FROM dbo.Files INNER JOIN
dbo.Client ON dbo.Files.ClientID =
dbo.Client.ClientID
WHERE (dbo.Files.File_Received BETWEEN @begdate AND @enddate)
Input Parameters for main report are set as:
@begdate = Forms!frmMyForm.txtbegdate
@enddate = Forms!frmMyForm.txtenddate
Subreport SP is:
SELECT TOP 2 NotesID, FileID, Notes
FROM dbo.Notes
WHERE (FileID = @fileid)
ORDER BY NoteDate DESC
Input Parameters for subreport are set to be blank.
HTH
It works! Thank you so much Nicole. That is quite a workaround. This
was one that had me stumped for several days.
> It works! Thank you so much Nicole. That is quite a workaround. This was one that had me stumped for several days.
Glad it helped. I don't recall where I got the idea from, but it was
probably either on this newsgroup or a providential accident, or some
combination of the two! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Aaron |
last post by:
The following code works fine when previewing a Crystal report using
ASP, EXCEPT when it gets to a report using a SubReport and its
associated parameters. The whole report just comes up blank with...
|
by: dixie |
last post by:
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...
|
by: Steve Jorgensen |
last post by:
I tried to fix a problem for a client today in which report sections and even
individual text controls in some of their reports are being split across page
boundaries. Of course, I was thinking...
|
by: shaqattack1992-newsgroups |
last post by:
I know this is kind of a weird question, but is there anyway to give a
subreport control of a main report?
I posted my situation earlier about having drawings print out after a
group. I have a...
|
by: lorirobn |
last post by:
Hello,
I have a report which uses a subreport. When I run the report, I get
"Enter Parameter Value" error message for "tblGuestRoom". I click ok
and the report seems to work fine.
I...
|
by: Robert |
last post by:
Arggg... I have a report that calls up numbers from two subreports. One
deals with project labor the other expenses. on each subreport, there is a
calc fields that sums cost and price. the main...
|
by: hodgesp |
last post by:
I have a c# web application. I have created a crystal report and report
view
er.
I can get the report to display by itself but when I add a subreport I
eithe
r get a logon error or object not...
|
by: Simon |
last post by:
Dear reader,
The syntax for the VBA code to change the RowSource of a Master Report is:
Me.RowSource = "TableOrQueryName"
|
by: JLeitner08 |
last post by:
I have a report that has criteria in the query, that will ask for a starting and ending date, a location, and a type. Alone, the parameters work great for the report. Now I want to add a subreport...
|
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...
|
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...
|
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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
