Re: Passing a value from a form to a Report's SQL stored procedure

Hi Duane,

Okay. Neat.

So I left that line of code in there...the debug line...and I pressed
"Ctrl + G". The VBA window opened up and down at the bottom, in the
"Immediate" pane, the information was correct. It said:

VersionID = '2.1.2317.24325'

That's exactly what it should be. Is it, then, because the data is
stored in a SQL database, rather than an Access database?

Ryan Cabanas

Duane Hookom wrote:
After running your report, press Ctrl+G to see the strSQLStatement value in
the immediate window. This should help you determine if the code is working
correctly.
--
Duane Hookom
MS Access MVP

"Ryan Cabanas" <ryan.cabanas@xxxxxxxxx> wrote in message
news:1147703811.493662.29200@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Duane,

Okay. I added the extra line of code you asked me to insert. The only
thing that happens is that when I open the report via the button, all
the information is still there (still isn't filtering), but now my
second page of my report (I only have two pages of info, so far) has no
data. Anything I'm doing wrong? What was that extra line supposed to
tell me?

Thanks for all your help, Duane. I appreciate it. Hopefully this
isn't becoming too much of a drain and too difficult. I know that I've
already gone beyond frustration and have no ideas, myself, how to get
this working. Thanks again for your help.

Ryan Cabanas

Duane Hookom wrote:
If you don't know what an ADP is then I assume you have linked to the
tables
on your SQL Server.

The code that I provided will limit the VersionID values in the report to
the value from the form control. That's why I asked about your code. Try
add
the second line below to see the results:
strSQLStatement = "VersionID = '" & strVersionID & "'"
Debug.Print strSQLStatement

Don't worry about filters. The above section of code is used to filter
the
report as it opens.
--
Duane Hookom
MS Access MVP


"Ryan Cabanas" <ryan.cabanas@xxxxxxxxx> wrote in message
news:1147559039.848530.116080@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Duane,

Is this an ADP or are you using a pass-through query?

You mean were the tables made in Access? No. I actually made them
using Enterprise Manager in Windows Server 2000. I'm just using Access
to get to them and made my forms and print my reports. I don't know
about pass-through queries. What's that? Is that what I'm doing?

Have you tried setting the various filter properties?

I don't know. I tried using some filtering settings I found in Access,
but none of them worked. Can you point me to the specific ones you're
thinking of that you want me to try? Thanks.

What is the exact code that you tried?

The code you gave me is the exact code I tried. It didn't work.

If this is an MDB, you can use code to modify the SQL property of a
pass-through query.

Do you think this is something easy enough to walk me through? I don't
know anything about this. Thanks so much, Duane.

Ryan Cabanas


Duane Hookom wrote:
Is this an ADP or are you using a pass-through query? Have you tried
setting
the various filter properties? What is the exact code that you tried?

If this is an MDB, you can use code to modify the SQL property of a
pass-through query.

--
Duane Hookom
MS Access MVP

"Ryan Cabanas" <ryan.cabanas@xxxxxxxxx> wrote in message
news:1147473734.493255.125250@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Duane,

Yes, there is a stored procedure that I created in Access and I did
base my report off of that. Here is the actual code for the store
procedure.


SELECT dbo.Version.VersionID, dbo.Version.VersionDate,
dbo.Version.ReleaseDate, dbo.VersionItem.VersionItemDescription,
dbo.VersionFolder.FolderLocation,
dbo.VersionType.VersionType, dbo.CriticalStatus.CriticalDescription
FROM dbo.Version INNER JOIN
dbo.VersionItem ON dbo.Version.VersionID =
dbo.VersionItem.VersionID INNER JOIN
dbo.VersionFolder ON
dbo.VersionItem.VersionFolderID = dbo.VersionFolder.VersionFolderID
INNER JOIN
dbo.VersionType ON
dbo.VersionItem.VersionTypeID
= dbo.VersionType.VersionTypeID INNER JOIN
dbo.CriticalStatus ON dbo.Version.CriticalID =
dbo.CriticalStatus.CriticalID


Sorry I didn't clean it up, but I just let it create it for me
because
it was quicker, yet messy.

Thanks Duane!

Ryan Cabanas



Duane Hookom wrote:
Is there a Stored Procedure in here somewhere? What is the record
source
of
your report?
--
Duane Hookom
MS Access MVP

"Ryan Cabanas" <ryan.cabanas@xxxxxxxxx> wrote in message
news:1147441435.984581.270740@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Duane,

Thank you so much for replying. I tried what you suggested, but
it
did
not work. The report gives me all of the records.

But I did forget to mention one thing, which is probably *the*
key
piece of information. My tables were created (and are stored) in
SQL
Server 2000. I need something special to connect to them for
this,
don't I?

Thanks for any more help you're able to provide. I really
appreciate
it.

Ryan Cabanas

Duane Hookom wrote:
Try this code that assumes VersionID is a text field.

Dim stDocName As String
Dim strVersionID As String
Dim strSQLStatement As String

strVersionID = Forms!VersionForm.txtVersionID
strSQLStatement = "VersionID = '" & strVersionID & "'"

stDocName = "rpt_VersionReport_SELECT"
DoCmd.OpenReport stDocName, acPreview, , strSQLStatement

--
Duane Hookom
MS Access MVP



"Ryan Cabanas" <ryan.cabanas@xxxxxxxxx> wrote in message
news:1147364014.084270.125340@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Okay. I really need help because this is just killing me.

I have created a form from a table. Then I created another
form
from a
table and embedded it in to the first form (thus, a subform).

Now. I created a stored procedure and then created a report
off
of
that. The report prints all of my records nicely, but I
cannot
get
it
to print just one record and the related sub elements. Let me
try
to
explain more clearly.

This is what I have, from a very generic viewpoint:

Table A (Main ID Numbers)
Table B (Items that belong to Main ID Numbers in Table A)

Table A
------------
1 - Tree
2 - Dog
3 - Cat
etc.

Table B
-----------
1 - Pine
2 - Elm
3 - Maple
4 - Dachsund
5 - Yorkshire Terrier
6 - Persian
7 - Tabby

Relationship of Tables
-------------------------------
1 - Tree
1 - Pine
2 - Elm
3 - Maple
2 - Dog
4 - Dachsund
5 - Yorkshire Terrier
3 - Cat
6 - Persian
7 - Tabby


Okay. So that's how my tables are layed out. Now the problem
I'm
having is that on the main form, I have an ID for the items in
Table
A.
If I want to just print a report based on "1 - Tree", for
instance,
I
can't get it to do this automatically by pulling the ID from
the
current record showing on the form.

This is what I've been trying to do in VBA from all the
examples
I've
been reading on here, but I can't get the dang thing to work
and
it's
driving me nuts. Here's the code from the builder (The above
example
of "Nature" was just an example, for clarity's sake. My code
below
is
the real code):

1st Coding Try (Didn't Work)
-----------------------------------------
Dim stDocName As String
Dim strSQLStatement As String

strSQLStatement = "SELECT dbo.Version.VersionID,
dbo.Version.VersionDate, dbo.Version.ReleaseDate,
dbo.VersionItem.VersionItemDescription,
dbo.VersionFolder.FolderLocation ,
dbo.VersionType.VersionType,
dbo.CriticalStatus.CriticalDescription FROM dbo.Version INNER
JOIN
dbo.VersionItem ON dbo.Version.VersionID =
dbo.VersionItem.VersionID
INNER JOIN dbo.VersionFolder ON
dbo.VersionItem.VersionFolderID =
dbo.VersionFolder.VersionFolderID INNER JOIN dbo.VersionType
ON
dbo.VersionItem.VersionTypeID = dbo.VersionType.VersionTypeID
INNER
JOIN dbo.CriticalStatus ON dbo.Version.CriticalID =
dbo.CriticalStatus.CriticalID WHERE (dbo.Version.VersionID =
'[Forms]![VersionForm]![txtVersionID]')"

stDocName = "rpt_VersionReport_SELECT"
DoCmd.OpenReport stDocName, acPreview, , strSQLStatement


2nd Coding Try (Didn't Work)
-----------------------------------------
Dim stDocName As String
Dim strVersionID As String
Dim strSQLStatement As String

strVersionID = Forms!VersionForm.txtVersionID
strSQLStatement = "dbo.Version.VersionID = " & strVersionID

stDocName = "rpt_VersionReport_SELECT"
DoCmd.OpenReport stDocName, acPreview, , strSQLStatement

-----------------------------------------

I'm sure I've tried a million other things, but none of them
have
worked for me. I don't know what else to try. I want the
report
to
pick up the ID from the first form and use it as criteria to
only
print
that record (and its child records) in the report. I can't
get
it
to
do this. It either prints nothing, or everything (with all
the
things
I've tried). I don't want to be prompted for the ID number,
but
I
just
want it to be retrieved from the form when I click the button
on
the
form to print the report.

Thanks for the help!!!

Ryan






.

Loading