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.
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
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!