[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

how to use an unbound form to pass paramaters to a stored procedure

I have a stored procedure which generates a pivot table view of information.
Currently, I ask for three paramaters:

WHERE     (dbo.Candidate_Activity.Entry_Date BETWEEN @Beginning_Date AND @Ending_Date) AND Team = @TeamName

I'd like to use a dialog form to obtain these paramaters. I have a form with 3 controls, actually 4.  With the following code and query for the controls:

Option Compare Database
Option Explicit
Dim cboOriginator As ComboBox

Private Sub BeginningDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set cboOriginator = BeginningDate
    ocxCalendar.Visible = True
    ocxCalendar.SetFocus
    If Not IsNull(cboOriginator) Then
        ocxCalendar.Value = cboOriginator.Value
    Else
        ocxCalendar.Value = Date
    End If
End Sub


Private Sub EndingDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set cboOriginator = EndingDate
    ocxCalendar.Visible = True
    ocxCalendar.SetFocus
    If Not IsNull(cboOriginator) Then
        ocxCalendar.Value = cboOriginator.Value
    Else
        ocxCalendar.Value = Date
    End If
End Sub

Private Sub ocxCalendar_Click()
    cboOriginator.Value = ocxCalendar.Value
    cboOriginator.SetFocus
    ocxCalendar.Visible = False
    Set cboOriginator = Nothing
End Sub

.
.
.
3rd control Row Source:
SELECT     Team
FROM         tblTeams
WHERE     (DepartmentID = 1)

How do I (or can I) modify the sp to pick up the paramaters from the form?
0
fabi2004
Asked:
fabi2004
  • 49
  • 23
1 Solution
 
SayedazizCommented:
Check with the following :

WHERE     (dbo.Candidate_Activity.Entry_Date BETWEEN Forms!Formname!Beginning_Date AND Forms!FormName!Ending_Date) AND dbo.Team = Forms!Formname!TeamName

0
 
morpheus30Commented:
You could create a sub procedure in the OnClick event of your submit button to open the form in design view and change the input parameters of your stored procedure programmatically.  Below is a sample code you could use for this....

Private Sub cmdButton_Click()

On Error GoTo cmdButton_Err

Dim strForm As String

strForm = "FormName"

DoCmd.Echo False, "Preparing Report..."

DoCmd.OpenForm strForm, acViewDesign
Forms(strForm).RecordSource = "Your_StoredProcedure_Name"
Forms(strForm).InputParameters = "@From= '" & txtFrom & "', @To= '" & txtTo & "'", @TeamName = '" & txtTeamName & "'"
DoCmd.Close acForm, strForm, acSaveYes

DoCmd.OpenForm strForm, acViewPreview

cmdButton_Exit:
    DoCmd.Echo True
    Exit Sub
   
cmdButton_Err:
    Resume cmdButton_Exit
   
End Sub
0
 
fabi2004Author Commented:
Thanks Sayedaziz but that worn't work on this type of project.

Morpheus30,
I like this code but I don't have a submit button. I have an 'OK' button on the form but that's for when it's already open.  the only other button I use is the switchboard command to open the stored procedure.  Is that where you think I should insert this code?  Or is there another place where I should be creating a new 'submit' button?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
fabi2004Author Commented:
K, Morpheus
I think I know where you were going with this.  However, I'm not using a report or another form to display the results.  I just use the sp in Pivot Table view.  So, I just need to know how to tell the sp that the parameters are coming from the 'frmReportsDialog', an unbound form.
0
 
morpheus30Commented:
Hey Fabi2004,

It sounds like you just want to open the stored procedure itself and not just display the results in a form.  I must confess that I've never used a stored procedure like that before.  In looking at ways to program it, I've found it to be a lot tougher than I expected, but I found an article on EE that address this issue in an ingenious way:

http://www.experts-exchange.com/Databases/MS_Access/Q_20767481.html

The very last comment is your answer.  I've adopted it for your needs to something like this....

First run this command in SQL Analyzer:

CREATE PROCEDURE sp_TempProc AS EXEC sp_YourSprocName @Beginning_Date = '1/1/2004', @Ending_Date = '12/31/2004', @TeamName = 'TeamName'

This will create the temporary stored procedure sp_TempProc you'll use to modify your parameters.  Then put the following code in your command button:

Dim sExec As String

sExec = "ALTER PROCEDURE sp_TempProc as EXEC sp_YourSprocName @Beginning_Date='" & Forms!FormName!FromTextBox & "', @Ending_Date='" & Forms!FormName!FromTextBox & "', @TeamName=' & Forms!FormName!TeamNameControlValue & "'"
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewNormal, acReadOnly

The idea here is to create a temporary stored procedure that is created from the result set of the original stored procedure with the proper parameters.

Good Luck...
0
 
fabi2004Author Commented:
Morpheus, ( I like your code name by the way)
I'm still unclear as to which command button you are refering to.  The birds eyeview of this scenario is:
A switchboard with one of the cmd buttons running a macro that opens a stored procedure in pivot table view.
The stored procedure  currently has built in parameters, @BeginningDate, @EndingDate, @Team
What I'd like to do...is use a form wich contains 3 fields, cmdBeginningDate, cmdEndingDate and cmdTeamName
The first two have a pop-up calendar for entering the dates and default to today's date.  The last is a list of teams populated by a Row Source query that pulls the info from a table so the user has to select the name instead of typing in the info. I'm using this unbound form successfully on reports but since this is an sp PT, it doesn't work the same.  I believe the only the ADO command object supports passing parameters to stored procedures.  So ??? I need to create a recordset? and?  I'm so lost.  Maybe I need to take another route with this and have that Pivot Table viewed through a form or report instead of through the SP.  It sure seems like it would be straightforward to pass form parameters to an sp but apparently, it's not.  Really need some direction with this.  I'd love to try the code above, but I don't kow where to put it.  
0
 
morpheus30Commented:
Oh, OK....

In your form (the one that has the 3 fields in it) create a new command button and call it cmdPivotTable or whatever you like.  In the OnClick event of this new button add the code I mentioned above.  

Make sure you replace the Forms!FormName!FromTextBox with the names of the fields corresponding to the parameters you need.  Also make sure you replace the name of sp_YourSprocName with the correct name of your Stored Procedure.

Try it and let me know how it goes...  Better yet can you list the names of the 3 controls in your form and the name of your stored procedure?  I can then build the code for you.
0
 
fabi2004Author Commented:
Thanks so much!
I'll be trying it in the meantime but here are the controls on my form:
BeginningDate
EndingDate
TeamName
Label3
ocxCalendar
OK
Cancel
>>>>>>>
and the stored procedure is spActivities


PS...after I add this button to the form, should I use the switchboard to open the form instead of the stored procedure and will this open the stored procedure in pivot table view?......OK...I'm off to test this....
THANKS
0
 
morpheus30Commented:
Yes, you should use the switchboard to open this form instead.  I've modifed the code just slightly to open the stored procedure in Pivot Table view...

Dim sExec As String

sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName=' & Me.TeamName & "'"
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly
0
 
fabi2004Author Commented:
WOW...you're fast
I was still trying to modify the code on mine.
I'll try this and get right back to you
Thanks,
0
 
morpheus30Commented:
Sorry, I forgot a quotation mark....

Dim sExec As String

sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName='" & Me.TeamName & "'"
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly

0
 
fabi2004Author Commented:
OK...two things
1.  When I try to run just the spTempProc on it's own, I get the error:  could not find the stored procedure spActivities.  I created both sps in the same adp.  Should I have used the SQL Query Analyser to create the temp one?  Would it actually make a difference?
2.  Clicking on the new button.  I get the error:  Run-Time error '8114'  Error converting data type varchar to smallint.  This would be coming from one of my 3 parameter controls right?  2 date ones and 1 txt.  
???
We need to have a talk with the Access and SQL MS developers...gotta be an easier way to pass parameters to SPs  :-)
0
 
morpheus30Commented:
You're right, I don't understand why the developers of Access didn't allow to pass parameters right from DoCmd.OpenStoredProcedure....

OK, first you should run this code in SQL Analyzer because the Access ADP window unfortunately doesn't let you run these type of commands by themselves without first saving them as views or stored procedures and you don't want to do this:

CREATE PROCEDURE sp_TempProc AS EXEC spActivities @Beginning_Date = '1/1/2004', @Ending_Date = '12/31/2004', @TeamName = 'TeamName'

Run it preferably with valid data so as to create the sp_TempProc.  

As for your second question my guess is that your list box (the one with TeamName) actually returns an integer, not a string, which I suppose represents the Team Name's ID number.  If that's the case, then the code must be altered like this:

Dim sExec As String

sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName=" & Me.TeamName
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly

Let me know....

0
 
fabi2004Author Commented:
Sorry it took so long,
I was trying to work through some errors but it seems like I no sooner get rid of one than another appears.  This one is now too general so I don't see where the prob is.  Probably a small change I mande threw the whole thing off.  Will you look at it for me PLEASE???

Private Sub cmdPivotTable_Click()
Dim sExec As String

sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName='" & Me.TeamName "'
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly

End Sub
0
 
morpheus30Commented:
Hey fabi,

I'm back from lunch....  Try this code:

Private Sub cmdPivotTable_Click()
Dim sExec As String

sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName=" & Me.TeamName
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly

End Sub
0
 
fabi2004Author Commented:
I finally have the right quote marks in the right places.  Now however, I'm getting an error message:  Run-time error '4610':  You can only grant or revoke permissions on objects in the current database.
(pointing to this line)
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
0
 
fabi2004Author Commented:
:-)
I think we were writing at the same time.
I did paste your new code and now I have:
Run-time error '170'
Line 1 incorrect syntax near =
0
 
fabi2004Author Commented:
points to
DoCmd.RunSQL sExec
0
 
morpheus30Commented:
Question:

What kind of data goes into the TeamName parameter.  Do you pass a string or a number?
0
 
fabi2004Author Commented:
this is the Row Source for cmdTeamName:  SELECT Team FROM tblTeams WHERE (DepartmentID = 1)
And this is an example of the table data
DepartmentID      Team      TeamID
Allied Marketing       JPELLEGRIN      1
Allied Marketing       GFITHIAN        2
Allied Marketing       JLINDSEY        3
Allied Recruiting       CHORVATH  4
Allied Recruiting       CCLARK          7
Allied Recruiting       DSTALEY         9
The DepartmentID field in the table has a look up list box:
SELECT DepartmentID, Department FROM tblDepartments WHERE (DepartmentID <> 6)
0
 
fabi2004Author Commented:
so i guess i don't know if it's passing the TeamID or the name
0
 
fabi2004Author Commented:
I'm confusing myself
What I meant to say is that the Team field in the tblTeams has a lookup combo box:  SELECT User_Name FROM User_Records WHERE (DepartmentID <> 6)
and the field is a char(10)
0
 
morpheus30Commented:
Then it's a string....

Now, before I go on though, what happens when you do this in SQL Query Analyzer:

CREATE PROCEDURE sp_TempProc AS EXEC spActivities @Beginning_Date = '1/1/2004', @Ending_Date = '12/31/2004', @TeamName = 'JPELLEGRIN'

EXEC sp_TempProc
0
 
fabi2004Author Commented:
Well,
If I don't drop the procedure first then I get an error saying it already exists
If I drop and then create, then I get a completed successfully msg.
0
 
morpheus30Commented:
OK, so when you run EXEC sp_TempProc what do you get?
0
 
fabi2004Author Commented:
ERROR:  Could not find stored procedure spActivities
spActivities was created and saved through Access
0
 
morpheus30Commented:
What happens when you do this:

EXEC spActivities @Beginning_Date = '1/1/2004', @Ending_Date = '12/31/2004', @TeamName = 'JPELLEGRIN'


Remember this is all done in SQL Query Analyzer....
0
 
fabi2004Author Commented:
the error is the first line
the second line is my comment on it
:-)
0
 
fabi2004Author Commented:
same error with EXEC spActivities
Could not find stored procedure spActivities
0
 
fabi2004Author Commented:
Do I need to re-create the spActivities through Query Analyzer?
0
 
morpheus30Commented:
No just run it...
0
 
morpheus30Commented:
Oh...

Hang on....  In SQL Query Analyzer make sure you've set the correct database.  The default is usually Master (which is 99% of the time incorrect)
0
 
fabi2004Author Commented:
I wish...
But no, I'm on the right DB...same error.
.....
I have to run and take my kid to football practice.  I'll be looking at this again in about 4 hours.  I feel like I'm running out on you when you're being so helpful!  Thank you SO MUCH.  I'll check this post as soon as I get back in front of a PC today.
Thanks so much.
0
 
morpheus30Commented:
hmmm....

We must troubleshoot this before we move on.  First find out for me in Access whether the Stored Procedures spActivities and sp_TempProc exist.  Just go to the Query tab in Access and look for these two stored procedures.

If both of them exist, then double click the sp_TempProc first to see what happens.  If you get an error, then double click the spActivities.  If you are not asked for parameters to enter, then something is wrong with spActivities.

If everything is O.K. with spActivities but sp_TempProc has the problem, then delete sp_TempProc and change the code to this:

Dim sExec As String

sExec = "CREATE PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName='" & Me.TeamName & "'"
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly

Do this ONLY ONCE.  Then if it works the first time, go back to the code and change it to this:

Dim sExec As String

sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName='" & Me.TeamName & "'"
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly

Good luck!
0
 
fabi2004Author Commented:
OK
in Access, spActivities exists, and works correctly, asking for the parameters
spTempProc does not exist, this one we created through the spl query analyzer
.
.
.
I just now tried dropping the sp_TempProc (in Query Analyzer); recreating it; and running it (EXEC)..........
I received a different error...one we've seen in Access earlier:
Server: Msg 8114, Level 16, State 4, Procedure spActivities, Line 0
Error converting data type varchar to smallint.
>>>>>>>>
This is an awesome piece of code to work around the passing parameters to SPs prob.  If you can help me troubleshoot in my particular db app, I can use this 'forevermore' since I have many, many SPs in this, and other .adp(s)  I'm so excited about this!
0
 
fabi2004Author Commented:
PS.  sp_TempProc still does not show up in the Access query list  (could it be renamed or some other prefix added to it?  I have a loooong list of query's Sps and views in here)
0
 
fabi2004Author Commented:
ok...blonde roots showing now...
I re-connected to the sql db and voila...here is the sp_TempProc
0
 
fabi2004Author Commented:
NOW...to follow your instructions...
I'll let you know in a few mins.......
0
 
fabi2004Author Commented:
sp_TempProc:  error converting data type from varchar to smallint

sp_Activities:  prompts for all 3 parameters, dates work, TeamName error using (cclark):  The value you entered isn't valid for this field.  For example, you may have entered text in a numeric field or a number that is larger than the fieldsize setting permits.

I think I know why this is happening.  The @TeamName parameter in spActivities is populating the Team field in the sp with data from the tblUsers.TeamId, in which TeamID is stored as a smallint.  The frmReportsDialog2 is populating the TeamName combo box from [SELECT Team FROM tblTeams WHERE (DepartmentID = 1)] where the tblTeams.Team has a lookup of [ SELECT User_Name FROM User_Records WHERE (DepartmentID <> 6)] and the field is a char(10).

You told me earlier that this way the frmReportsDialog2.TeamName is passing the char type instead of the smallint type.

The spActivities is expecting a smallint type.

Sooooo......

The one I'd like to modify is the spActivities parameter since I'd really like to display the team "name" instead of ID.  A couple of ways I can think of to do that.  Let's see if I can find the path of least resistance.

I think you're gone for the day.  So, I'll keep you posted on my progress.

THANKS AGAIN
0
 
fabi2004Author Commented:
I think I got it
I'll check everything tomorrow at the office and let you know if it all works like it's supposed to.
g'night
0
 
fabi2004Author Commented:
Good Morning Morpheus,
At this point the sp_TempProc and spActivities are both working correctly.  However, the form is now generating errors:
Run-time error '2447'
There is an invalid use of the .(dot) or ! operator or invalid parentheses.
>>>Debug points to the first line in this code when I click the cmdViewReport:
Private Sub cmdPivotTable_Click()
Dim sExec As String

sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName=" & Me.TeamName
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly

End Sub

If I click the either of the date controls then I get the same message but with the debugger stopping at different locations.

I'd swear the form was working yesterday!

Any ideas?
0
 
fabi2004Author Commented:
PS...when I Debug - Compile I get
Compile error:
Syntax error
>>>on:
Forms(strForm).InputParameters = "@From= '" & txtFrom & "', @To= '" & txtTo & "'", @TeamName = '" & txtTeamName & "'"
0
 
fabi2004Author Commented:
I copied your last code from 8/5 2:43pm and pasted it, saved and ran.  Same error though.
0
 
morpheus30Commented:
Good Morning Fabi,

Hey, I see you've been quite busy....  That last error you got: Forms(strForm).InputParameters you're getting is probably from code in the Form_Open event of your form.  Delete this code by and let me know what happens.
0
 
fabi2004Author Commented:
Hi Morpheus!  Boy, I've missed you.  :-)

I couldn't find a way around that error this morning.  Combed the internet but no luck.

I just checked the Form_Open event for code, but there isn't any.

Since I couldn't find a way around the error, I reverted back to an earlier copy of the db.  (I always try to keep copies when I'm modifying code so if something gets really messed up I can go back to when it worked)
Anyways, in the earlier version of the db...things look like they are working.  The form is not generating errors.  The spActivities pulls up the correct information in Pivot Table view though it's not yet formatted (guess it lost it's fomatting).  So, I am now trying to re-create the correct Pivot Table view and as long as nothing else comes up with this.  We may be done....  If that's the case, you should be able to hear my celebration yell from whereever you are.  :-)...
I'll let you know in a little while.
0
 
fabi2004Author Commented:
Almost, but not quite
When I enter parameters in the form it passes them and opens the newly formatted pivot table.  Perfect.  However, one of the parameters should work like this: "Please select a team from the list.  Leave blank to view ALL teams."  If I leave this blank to pull information for all the teams, I get a run-time error '170'   Line1:  Incorrect Syntax near '='.  The Debug points to the 2nd line here:
sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName=" & Me.TeamName
DoCmd.RunSQL sExec
>>>>>FYI, I use an almost identical form to input parameters to a report that runs from an sp.  I looked at the code in that sp to see how the @TeamName was developed and here what I have:
ALTER PROCEDURE dbo.spScreetoIV2
@TeamName varchar(30), @BeginningDate datetime, @EndingDate datetime
AS
IF @TeamName IS NULL BEGIN
SET              @TeamName = '%' END ELSE BEGIN
SET              @TeamName = @TeamName + '%' END
>>>>etc.
Now, before I go about destroying this 'almost working perfectly code' that you created....
Should I add the above IF to my spActivities?
0
 
fabi2004Author Commented:
or to sp_TempProc?
0
 
morpheus30Commented:
Hey Fabi...

Sorry to get back so late, had a meeting with my boss...  Your suggestion is correct.  If you can modify the stored procedure so that it has a default value that will work.  One thing I noticed in your SProc above is that TeamName is a varchar(30) not a number.  Is this what you want?  If so you don't have to modify anything in the SP.  You could just change my code to this:

Dim sExec As String
Dim sTeamName As String

If Nz(Me.TeamName, "") = "" Then
  sTeamName = "%"
Else
  sTeamName = Me.TeamName
End If

sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName='" & sTeamName & "'"
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly
0
 
fabi2004Author Commented:
Morpheus,
me too with the meetings, jeez, all afternoon...and I get so impatient with them when I'm trying to work on code.
Anyways,
Yes, I modified the spActivities so the @TeamName parameter could be passed as str.
I'll copy this code into the db and let you shortly how beautifully it works.
If I could actually finish this today and take my weekend trip without it hanging over my head I would be thrilled.
BRB
0
 
fabi2004Author Commented:
hmmm,
I don't get any records back with the new code
???
0
 
fabi2004Author Commented:
also, how do I close the form after the parameters have been passed to the sp?
before, I was using the SetValue action [Forms]![frmReportsDialog2].[Visible] = no
0
 
morpheus30Commented:
Hey Fabi,

Great to read back from ya!  I forgot to mention that the SQL in spActivities needs to be altered slightly.  Change the WHERE clause corresponding to the TeamName, change the = sign to LIKE.

Try that and let me know...
0
 
fabi2004Author Commented:
still not getting any records when I leave the team name blank
0
 
fabi2004Author Commented:
AND (dbo.tblTeams.Team LIKE @TeamName)???
0
 
morpheus30Commented:
Yes...
0
 
fabi2004Author Commented:
not working...
earlier I tried modifying spActivities to:
ALTER PROCEDURE dbo.spActivities
(@Beginning_Date datetime,
@Ending_Date datetime,
@TeamName char(10))
AS
IF @TeamName IS NULL BEGIN
SET              @TeamName = '%' END ELSE BEGIN
SET              @TeamName = @TeamName + '%' END
SELECT.........
Thought that would have done the trick but still no records when @TeamName is left blank
0
 
fabi2004Author Commented:
would the keyword DISTINCT affect how the parameter is handled?
0
 
morpheus30Commented:
No the DISTINCT keyword should not affect anything....

Change the code to this.  Make sure your stored procedure still has the LIKE operator in it!

Dim sExec As String
Dim sTeamName As String

If Me.TeamName = "" Then
  sTeamName = "%"
ElseIf IsNull(Me.TeamName) Then
  sTeamName = "%"
Else
  sTeamName = Me.TeamName
End If

sExec = "ALTER PROCEDURE sp_TempProc as EXEC spActivities @Beginning_Date='" & Me.BeginningDate & "', @Ending_Date='" & Me.EndingDate & "', @TeamName='" & sTeamName & "'"
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON sp_TempProc TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "sp_TempProc", acViewPivotTable, acReadOnly

0
 
fabi2004Author Commented:
right now spActivities does have he LIKE operator for @TeamName...but it also as the
AS
IF @TeamName IS NULL BEGIN
SET              @TeamName = '%' END ELSE BEGIN
SET              @TeamName = @TeamName + '%' END
SELECT.........
should I remove that part?
0
 
fabi2004Author Commented:
still no records when @TeamName is left blank
0
 
fabi2004Author Commented:
I'm out of ideas...don't know what else to try
checked the relationships/joins among the tables and it looks good
procedure works like a charm as long as a team name is selected from the list
0
 
fabi2004Author Commented:
Tried with and without this in the sp:
AS
IF @TeamName IS NULL BEGIN
SET              @TeamName = '%' END ELSE BEGIN
SET              @TeamName = @TeamName + '%' END
SELECT.........
Same results either way.
no records when the param is left blank
I'm off for the weekend.  Have a great one.  And thanks SO MUCH for all you help this week.

0
 
fabi2004Author Commented:
Hi Morpheus,
Hope you had a great weekend.  I'm looking at this again and honestly, I have no idea what to try next.  This @TeamName param works from a form when I pass it onto a report but it doesn't work exactly right passing it straight to the SP.  Any suggestions?  
Thanks!
Fabi
0
 
morpheus30Commented:
Hey Fabi,

Are you saying that the SP works correctly with the form but it doesn't if you run it by itself?  Run it how?  By double clicking it from the Access Window?
0
 
fabi2004Author Commented:
No, everything works correctly except...if the @TeamName is left blank, it should pass the '%' and pull all records regardless of the team (even if the record has a blank team), What is happening is that when the @TeamName param is left blank...all records are excluded; instead of all records being included.  This only occurs if the @TeamName param is left blank, if a team is present, i.e. 'cclark' then everything works like a charm.  This is all SO CLOSE.  Just need to modify a tiny bit so that if the pram for team is blank, it isnot used as a criteria for the records.
Does this make sense to you?
0
 
morpheus30Commented:
Do a test for me:

Run the SP by itself with the correct date parameters, but when it asks you for the Team Name parameter, type the percent sign (%).  What this SHOULD do is tell the query to include EVERY record with at least a LETTER in it.  (This means ALL THE RECORDS).   Let me know what you get...
0
 
fabi2004Author Commented:
I didn't get any records back.
0
 
morpheus30Commented:
That's wild....

Can you copy and paste the full SQL for your stored procedure?
0
 
fabi2004Author Commented:
ALTER PROCEDURE dbo.spActivities
(@Beginning_Date datetime,
@Ending_Date datetime,
@TeamName char(10))
AS
SELECT DISTINCT
                      dbo.Candidate_Activity.Entry_Date AS Date, dbo.User_Records.Description, dbo.Candidate_Activity.User_Name,
                      dbo.Candidate_Activity.Act_Type AS Activity, dbo.tblDepartments.Department, dbo.tblTeams.Team
FROM         dbo.Candidate_Activity INNER JOIN
                      dbo.User_Records ON dbo.Candidate_Activity.User_Name = dbo.User_Records.User_Name INNER JOIN
                      dbo.tblDepartments ON dbo.User_Records.DepartmentID = dbo.tblDepartments.DepartmentID INNER JOIN
                      dbo.tblTeams ON dbo.User_Records.TeamID = dbo.tblTeams.TeamID
WHERE     (dbo.Candidate_Activity.Entry_Date BETWEEN @Beginning_Date AND @Ending_Date) AND (dbo.tblTeams.Team LIKE @TeamName)
GROUP BY dbo.Candidate_Activity.User_Name, dbo.Candidate_Activity.Act_Type, dbo.Candidate_Activity.Entry_Date, dbo.tblDepartments.Department,
                      dbo.User_Records.Description, dbo.tblTeams.Team
HAVING      (dbo.Candidate_Activity.Act_Type = 'stage' OR
                      dbo.Candidate_Activity.Act_Type = 'SCREEN' OR
                      dbo.Candidate_Activity.Act_Type = 'PASS FROM' OR
                      dbo.Candidate_Activity.Act_Type = 'a/r call' OR
                      dbo.Candidate_Activity.Act_Type = 'ps pass to') AND (dbo.tblDepartments.Department = 'Allied Recruiting' OR
                      dbo.tblDepartments.Department = 'NURSE RECRUITING')






0
 
fabi2004Author Commented:
I just changed @TeamName char(10) to @TeamName varchar(10) ; used the % for the param and BINGO...I have records.
Let me check using the form now.
0
 
fabi2004Author Commented:
IT WORKS
It's not pulling the records exactly like I want them but that's just the sql design.  I can manipulate that to show the records correctly.
You are incredible!!!!!
I can't thank you enough!
0
 
morpheus30Commented:
I'm glad it all worked out for ya!  :-)

0
 
fabi2004Author Commented:
BTW...
I have another questions open that uses the information from this SP...I have to transfer it to an Excel template...through automation?  Anyways, if you're interested helping me go forwards with this, please see my other question...
Again, thank you so very, very much for all your help!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 49
  • 23
Tackle projects and never again get stuck behind a technical roadblock.
Join Now