If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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 |
#22
|
|||
|
|||
Pass parameter to stored procedure
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 |
#23
|
|||
|
|||
Pass parameter to stored procedure
"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!) |
#24
|
|||
|
|||
Pass parameter to stored procedure
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 |
#25
|
|||
|
|||
Pass parameter to stored procedure
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 |
#26
|
|||
|
|||
Pass parameter to stored procedure
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 |
#27
|
|||
|
|||
Pass parameter to stored procedure
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 |
#28
|
|||
|
|||
Pass parameter to stored procedure
"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 |
#29
|
|||
|
|||
Pass parameter to stored procedure
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! |
Thread Tools | |
Display Modes | |
|
|