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

R

Ryan Cabanas

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
 
D

Duane Hookom

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 said:
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
Click to expand...
 
R

Ryan Cabanas

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 said:
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 said:
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
Click to expand...
Click to expand...
 
D

Duane Hookom

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

Ryan Cabanas said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Ryan Cabanas

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 said:
Is there a Stored Procedure in here somewhere? What is the record source of
your report?
--
Duane Hookom
MS Access MVP

Ryan Cabanas said:
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 said:
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



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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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 said:
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 said:
Is there a Stored Procedure in here somewhere? What is the record source
of
your report?
--
Duane Hookom
MS Access MVP

Ryan Cabanas said:
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



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
Click to expand...
Click to expand...
Click to expand...
 
R

Ryan Cabanas

Hi Duane,
Is this an ADP or are you using a pass-through query?
Click to expand...

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?
Click to expand...

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?
Click to expand...

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.
Click to expand...

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 said:
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 said:
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 said:
Is there a Stored Procedure in here somewhere? What is the record source
of
your report?
--
Duane Hookom
MS Access MVP

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



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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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 said:
Hi Duane,
Is this an ADP or are you using a pass-through query?
Click to expand...

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?
Click to expand...

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?
Click to expand...

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.
Click to expand...

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 said:
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 said:
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

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



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
Click to expand...
Click to expand...
Click to expand...
 
R

Ryan Cabanas

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 said:
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 said:
Hi Duane,
Is this an ADP or are you using a pass-through query?
Click to expand...

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?
Click to expand...

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?
Click to expand...

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.
Click to expand...

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 said:
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

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

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



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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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 said:
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 said:
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 said:
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

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

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



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
Click to expand...
Click to expand...
Click to expand...
 
R

Ryan Cabanas

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 said:
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 said:
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 said:
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


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

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

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



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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Where your data is stored shouldn't be an issue. Place a text box in your
report header section with a control source of:
=[Filter]
You should see the same value as the debug.

I expect the code is doing exactly what you are telling it to do but
probably not what you want it to do.
--
Duane Hookom
MS Access MVP

Ryan Cabanas said:
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 said:
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 said:
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


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

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

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



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help creating a search form in Access XP 0
Can I save a sql statement generated by a search form? 1
Crosstab Report's subreport 1
Run a SQL Stored Procedure from VBA 0
Parameter for Report from Form 2
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Individual Report to each Staff member 0
Seeing report filter in report open procedure 6

Top