Pass parameters from Form to Report

[Todd K.]

I am working on a Project in Access 2003, and am new to Visual Basic. I am
trying to open a report based on the Project_ID and Grant_Type fields in a
form. I know how to set up a Stored Procedure setting parameters for the two
fields, and then reference the form in the report's Input Parameters property
to pass them that way. Unfortunately this is a rather large project with
about 30 different entry forms, and rather than build 30 Stored Procedures
and 30 Reports, I would like to be able to use the same Stored Procedure and
Report but use visual basic in the forms to pass the variables. Can this be
done?

 

[ruralguy via AccessMonster.com]

Look into the OpenArgs argument of the OpenReport command. You can pass any
reasonable parameters to a report that way by concantenating your parameters
together with a separator like the semicolon ";" since the OpenArgs is just a
string and then take it apart with the Split() finction in the OpenEvent of
the report.

 

[Todd K.]

In the code to open the report I wrote:
Private Sub BtnRptSOW_Click()

DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , "[@Project_ID] =
[Forms]![FrmProjEntry_Renaissance]![Project_ID];[@Grant_Type] =
[Forms]![FrmProjEntry_Renaissance]![Grant_Type]"

End Sub

Then in the OnOpen Event for the report I wrote:
Private Sub Report_Open(Cancel As Integer)

split(OpenArgs As String,;,,vbBinaryCompare)

End Sub

.... but I'm getting a "Compile Error - Expected: List Separator or )" Do I
need some quotation marks in there somewhere? Should the "Compare As"
statement be vbTextCompare instead of vbBinaryCompare?

 

[ruralguy via AccessMonster.com]

Will your parameters always be filling in equates for [@Project_ID] and
[@Grant_Type] ? If so then just pass the answer with:

Dim strOpenArgs as String
strOpenArgs = Me.Project_ID & ";" & Me.Grant_Type

Then the Open event in the Report will look something like:

Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
'-- Report is being opened from a form passing parameters
Args = Split(Me.OpenArgs, ";")
Me.[@Project_ID] = Args(0)
Me.[@Grant_Type] = Args(1)
End If

By the way, it is not a good idea to use an ampersand "@" in a name.

http://support.microsoft.com/kb/q286335/ List of reserved words in Access
2002 and Access 2003
http://support.microsoft.com/?id=321266 List of Microsoft Jet 4.0 reserved
words
http://support.microsoft.com/?id=826763 Special characters that you must
avoid when you work with Access databases

In the code to open the report I wrote:
Private Sub BtnRptSOW_Click()

DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , "[@Project_ID] =
[Forms]![FrmProjEntry_Renaissance]![Project_ID];[@Grant_Type] =
[Forms]![FrmProjEntry_Renaissance]![Grant_Type]"

End Sub

Then in the OnOpen Event for the report I wrote:
Private Sub Report_Open(Cancel As Integer)

split(OpenArgs As String,;,,vbBinaryCompare)

End Sub

... but I'm getting a "Compile Error - Expected: List Separator or )" Do I
need some quotation marks in there somewhere? Should the "Compare As"
statement be vbTextCompare instead of vbBinaryCompare?

Look into the OpenArgs argument of the OpenReport command. You can pass any
reasonable parameters to a report that way by concantenating your parameters

[quoted text clipped - 11 lines]

 

[Todd K.]

Yes, the report will always be based on the Project_ID and Grant_Type. I got
the "@" from the book "Microsoft Office Access 2003 Inside Out" by John
Viescas (unfortunately that is the extent of my Visual Basic and SQL training
up to this point). The "@" sign is from where I set my parameters in the
stored procedure which is the source for the report:
ALTER PROCEDURE QrySP_RptSOW

(
@Project_ID int,
@Grant_Type nvarchar(50)
)
AS

SELECT Project_ID, Grant_Type, City, Project_Name, Fund_Amt, Grant_No,
Project_No, Project_Desc

FROM TblProjects

WHERE @Project_ID = TblProjects.Project_ID
AND @Grant_Type = TblProjects.Grant_Type

RETURN

Is there a better way to define them? Also, when I ran the code you listed,
I got a Run Time Error '2465' and the debugger highlighted the line
"Me.[@Project_ID] = Args(0)" but in the Locals window, both Args(0) and
Args(1) were showing the correct data.

Will your parameters always be filling in equates for [@Project_ID] and
[@Grant_Type] ? If so then just pass the answer with:

Dim strOpenArgs as String
strOpenArgs = Me.Project_ID & ";" & Me.Grant_Type

Then the Open event in the Report will look something like:

Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
'-- Report is being opened from a form passing parameters
Args = Split(Me.OpenArgs, ";")
Me.[@Project_ID] = Args(0)
Me.[@Grant_Type] = Args(1)
End If

By the way, it is not a good idea to use an ampersand "@" in a name.

http://support.microsoft.com/kb/q286335/ List of reserved words in Access
2002 and Access 2003
http://support.microsoft.com/?id=321266 List of Microsoft Jet 4.0 reserved
words
http://support.microsoft.com/?id=826763 Special characters that you must
avoid when you work with Access databases

In the code to open the report I wrote:
Private Sub BtnRptSOW_Click()

DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , "[@Project_ID] =
[Forms]![FrmProjEntry_Renaissance]![Project_ID];[@Grant_Type] =
[Forms]![FrmProjEntry_Renaissance]![Grant_Type]"

End Sub

Then in the OnOpen Event for the report I wrote:
Private Sub Report_Open(Cancel As Integer)

split(OpenArgs As String,;,,vbBinaryCompare)

End Sub

... but I'm getting a "Compile Error - Expected: List Separator or )" Do I
need some quotation marks in there somewhere? Should the "Compare As"
statement be vbTextCompare instead of vbBinaryCompare?

Look into the OpenArgs argument of the OpenReport command. You can pass any
reasonable parameters to a report that way by concantenating your parameters

[quoted text clipped - 11 lines]

Report but use visual basic in the forms to pass the variables. Can this be
done?

 

[ruralguy via AccessMonster.com]

Well John Viescas certainly knows what he's talking about and the ampersand
is *not* one of the special characters to avoid. You may want to define two
variables in the report and assign the OpenArgs to the variables. I was
assigning them to "controls" on your report as a GUESS. Oops!

Yes, the report will always be based on the Project_ID and Grant_Type. I got
the "@" from the book "Microsoft Office Access 2003 Inside Out" by John
Viescas (unfortunately that is the extent of my Visual Basic and SQL training
up to this point). The "@" sign is from where I set my parameters in the
stored procedure which is the source for the report:
ALTER PROCEDURE QrySP_RptSOW

(
@Project_ID int,
@Grant_Type nvarchar(50)
)
AS

SELECT Project_ID, Grant_Type, City, Project_Name, Fund_Amt, Grant_No,
Project_No, Project_Desc

FROM TblProjects

WHERE @Project_ID = TblProjects.Project_ID
AND @Grant_Type = TblProjects.Grant_Type

RETURN

Is there a better way to define them? Also, when I ran the code you listed,
I got a Run Time Error '2465' and the debugger highlighted the line
"Me.[@Project_ID] = Args(0)" but in the Locals window, both Args(0) and
Args(1) were showing the correct data.

Will your parameters always be filling in equates for [@Project_ID] and
[@Grant_Type] ? If so then just pass the answer with:

[quoted text clipped - 47 lines]

 

[Todd K.]

Again, please forgive me for my ignorance, but how would I do that? Would it
be in the OnOpen event? I know how to assign variables in SQL, but not in
Visual Basic.

Well John Viescas certainly knows what he's talking about and the ampersand
is *not* one of the special characters to avoid. You may want to define two
variables in the report and assign the OpenArgs to the variables. I was
assigning them to "controls" on your report as a GUESS. Oops!

Yes, the report will always be based on the Project_ID and Grant_Type. I got
the "@" from the book "Microsoft Office Access 2003 Inside Out" by John
Viescas (unfortunately that is the extent of my Visual Basic and SQL training
up to this point). The "@" sign is from where I set my parameters in the
stored procedure which is the source for the report:
ALTER PROCEDURE QrySP_RptSOW

(
@Project_ID int,
@Grant_Type nvarchar(50)
)
AS

SELECT Project_ID, Grant_Type, City, Project_Name, Fund_Amt, Grant_No,
Project_No, Project_Desc

FROM TblProjects

WHERE @Project_ID = TblProjects.Project_ID
AND @Grant_Type = TblProjects.Grant_Type

RETURN

Is there a better way to define them? Also, when I ran the code you listed,
I got a Run Time Error '2465' and the debugger highlighted the line
"Me.[@Project_ID] = Args(0)" but in the Locals window, both Args(0) and
Args(1) were showing the correct data.

Will your parameters always be filling in equates for [@Project_ID] and
[@Grant_Type] ? If so then just pass the answer with:

[quoted text clipped - 47 lines]

Report but use visual basic in the forms to pass the variables. Can this be
done?

 

[ruralguy via AccessMonster.com]

You need to define the variables as global variables for the report. They
are defined outside of any procedures so they can be used anywhere in any
procedure.

Option Compare Database
Option Explicit
Dim MyProjectID As Integer, MyGrantType As String

Then the Open event in the Report will look something like:

Private Sub Report_Open(Cancel As Integer)
Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
'-- Report is being opened from a form passing parameters
Args = Split(Me.OpenArgs, ";")
MyProjectID = CInt(Args(0))
MyGrantType = Args(1)
End If

 

[Todd K.]

I tried the global variables, but when I ran the report it debugged at the
variable line and said "variables not declared". So I tried declaring them
in that procedure and when I ran the report, it asked me for the values of
MyProjectID and MyGrantType. Here is what I typed in the report module:
Private Sub Report_Open(Cancel As Integer)

Dim Args As Variant
Dim MyProjectID As Integer
Dim MyGrantType As String

If Not IsNull(Me.OpenArgs) Then
'-- Report is being opened from a form passing parameters
Args = Split(Me.OpenArgs, ";")
MyProjectID = CInt(Args(0))
MyGrantType = Args(1)

End If

End Sub

I also tried to "help" pass the variables by entering into the Input
Parameters property of the report "@Project_ID int = MyProjectID,
@Grant_Type varchar(50) = MyGrantType", which is how I passed the variables
before.

 

[ruralguy via AccessMonster.com]

Hi Todd,
If your "help" is contained in the Open event of the report then that should
work. I'll be honest, I'm not familiar with the ALTER PROCEDURE stuff you
got from John. Somehow you need to have the query refer to your variables
rather than a field in a table. Declaring the variables outside of a
procedure should have made then Public but maybe it needs the Public keyword.

Option Compare Database
Option Explicit

Public MyProjectID As Integer
Public MyGrantType As String

You should then be able to refer to the variables as properties of the report:

WHERE @Project_ID = Reports.Project_ID
AND @Grant_Type = Reports.Grant_Type

I have to leave for a few hours. I'll check in when I get back. Good luck.
Maybe someone else will drop by and offer assistance whil I'm gone if you
still have problems.

I tried the global variables, but when I ran the report it debugged at the
variable line and said "variables not declared". So I tried declaring them
in that procedure and when I ran the report, it asked me for the values of
MyProjectID and MyGrantType. Here is what I typed in the report module:
Private Sub Report_Open(Cancel As Integer)

Dim Args As Variant
Dim MyProjectID As Integer
Dim MyGrantType As String

If Not IsNull(Me.OpenArgs) Then
'-- Report is being opened from a form passing parameters
Args = Split(Me.OpenArgs, ";")
MyProjectID = CInt(Args(0))
MyGrantType = Args(1)

End If

End Sub

I also tried to "help" pass the variables by entering into the Input
Parameters property of the report "@Project_ID int = MyProjectID,
@Grant_Type varchar(50) = MyGrantType", which is how I passed the variables
before.

You need to define the variables as global variables for the report. They
are defined outside of any procedures so they can be used anywhere in any

[quoted text clipped - 15 lines]

MyGrantType = Args(1)
End If

 

[ruralguy via AccessMonster.com]

Did you have any success Todd?

Hi Todd,
If your "help" is contained in the Open event of the report then that should
work. I'll be honest, I'm not familiar with the ALTER PROCEDURE stuff you
got from John. Somehow you need to have the query refer to your variables
rather than a field in a table. Declaring the variables outside of a
procedure should have made then Public but maybe it needs the Public keyword.

Option Compare Database
Option Explicit

Public MyProjectID As Integer
Public MyGrantType As String

You should then be able to refer to the variables as properties of the report:

WHERE @Project_ID = Reports.Project_ID
AND @Grant_Type = Reports.Grant_Type

I have to leave for a few hours. I'll check in when I get back. Good luck.
Maybe someone else will drop by and offer assistance whil I'm gone if you
still have problems.

I tried the global variables, but when I ran the report it debugged at the
variable line and said "variables not declared". So I tried declaring them

[quoted text clipped - 26 lines]

 

[Todd K.]

No, but I'm thinking about a different approach (something simpler). Can you
run SQL language from a module? I know that the form is sending the
information, because I see the correct numbers in the Locals window, but
between the report having a stored procedure as its source and declaring
variables in the opening statement, something is getting lost. I thought I
could do it all in the Form Module, maybe something like:
Private Sub BtnRptSOW_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblProjects WHERE
Project_ID = " & Me.Project_ID & " AND Grant_Type = " & Me.Grant_Type)
DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , rst

End Sub

But the recordset "rst" is coming back empty.

Did you have any success Todd?

Hi Todd,
If your "help" is contained in the Open event of the report then that should
work. I'll be honest, I'm not familiar with the ALTER PROCEDURE stuff you
got from John. Somehow you need to have the query refer to your variables
rather than a field in a table. Declaring the variables outside of a
procedure should have made then Public but maybe it needs the Public keyword.

Option Compare Database
Option Explicit

Public MyProjectID As Integer
Public MyGrantType As String

You should then be able to refer to the variables as properties of the report:

WHERE @Project_ID = Reports.Project_ID
AND @Grant_Type = Reports.Grant_Type

I have to leave for a few hours. I'll check in when I get back. Good luck.
Maybe someone else will drop by and offer assistance whil I'm gone if you
still have problems.

I tried the global variables, but when I ran the report it debugged at the
variable line and said "variables not declared". So I tried declaring them

[quoted text clipped - 26 lines]

MyGrantType = Args(1)
End If

 

[ruralguy via AccessMonster.com]

Why can't you do it in the WhereCondition of the OpenReport command?

Dim strCondition As String
strCondition = "[Project_ID] = " & Me.Project_ID & " AND [Grant_Type] = " &
Chr(34) & Me.Grant_Type & Chr(34)
[Grant_Type] is a String Data type and needs to be surrounded by quotes Chr
(34)

Then:
DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , strCondition

This adds a FILTER to the report so the RecordSource of the Report needs to
be generic and let the Filter limit the returned dataset.

No, but I'm thinking about a different approach (something simpler). Can you
run SQL language from a module? I know that the form is sending the
information, because I see the correct numbers in the Locals window, but
between the report having a stored procedure as its source and declaring
variables in the opening statement, something is getting lost. I thought I
could do it all in the Form Module, maybe something like:
Private Sub BtnRptSOW_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblProjects WHERE
Project_ID = " & Me.Project_ID & " AND Grant_Type = " & Me.Grant_Type)
DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , rst

End Sub

But the recordset "rst" is coming back empty.

Did you have any success Todd?

[quoted text clipped - 25 lines]

 

[Todd K.]

Okay, I cleared the OnOpen code in the report module and tried using the
table tblProjects as the source of the report instead of the stored procedure
that declared variables, entering your code below in the form module. When I
ran the report, I got an error saying Invalid Column Name 'Renaissance'
(which is the correct value for Grant_Type).

When I debugged, the values in the Locals window were correct:
strCondition "[Project_ID] = 368 AND [Grant_Type] = "Renaissance""

Why can't you do it in the WhereCondition of the OpenReport command?

Dim strCondition As String
strCondition = "[Project_ID] = " & Me.Project_ID & " AND [Grant_Type] = " &
Chr(34) & Me.Grant_Type & Chr(34)
[Grant_Type] is a String Data type and needs to be surrounded by quotes Chr
(34)

Then:
DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , strCondition

This adds a FILTER to the report so the RecordSource of the Report needs to
be generic and let the Filter limit the returned dataset.

No, but I'm thinking about a different approach (something simpler). Can you
run SQL language from a module? I know that the form is sending the
information, because I see the correct numbers in the Locals window, but
between the report having a stored procedure as its source and declaring
variables in the opening statement, something is getting lost. I thought I
could do it all in the Form Module, maybe something like:
Private Sub BtnRptSOW_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblProjects WHERE
Project_ID = " & Me.Project_ID & " AND Grant_Type = " & Me.Grant_Type)
DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , rst

End Sub

But the recordset "rst" is coming back empty.

Did you have any success Todd?

[quoted text clipped - 25 lines]

MyGrantType = Args(1)
End If

 

[Todd K.]

SUCCESS!!!!

The only problem with your code below is that the Grant_Type needed to be
surrounded in single quotes instead of double quotes, so I just altered it to:
strCondition = "[Project_ID] = " & Me.Project_ID & " AND [Grant_Type] = '" &
Me.Grant_Type & "'"
AND IT WORKED.

This rocks, you have truly been an answer to prayer. Thanks SO much.

Why can't you do it in the WhereCondition of the OpenReport command?

Dim strCondition As String
strCondition = "[Project_ID] = " & Me.Project_ID & " AND [Grant_Type] = " &
Chr(34) & Me.Grant_Type & Chr(34)
[Grant_Type] is a String Data type and needs to be surrounded by quotes Chr
(34)

Then:
DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , strCondition

This adds a FILTER to the report so the RecordSource of the Report needs to
be generic and let the Filter limit the returned dataset.

No, but I'm thinking about a different approach (something simpler). Can you
run SQL language from a module? I know that the form is sending the
information, because I see the correct numbers in the Locals window, but
between the report having a stored procedure as its source and declaring
variables in the opening statement, something is getting lost. I thought I
could do it all in the Form Module, maybe something like:
Private Sub BtnRptSOW_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblProjects WHERE
Project_ID = " & Me.Project_ID & " AND Grant_Type = " & Me.Grant_Type)
DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , rst

End Sub

But the recordset "rst" is coming back empty.

Did you have any success Todd?

[quoted text clipped - 25 lines]

MyGrantType = Args(1)
End If

 

[ruralguy via AccessMonster.com]

Outstanding Todd! Thanks for posting back with your success.

SUCCESS!!!!

The only problem with your code below is that the Grant_Type needed to be
surrounded in single quotes instead of double quotes, so I just altered it to:
strCondition = "[Project_ID] = " & Me.Project_ID & " AND [Grant_Type] = '" &
Me.Grant_Type & "'"
AND IT WORKED.

This rocks, you have truly been an answer to prayer. Thanks SO much.

Why can't you do it in the WhereCondition of the OpenReport command?

[quoted text clipped - 32 lines]