Solved

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

Posted on 2004-08-04
73
667 Views
Last Modified: 2008-01-09
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
Comment
Question by:fabi2004
  • 49
  • 23
73 Comments
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 11719497
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11721914
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11726706
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11727057
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11728485
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11728737
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11728905
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11729006
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11729104
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11729133
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11729192
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11729290
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11729413
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11730139
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11730442
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11730453
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11730491
:-)
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11730503
points to
DoCmd.RunSQL sExec
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11730521
Question:

What kind of data goes into the TeamName parameter.  Do you pass a string or a number?
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11730606
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11730613
so i guess i don't know if it's passing the TeamID or the name
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11730648
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11730684
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11730701
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11730716
OK, so when you run EXEC sp_TempProc what do you get?
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11730750
ERROR:  Could not find stored procedure spActivities
spActivities was created and saved through Access
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11730762
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11730767
the error is the first line
the second line is my comment on it
:-)
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11730797
same error with EXEC spActivities
Could not find stored procedure spActivities
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11730810
Do I need to re-create the spActivities through Query Analyzer?
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11730899
No just run it...
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11730912
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11730972
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11731089
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11732463
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11732467
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:fabi2004
ID: 11732474
ok...blonde roots showing now...
I re-connected to the sql db and voila...here is the sp_TempProc
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11732480
NOW...to follow your instructions...
I'll let you know in a few mins.......
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11732550
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11732700
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11735796
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11735820
PS...when I Debug - Compile I get
Compile error:
Syntax error
>>>on:
Forms(strForm).InputParameters = "@From= '" & txtFrom & "', @To= '" & txtTo & "'", @TeamName = '" & txtTeamName & "'"
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11735907
I copied your last code from 8/5 2:43pm and pasted it, saved and ran.  Same error though.
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11736807
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11736894
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11737299
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11737445
or to sp_TempProc?
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11737878
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11739722
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11739765
hmmm,
I don't get any records back with the new code
???
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11739781
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11740026
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11740043
still not getting any records when I leave the team name blank
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11740054
AND (dbo.tblTeams.Team LIKE @TeamName)???
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11740079
Yes...
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11740124
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11740157
would the keyword DISTINCT affect how the parameter is handled?
0
 
LVL 5

Accepted Solution

by:
morpheus30 earned 500 total points
ID: 11740230
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11740730
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11740823
still no records when @TeamName is left blank
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11740836
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11740854
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11755013
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11756208
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11756701
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11757068
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11757247
I didn't get any records back.
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11757277
That's wild....

Can you copy and paste the full SQL for your stored procedure?
0
 
LVL 1

Author Comment

by:fabi2004
ID: 11757320
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11757360
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
 
LVL 1

Author Comment

by:fabi2004
ID: 11757378
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 11757395
I'm glad it all worked out for ya!  :-)

0
 
LVL 1

Author Comment

by:fabi2004
ID: 11757420
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now