Pass parameter to stored procedure

Hi again - I hit send too soon g.

I've never used pass-through queries --- I know it's possible to write
an Access (JET) query that pulls a parameter from a form; I don't know
if you can do that with a pass-through query or not.

Maybe your missing my 2nd response...


That's why I normally retain at least some (most?) of the original post
in my replies -- historical data is right in front of the responder:
(s)he doesn't have to hunt down the thread history.

I have my newsreader set to hide anything I've already seen -- I suspect
many others here who use newsreaders do the same.

--
Clif


"Clif McIrvin" wrote in message
...
Try using code to launch the report from the on-click event that gets
the date from the user. Your existing code successfully modifies the
pass-through query; if you then launch the report the query should
return the recordset you're looking for.

My suggestion below does not fit this situation.

--
Clif

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:9916fd6bde566@uwe...
perhaps you need something like

Set rst = qdfPass.OpenRecordset (dbOpenSnapshot)

I'm not sure, but I'll try.

Otherwise, from your conversation with Doug it appears that we're
missing something that you think you've told us.

Maybe your missing my 2nd response...

What my original post did not include was that I desire to open the
results
of this process into a report "rptActivityMaster". Currently, I
manually edit
the date of the above statement and open the report using
"qsptActivityMaster" as the Record Source.

--
---
TraciAnn

Message posted via http://www.accessmonster.com




--
Clif




--
Clif

We've been discussing code that changes the SQL contained in qsptPassThrough
so that it encapsulates the value entered on the form. When you open the
report once that code's run, you'll be getting the report based on the
information entered on the form.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:9916eef2f8161@uwe...
I'm sorry to be so naive, but it seams that puts me back to where I
started.

I'm trying to pass a date, that the user enters into a form, as a
parameter
(@ActivityDate) to the sproc.

If I put qsptPassThrough as the RecordSource of the report
(rptActivityMaster)
I will need to hard code the date.

I really am sorry that I'm not getting this. Thank you for your patience.

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

"Clif McIrvin" wrote in message
...
Hi again - I hit send too soon g.

I've never used pass-through queries --- I know it's possible to write an
Access (JET) query that pulls a parameter from a form; I don't know if you
can do that with a pass-through query or not.

No, it's not possible. Pass-through queries run on the server, so they don't
know anything about Access forms or VBA code.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

AWESOME!!!

I'm almost there...

Current code is:
Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdfPass = CurrentDb.QueryDefs("qsptPassThrough")
qdfPass.SQL = "exec usp_ComTrakActivityMaster @ActivityDate=" & _
"'" & Format(Me.txtActivityDate, "mm/dd/yyyy") & "'"
DoCmd.OpenReport "rptActivityMaster", acViewReport
DoCmd.Close acForm, "frmActivityMaster"

Now I need to pass the same parameter to objects within the report.
Currently, the report uses a "[Enter Activity Date]" in several of its
arguments so, when the code opens the report a message box appears saying
"Enter Activity Date". Since the date was provided from txtActivityDate in
frmActivityMaster I need to pass the same value to the report.

I have a text box on the report "txtDateEntered" that has "=[Enter Activity
Date]"

Do I add an argument to the OpenReport command line that passes Me.
txtActivityDate to txtDateEntered or to "[Enter Activity Date]"?

Don't laugh but I unsuccessfully tried :
DoCmd.OpenReport "rptActivityMaster", acViewReport, , , ,
"txtDateEntered" = Me.txtActivityDate

What is the correct syntax?

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

Can you change the stored procedure so that it returns the date as one of
the fields in its recordset?

If not, don't close the form, so that the report can refer to
Forms!frmActivityMaster!txtActivityDate instead, or pass the date as a
OpenArgs value and handle it in the code behind the report.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:991d5ae652fa8@uwe...
AWESOME!!!

I'm almost there...

Current code is:
Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdfPass = CurrentDb.QueryDefs("qsptPassThrough")
qdfPass.SQL = "exec usp_ComTrakActivityMaster @ActivityDate=" & _
"'" & Format(Me.txtActivityDate, "mm/dd/yyyy") & "'"
DoCmd.OpenReport "rptActivityMaster", acViewReport
DoCmd.Close acForm, "frmActivityMaster"

Now I need to pass the same parameter to objects within the report.
Currently, the report uses a "[Enter Activity Date]" in several of its
arguments so, when the code opens the report a message box appears saying
"Enter Activity Date". Since the date was provided from txtActivityDate in
frmActivityMaster I need to pass the same value to the report.

I have a text box on the report "txtDateEntered" that has "=[Enter
Activity
Date]"

Do I add an argument to the OpenReport command line that passes Me.
txtActivityDate to txtDateEntered or to "[Enter Activity Date]"?

Don't laugh but I unsuccessfully tried :
DoCmd.OpenReport "rptActivityMaster", acViewReport, , , ,
"txtDateEntered" = Me.txtActivityDate

What is the correct syntax?

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

Can you change the stored procedure so that it returns the date as one of
the fields in its recordset?

This takes an act of God.

If not, don't close the form,

ok. is there a way to hide the form so it doesn't remain in front of the
report? I assume I would then close the form in a OnClose Event of the report?


so that the report can refer to
Forms!frmActivityMaster!txtActivityDate instead,

I have multiple controls in the report that currently refer to "[Enter
Activity Date]". If I do the above, would something like "=Count(IIf(
[IntroEmailDate]=[Enter Activity Date],1,Null))" Change to "=Count(IIf(
[IntroEmailDate]=Forms!frmActivityMaster!txtActivityDate,1,Null))" ?

or pass the date as a
OpenArgs value and handle it in the code behind the report.

"pass the date as a OpenArgs value" - Is this done in the DoCmd.OpenReport
command line? How?

"handle it in the code behind the report" - I'm unsure how to do this? I'm
sorry that I require you to be more specific.

Thank you so much for your help! You are terrific!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

I'll jump in again regarding using OpenArgs, if you choose to go that
route (but I think Doug's suggestions 1 or 2 could be "easier").

The OpenArgs property is simply a string value containing whatever you
pass in the DoCmd.Open... statement. It's up to your VBA code to
extract the relevant bits of information and do the right things with
it.

So, you could change

DoCmd.OpenReport "rptActivityMaster", acViewReport, , , ,
"txtDateEntered" = Me.txtActivityDate

to

DoCmd.OpenReport "rptActivityMaster", acViewReport, , , , _
Me.txtActivityDate

and in the Open (or load?) event of the report you could test for the
presence of a valid date in the OpenArgs property and use it; if none
supplied do nothing and the report will prompt for a date as it does
now.

Something like (air code):

dim strDate as string
strDate = me.OpenArgs
code to test for valid date
if strDate is valid then
me.txtDateEntered = strDate
endif

HTH!
--
Clif


"Douglas J. Steele" wrote in message
...
Can you change the stored procedure so that it returns the date as one
of the fields in its recordset?

If not, don't close the form, so that the report can refer to
Forms!frmActivityMaster!txtActivityDate instead, or pass the date as a
OpenArgs value and handle it in the code behind the report.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:991d5ae652fa8@uwe...
AWESOME!!!

I'm almost there...

Current code is:
Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdfPass = CurrentDb.QueryDefs("qsptPassThrough")
qdfPass.SQL = "exec usp_ComTrakActivityMaster @ActivityDate=" & _
"'" & Format(Me.txtActivityDate, "mm/dd/yyyy") & "'"
DoCmd.OpenReport "rptActivityMaster", acViewReport
DoCmd.Close acForm, "frmActivityMaster"

Now I need to pass the same parameter to objects within the report.
Currently, the report uses a "[Enter Activity Date]" in several of
its
arguments so, when the code opens the report a message box appears
saying
"Enter Activity Date". Since the date was provided from
txtActivityDate in
frmActivityMaster I need to pass the same value to the report.

I have a text box on the report "txtDateEntered" that has "=[Enter
Activity
Date]"

Do I add an argument to the OpenReport command line that passes Me.
txtActivityDate to txtDateEntered or to "[Enter Activity Date]"?

Don't laugh but I unsuccessfully tried :
DoCmd.OpenReport "rptActivityMaster", acViewReport, , , ,
"txtDateEntered" = Me.txtActivityDate

What is the correct syntax?

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1






--
Clif

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:991da4e4ae248@uwe...
Can you change the stored procedure so that it returns the date as
one of
the fields in its recordset?

This takes an act of God.

Ah ... one of those grin.


If not, don't close the form,

ok. is there a way to hide the form so it doesn't remain in front of
the
report?

try: Forms!frmActivityMaster.Visible = False

I assume I would then close the form in a OnClose Event of the report?


yes


snip

--
Clif

You can set the form's visible property to false. That will hide the form,
but keep it open. And yes, you could then close the form in the Close event
of the report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

TraciAnn via AccessMonster.com wrote:
Can you change the stored procedure so that it returns the date as one of
the fields in its recordset?

This takes an act of God.

If not, don't close the form,

ok. is there a way to hide the form so it doesn't remain in front of the
report? I assume I would then close the form in a OnClose Event of the report?


so that the report can refer to
Forms!frmActivityMaster!txtActivityDate instead,

I have multiple controls in the report that currently refer to "[Enter
Activity Date]". If I do the above, would something like "=Count(IIf(
[IntroEmailDate]=[Enter Activity Date],1,Null))" Change to "=Count(IIf(
[IntroEmailDate]=Forms!frmActivityMaster!txtActivityDate,1,Null))" ?

or pass the date as a
OpenArgs value and handle it in the code behind the report.

"pass the date as a OpenArgs value" - Is this done in the DoCmd.OpenReport
command line? How?

"handle it in the code behind the report" - I'm unsure how to do this? I'm
sorry that I require you to be more specific.

Thank you so much for your help! You are terrific!