Problem passing parameters to stored procedure

pbrock · December 30th, 2011, 10:10 PM

I have been trying everything I can think of to get this to work and have been unable to do so, therefore, I through myself on the mercy of the forum! I have been given a project due to my familiarity with SQL and dBase/Clipper programming years ago. An example of what I am trying to do follows.

I have a main form where I have text boxes to collect the invoice number and date range to be used throughout the application which performs various checks on the current month invoice. One of the tests is to produce a pivot table of hours by type of job for each employee for the month. I have this listed by week in a SQL view and then have created a stored procedure to produce the monthly result. The stored procedure is the recordsource for the report where I add totals by type of labor, job, and grand totals.

The following code was added to the On Click event of a command button to produce the report. The main form is a tabbed form with the first tab being where I collect the variables which are global. Other tabs are where I perform the calculations by labor types.

Code:

    Dim cmdCommand As ADODB.Command
    Dim prmStart As ADODB.Parameter
    Dim prmEnd As ADODB.Parameter
    Dim dtStart As Date
    Dim dtEnd As Date
    
    dtStart = Me.txtInvBegDt
    dtEnd = Me.txt_InvEndDt
     
    Set cmdCommand = New ADODB.Command
    cmdCommand.ActiveConnection = CurrentProject.Connection
    
    cmdCommand.CommandType = adCmdStoredProc
    cmdCommand.CommandText = "usp_AH_ByJob_Pivot"
    
    Set prmStart = cmdCommand.CreateParameter("@BegDt", adDate, adParamInput)
    Set prmEnd = cmdCommand.CreateParameter("@EndDt", adDate, adParamInput)
    
    prmStart.Value = dtStart
    prmEnd.Value = dtEnd
    cmdCommand.Parameters.Append prmStart
    cmdCommand.Parameters.Append prmEnd
    
    cmdCommand.Execute

There may be something basic which I am missing here, but I have stepped through the code and it seems the parameter is being passed but the sproc doesn't seem to be running. Since it is the recordsource for the report, should I try opening the report instead? I am a little confused by some of the new object oriented programming. I have many of the SQL queries written but need to get this date logic working to make an efficient application for the users. I appreciate any assistance that any of you may provide. Thank you in advance! And, happy new year!