I am using SQL 2000 (edition installed with SBS2003 Premium) and converted an Access Database application to an .adp file. We are using Access 2003 to develop the .adp.

My issue is working with stored procedures. I created a stored procedure that works fine in Query Analyzer. For reference here it is:

CREATE PROCEDURE dbo.usp_ShowAllBetweenDates (@dtStartDate As DateTime, @dtEndDate As DateTime)
As
SELECT dbo.tblComments.chrSubjectType, dbo.tblComments.chrSubject, dbo.tblComments.chrUser, dbo.tblComments.dtmDateEnt,
                      dbo.tblComments.intHeadingID AS HeadingID, dbo.tblComments.intDoorID, dbo.tblDoors.chrDoorNr AS chrDoorN, dbo.tblHeadings.chrHeadingNr,
                      dbo.tblHeadings.intProjID, dbo.tblHeadings.chrH_Flag, dbo.tblDoors.chrD_Flag, dbo.tblComments.CommentsID, dbo.tblHeadings.chrH_Flag2,
                      dbo.tblDoors.chrD_Flag2
FROM dbo.tblComments INNER JOIN
                      dbo.tblHeadings ON dbo.tblComments.intHeadingID = dbo.tblHeadings.HeadingID INNER JOIN
                      dbo.tblDoors ON dbo.tblComments.intDoorID = dbo.tblDoors.DoorID AND dbo.tblHeadings.HeadingID = dbo.tblDoors.intHeadingID
WHERE (dbo.tblHeadings.chrHeadingNr <> N'Global') AND (dbo.tblHeadings.chrHeadingNr <> N'Schedule') AND (dbo.tblComments.dtmDateEnt >= @dtStartDate) AND
  (dbo.tblComments.dtmDateEnt <= @dtEndDate)
GO

I wrorte the following code in the .adp project which runs when the form loads that the data is supposed to populate into the form (that is what I would like it to do):

Private Sub Form_Load()

    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim param1 As ADODB.Parameter, parama2 As ADODB.Parameter

    Dim dtStartDateH As Date
    Dim dtEndDateH As Date

    dtStartDateH = Forms![frmMain]![txtSortStartDate]
    dtEndDateH = Forms![frmMain]![txtSortEndDate]

    Set cnn = CurrentProject.Connection

    Set cmd.ActiveConnection = cnn

    cmd.CommandText = "dbo.usp_ShowAllBetweenDates"
    cmd.CommandType = adCmdStoredProc

    Set param1 = cmd.CreateParameter("@dtStartDate", adDBDate, adParamInput)
    cmd.Parameters.Append param1
    param1.Value = dtStartDateH
    Set param2 = cmd.CreateParameter("@dtEndDate", adDBDate, adParamInput)
    cmd.Parameters.Append param2
    param2.Value = dtEndDateH

    cmd.Execute

It seems to run fine as I get no errors. However when the form loads I get the typical '#Name?' showing up in all my form boxes. The form label names all match the names in the stored procedure. If I use the Stored Procedure as the record source and manually enter the dates the form populates perfectly. It is just when I am trying to pass data through the VB code that nothing happens. I checked the security and the Stored Procedure has the correct Execute permisions.

Can anyone tell me what I doing wrong? Any help would be greatly appreciated.

Thanks,

Chris

At which line of code does the "#Name?" occur?