|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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?
|
|
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. wrote:
[Quoted Text] >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?
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1
|
|
In the code to open the report I wrote:
Private Sub BtnRptSOW_Click()
DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , "[[ at ]Project_ID] =
[Forms]![FrmProjEntry_Renaissance]![Project_ID];[[ at ]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" wrote:
[Quoted Text] > 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. wrote: > >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? > > -- > HTH - RuralGuy (RG for short) acXP WinXP Pro > Please post back to this forum so all may benefit. > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1> > |
|
Will your parameters always be filling in equates for [[ at ]Project_ID] and
[[ at ]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.[[ at ]Project_ID] = Args(0)
Me.[[ at ]Grant_Type] = Args(1)
End If
By the way, it is not a good idea to use an ampersand "[ at ]" 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
Todd K. wrote:
[Quoted Text] >In the code to open the report I wrote:
>Private Sub BtnRptSOW_Click()
>
> DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , "[[ at ]Project_ID] =
>[Forms]![FrmProjEntry_Renaissance]![Project_ID];[[ at ]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?
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.
Message posted via http://www.accessmonster.com
|
|
Yes, the report will always be based on the Project_ID and Grant_Type. I got
the "[ at ]" 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 "[ at ]" sign is from where I set my parameters in the
stored procedure which is the source for the report:
ALTER PROCEDURE QrySP_RptSOW
(
[ at ]Project_ID int,
[ at ]Grant_Type nvarchar(50)
)
AS
SELECT Project_ID, Grant_Type, City, Project_Name, Fund_Amt, Grant_No,
Project_No, Project_Desc
FROM TblProjects
WHERE [ at ]Project_ID = TblProjects.Project_ID
AND [ at ]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.[[ at ]Project_ID] = Args(0)" but in the Locals window, both Args(0) and
Args(1) were showing the correct data.
"ruralguy via AccessMonster.com" wrote:
[Quoted Text] > Will your parameters always be filling in equates for [[ at ]Project_ID] and > [[ at ]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.[[ at ]Project_ID] = Args(0) > Me.[[ at ]Grant_Type] = Args(1) > End If > > By the way, it is not a good idea to use an ampersand "[ at ]" 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 > > > Todd K. wrote: > >In the code to open the report I wrote: > >Private Sub BtnRptSOW_Click() > > > > DoCmd.OpenReport "RptScopeOfWork", acViewPreview, , , , "[[ at ]Project_ID] = > >[Forms]![FrmProjEntry_Renaissance]![Project_ID];[[ at ]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? > > -- > HTH - RuralGuy (RG for short) acXP WinXP Pro > Please post back to this forum so all may benefit. > > Message posted via http://www.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!
Todd K. wrote:
[Quoted Text] >Yes, the report will always be based on the Project_ID and Grant_Type. I got
>the "[ at ]" 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 "[ at ]" sign is from where I set my parameters in the
>stored procedure which is the source for the report:
>ALTER PROCEDURE QrySP_RptSOW
>
> (
> [ at ]Project_ID int,
> [ at ]Grant_Type nvarchar(50)
> )
>AS
>
>SELECT Project_ID, Grant_Type, City, Project_Name, Fund_Amt, Grant_No,
>Project_No, Project_Desc
>
>FROM TblProjects
>
>WHERE [ at ]Project_ID = TblProjects.Project_ID
>AND [ at ]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.[[ at ]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 [[ at ]Project_ID] and
>> [[ at ]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?
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1
|
|
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.
"ruralguy via AccessMonster.com" wrote:
[Quoted Text] > 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! > > Todd K. wrote: > >Yes, the report will always be based on the Project_ID and Grant_Type. I got > >the "[ at ]" 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 "[ at ]" sign is from where I set my parameters in the > >stored procedure which is the source for the report: > >ALTER PROCEDURE QrySP_RptSOW > > > > ( > > [ at ]Project_ID int, > > [ at ]Grant_Type nvarchar(50) > > ) > >AS > > > >SELECT Project_ID, Grant_Type, City, Project_Name, Fund_Amt, Grant_No, > >Project_No, Project_Desc > > > >FROM TblProjects > > > >WHERE [ at ]Project_ID = TblProjects.Project_ID > >AND [ at ]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.[[ at ]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 [[ at ]Project_ID] and > >> [[ at ]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? > > -- > HTH - RuralGuy (RG for short) acXP WinXP Pro > Please post back to this forum so all may benefit. > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1> > |
|
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
|
|
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 "[ at ]Project_ID int = MyProjectID,
[ at ]Grant_Type varchar(50) = MyGrantType", which is how I passed the variables
before.
"ruralguy via AccessMonster.com" wrote:
[Quoted Text] > 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
>
>
|
|
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 [ at ]Project_ID = Reports.Project_ID
AND [ at ]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.
Todd K. wrote:
[Quoted Text] >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 "[ at ]Project_ID int = MyProjectID,
>[ at ]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
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.
Message posted via http://www.accessmonster.com
|
|
Did you have any success Todd?
ruralguy wrote:
[Quoted Text] >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 [ at ]Project_ID = Reports.Project_ID
>AND [ at ]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
>
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1
|
|
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.
"ruralguy via AccessMonster.com" wrote:
[Quoted Text] > Did you have any success Todd? > > ruralguy wrote: > >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 [ at ]Project_ID = Reports.Project_ID > >AND [ at ]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 > > > > -- > HTH - RuralGuy (RG for short) acXP WinXP Pro > Please post back to this forum so all may benefit. > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1> > |
|
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.
Todd K. wrote:
[Quoted Text] >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
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1
|
|
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""
"ruralguy via AccessMonster.com" wrote:
[Quoted Text] > 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. > > > Todd K. wrote: > >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 > > -- > HTH - RuralGuy (RG for short) acXP WinXP Pro > Please post back to this forum so all may benefit. > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1> > |
|
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.
"ruralguy via AccessMonster.com" wrote:
[Quoted Text] > 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. > > > Todd K. wrote: > >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 > > -- > HTH - RuralGuy (RG for short) acXP WinXP Pro > Please post back to this forum so all may benefit. > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1> > |
|
Outstanding Todd! Thanks for posting back with your success.
Todd K. wrote:
[Quoted Text] >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]
>> >> >>> MyGrantType = Args(1)
>> >> >>> End If
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1
|
|
|