473,385 Members | 1,555 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,385 software developers and data experts.

Change the recordset of an Access report on Open

When I open an Access form I can have no recordset specified, then in
the form's OnOpen event I can do something like:
Me.paramaters = "@SomeColumn = 22)"
Me.recordsource = "dbo.sproc123"

But I can't do this in a report as it will prompt me for the
parameters, even though they seem to be defined ahead of the
recordsource.

I have worked around this by opening reports to a bogus recordsource
that has the same underlying parameters and tables but is designed to
return only one record, then in the report's OnOpen event I specify the
recordsource I want.This seems like a waste of speed and bandwidth.

The reason I am doing this is because I have some forms with multiple
recordsources that share the same report and in the OnOpen of the
report I user something like:

me.recordsource = Forms!myForm.RecordSource
Am I missing something here?

Thanks,
lq

Nov 13 '05 #1
8 10716
lauren quantrell wrote:
When I open an Access form I can have no recordset specified, then in
the form's OnOpen event I can do something like:
Me.paramaters = "@SomeColumn = 22)"
Me.recordsource = "dbo.sproc123"

But I can't do this in a report as it will prompt me for the
parameters, even though they seem to be defined ahead of the
recordsource.

I have worked around this by opening reports to a bogus recordsource
that has the same underlying parameters and tables but is designed to
return only one record, then in the report's OnOpen event I specify the
recordsource I want.This seems like a waste of speed and bandwidth.

The reason I am doing this is because I have some forms with multiple
recordsources that share the same report and in the OnOpen of the
report I user something like:

me.recordsource = Forms!myForm.RecordSource
Am I missing something here?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No. You're do it correctly. The recordset is not retrieved until after
the Report's Open event finishes. Therefore, all the code you run in
the Open event occurs before any data is retrieved, and, there isn't a
waste of speed & bandwidth.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjX7joechKqOuFEgEQL8oACgxuhV4ty3j5nqzrJegp2WOf p9HfEAoOLK
X7SwcK/5IG7VYrLbqFv3+pUq
=BIAT
-----END PGP SIGNATURE-----
Nov 13 '05 #2
And dare I ask you have tried this using an Access project with
parameters passed to the stored procedure recordsource?

lq

MGFoster wrote:
lauren quantrell wrote:
When I open an Access form I can have no recordset specified, then in the form's OnOpen event I can do something like:
Me.paramaters = "@SomeColumn = 22)"
Me.recordsource = "dbo.sproc123"

But I can't do this in a report as it will prompt me for the
parameters, even though they seem to be defined ahead of the
recordsource.

I have worked around this by opening reports to a bogus recordsource that has the same underlying parameters and tables but is designed to return only one record, then in the report's OnOpen event I specify the recordsource I want.This seems like a waste of speed and bandwidth.

The reason I am doing this is because I have some forms with multiple recordsources that share the same report and in the OnOpen of the
report I user something like:

me.recordsource = Forms!myForm.RecordSource
Am I missing something here?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No. You're do it correctly. The recordset is not retrieved until

after the Report's Open event finishes. Therefore, all the code you run in
the Open event occurs before any data is retrieved, and, there isn't a waste of speed & bandwidth.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjX7joechKqOuFEgEQL8oACgxuhV4ty3j5nqzrJegp2WOf p9HfEAoOLK
X7SwcK/5IG7VYrLbqFv3+pUq
=BIAT
-----END PGP SIGNATURE-----


Nov 13 '05 #3
lauren quantrell wrote:
And dare I ask you have tried this using an Access project with
parameters passed to the stored procedure recordsource?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, I did; and got an error. Found this KB article which explained how
to fix it:

http://support.microsoft.com/default...b;en-us;300693

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjYaN4echKqOuFEgEQJ6igCgj8FBboSDICQxyIEldGmi8o WzK8gAoPFv
wztRIHYaDOKn9QLOanGHb/Ly
=vO2O
-----END PGP SIGNATURE-----
Nov 13 '05 #4
Lauren,

I have a fairly large ADP. In my experience you cannot change the report
recordsource or parameters in the code of the report. There are two ways to
accomplish a changing recordsource for a report.

1) Before running the report, your application can open it in design mode
(which can be hidden) and change the necessary setting, save it, close it
and reopen it. There are several negatives to this methodology. First,
your app MUST be and adp (not ade) and your users must have full Access
installed (Not runtime), as you are actually doing design changes, albiet
through code.

2) My preferred method is to base a report off of a stored procedure and
have the input parameters refer to a "Control" form that stores all of the
necessary info that the stored procedure needs to return a recordset to the
report. If you have very different data, you will most likely want to open
a different report based on an option on the "Control" form.

Using method two, you would set your report recordsouce in the properties
window to "dbo.SomeStoredProcedure". Then in the inputParameters you would
put something like:

@Param1 INT = Forms("ControlForm")("txtBoxWithInfo1"), @Param2 VARCHAR(20)
= Forms("ControlForm")("txtBoxWithInfo2")

HTH,
Jim
"lauren quantrell" <la*************@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
When I open an Access form I can have no recordset specified, then in
the form's OnOpen event I can do something like:
Me.paramaters = "@SomeColumn = 22)"
Me.recordsource = "dbo.sproc123"

But I can't do this in a report as it will prompt me for the
parameters, even though they seem to be defined ahead of the
recordsource.

I have worked around this by opening reports to a bogus recordsource
that has the same underlying parameters and tables but is designed to
return only one record, then in the report's OnOpen event I specify the
recordsource I want.This seems like a waste of speed and bandwidth.

The reason I am doing this is because I have some forms with multiple
recordsources that share the same report and in the OnOpen of the
report I user something like:

me.recordsource = Forms!myForm.RecordSource
Am I missing something here?

Thanks,
lq



----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Nov 13 '05 #5
Jim,
Thanks for your response.
I use your method of parameters in all of my reports. The problem is
the recordsource of the form changes among many stored procedures.
It's starting to look like I need to create many reports and open the
appropriate one based on the stored procedure of the form, rather than
the other way around of changing the stored procedure of the report...
Thanks,
lq
J. Clay wrote:
Lauren,

I have a fairly large ADP. In my experience you cannot change the report recordsource or parameters in the code of the report. There are two ways to accomplish a changing recordsource for a report.

1) Before running the report, your application can open it in design mode (which can be hidden) and change the necessary setting, save it, close it and reopen it. There are several negatives to this methodology. First, your app MUST be and adp (not ade) and your users must have full Access installed (Not runtime), as you are actually doing design changes, albiet through code.

2) My preferred method is to base a report off of a stored procedure and have the input parameters refer to a "Control" form that stores all of the necessary info that the stored procedure needs to return a recordset to the report. If you have very different data, you will most likely want to open a different report based on an option on the "Control" form.

Using method two, you would set your report recordsouce in the properties window to "dbo.SomeStoredProcedure". Then in the inputParameters you would put something like:

@Param1 INT = Forms("ControlForm")("txtBoxWithInfo1"), @Param2 VARCHAR(20) = Forms("ControlForm")("txtBoxWithInfo2")

HTH,
Jim
"lauren quantrell" <la*************@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
When I open an Access form I can have no recordset specified, then in the form's OnOpen event I can do something like:
Me.paramaters = "@SomeColumn = 22)"
Me.recordsource = "dbo.sproc123"

But I can't do this in a report as it will prompt me for the
parameters, even though they seem to be defined ahead of the
recordsource.

I have worked around this by opening reports to a bogus recordsource that has the same underlying parameters and tables but is designed to return only one record, then in the report's OnOpen event I specify the recordsource I want.This seems like a waste of speed and bandwidth.

The reason I am doing this is because I have some forms with multiple recordsources that share the same report and in the OnOpen of the
report I user something like:

me.recordsource = Forms!myForm.RecordSource
Am I missing something here?

Thanks,
lq

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet

News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption

=----

Nov 13 '05 #6
Thanks for your reply. I read the link but unfortunately that structure
in the Microsoft solution (strRecordSource = "Exec [Sales By Year]
'1/1/97','12/31/98'") isn't available to Access2K which my appliation
is.
lq

Nov 13 '05 #7
The other option is to create a "Master" stored procedure that is the record
source for the report. From within that SP, you can call the appropriate
stored procedure that you really want based on an input parameter. You
would just use a case statement within the master to call each of the "Sub"
procedures.

Jim

"lauren quantrell" <la*************@hotmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
Jim,
Thanks for your response.
I use your method of parameters in all of my reports. The problem is
the recordsource of the form changes among many stored procedures.
It's starting to look like I need to create many reports and open the
appropriate one based on the stored procedure of the form, rather than
the other way around of changing the stored procedure of the report...
Thanks,
lq
J. Clay wrote:
Lauren,

I have a fairly large ADP. In my experience you cannot change the

report
recordsource or parameters in the code of the report. There are two

ways to
accomplish a changing recordsource for a report.

1) Before running the report, your application can open it in design

mode
(which can be hidden) and change the necessary setting, save it,

close it
and reopen it. There are several negatives to this methodology.

First,
your app MUST be and adp (not ade) and your users must have full

Access
installed (Not runtime), as you are actually doing design changes,

albiet
through code.

2) My preferred method is to base a report off of a stored procedure

and
have the input parameters refer to a "Control" form that stores all

of the
necessary info that the stored procedure needs to return a recordset

to the
report. If you have very different data, you will most likely want

to open
a different report based on an option on the "Control" form.

Using method two, you would set your report recordsouce in the

properties
window to "dbo.SomeStoredProcedure". Then in the inputParameters you

would
put something like:

@Param1 INT = Forms("ControlForm")("txtBoxWithInfo1"), @Param2

VARCHAR(20)
= Forms("ControlForm")("txtBoxWithInfo2")

HTH,
Jim
"lauren quantrell" <la*************@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
When I open an Access form I can have no recordset specified, then in the form's OnOpen event I can do something like:
Me.paramaters = "@SomeColumn = 22)"
Me.recordsource = "dbo.sproc123"

But I can't do this in a report as it will prompt me for the
parameters, even though they seem to be defined ahead of the
recordsource.

I have worked around this by opening reports to a bogus recordsource that has the same underlying parameters and tables but is designed to return only one record, then in the report's OnOpen event I specify the recordsource I want.This seems like a waste of speed and bandwidth.

The reason I am doing this is because I have some forms with multiple recordsources that share the same report and in the OnOpen of the
report I user something like:

me.recordsource = Forms!myForm.RecordSource
Am I missing something here?

Thanks,
lq



----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet

News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World!

120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption

=----



----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Nov 13 '05 #8
Jim,
Yours is the perfect solution. I will do just that whern I get the
time.
lq

Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Nicola | last post by:
Hi Everyone, I am new to programming and would like to know how to open an access Report from within vb 6. I am trying to write a program to organise cross stitch threads. I have found out how...
13
by: Seth Spearman | last post by:
Hey guys, I have the following code: '****************************************************** If Not Me.NewRecord Then Dim rs As DAO.Recordset Dim strBookmark As String Set rs =...
15
by: Mark C | last post by:
All, I have exhaustingly been looking through the newsgroups in search of a way to systemically output an Access 97 report to a pdf file using the full version of Adobe Acrobat. I want the user...
3
by: Newbie | last post by:
This is my first try at running Access Report by Visual Basic I have the following code in my button press event: ' 2 - Show print preview objAccess.DoCmd.OpenReport "Invoices", 2, , ".=" & _...
4
by: dkelly925 | last post by:
I have an Access Report that I am trying to enter an "If Statement" that when a certain field exceeds a certain number of lines or a certain number of characters that field is not visible and...
1
by: ellenh | last post by:
I have read postings on the similar subject including the posting from 2003 shown below. This process works fine to display a single page snapshot report in PowerPoint. I need to display...
6
by: DeniseY | last post by:
I have an Access report that is created on the fly by the user selecting the fields to be included. The Access report comes out fine, but I want it to automatically output to an Excel spreadsheet....
17
by: kkk1979 | last post by:
I have been using access as front end and SQL as Back end. I need help in generating an access report, by using a stored procedure with input parameters as record source. I tried the following...
5
by: sphinney | last post by:
All, How do you use an ADO recordset with an Access 2002 report? The ADO recordset was created by querying an external SYBASE database. Once I get the data, how to I get my Access report to use...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
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: 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.