Calling a stored procedure and passing parameter

[OtheymAverian]

I am trying to call a stored procedure using DoCmd.RunSQL, but I need to pass a parameter as well. I cannot figure out how to do this. I have coded a variable that will take the input for the parameter, but I do not know how to associate it with my call.
Here is the code that I made before I got stuck.

Private Sub butMoveNDStoBO_Click()
On Error GoTo Err_butMoveNDStoBO_Click

    Dim RecNo As Long
    Dim SQL As String
    
    RecNo = InputBox(Prompt:="Enter Record Number")
    SQL = "EXEC usp_MoveNDS_to_BO"
    
    DoCmd.RunSQL SQL
    

Exit_butMoveNDStoBO_Click:
    Exit Sub

Err_butMoveNDStoBO_Click:
    MsgBox Err.Description
    Resume Exit_butMoveNDStoBO_Click
    
End Sub

Thank you in advance for your assistance.

 

[MarkK]

Where processing speed is not critical I'd use the full text of the query in the code, and implement a WHERE clause for the variable supplied by the user.
Also, consider using the .Execute method of a DAO.Database object if you want to cleanly avoid prompts...

  ...
  RecNo = InputBox("Enter Record Number")
  CurrentDb.Execute _
    "DELETE FROM YourTable WHERE RecNum = " & RecNo & ";"
  ...

Where speed is critical you can use a parameter query, but I find that a little trickier.

 

[OtheymAverian]

I tried copying the code from the stored procedure into the code for the button and I am getting an error message now. I am running this as an ADP, so I do not know if I will be able to use the suggestion you gave for DAO.

Code is:

Private Sub butMoveNDStoBO_Click()
On Error GoTo Err_butMoveNDStoBO_Click

    Dim RecordNo As Long
    Dim strSQL As String
    
    RecordNo = InputBox(Prompt:="Enter Record Number")
    
    strSQL = "DECLARE @AdjustDate datetime, @SalesDate datetime " & _
    "SET @AdjustDate = (SELECT dbo.udf_DateAdjAmt(GetDate()))" & _
    "SET @SalesDate = CONVERT(datetime, CAST(CAST(Month(@AdjustDate) AS varchar(2))" & _
    "+ '/' + CAST(Day(@AdjustDate) AS varchar(2)) + '/' +" & _
    "CAST(Year(@AdjustDate) AS varchar(4)) AS varchar(10)))" & _
    "INSERT BackOffs (DSKey, DelDate, BODate, DealerID, SalesPersonID, FinMgrID, CustomerName, Stock#," & _
     "NewUsed, TransactType, Gross, ESAGross, ESASold, ESAType, ESAPolicy, CLGross, CLSold, CLType, CLPolicy," & _
     "AHGross, AHSold, AHType, AHPolicy, ETCHGross, ETCHSold, ETCHPolicy, GAPGross, GAPSold, GAPType," & _
     "GAPPolicy, IDENSold, IDENGross, EnviroShieldGross, EnviroPolicy, SpitzerCare, OtherGross, OtherCount," & _
     "PVR, Make, Spitzer300, LeaseTerm)" & _
    "SELECT DSKey, DelDate, @SalesDate, DealerID, SalespersonID, FinMgrID, CustomerName," & _
     "Stock#, NewUsed, TransactType, Gross, ESAGross, ESASold, ESAType, ESAPolicy, CLGross, CLSold, CLType," & _
     "CLPolicy, AHGross, AHSold, AHType, AHPolicy, ETCHGross, ETCHSold, ETCHPolicy, GAPGross, GAPSold," & _
     "GAPType, GAPPolicy, IDENSold, IDENGross, EnviroShieldGross, EnviroPolicy, SpitzerCare, OtherGross," & _
     "OtherCount , PVR, Make, Spitzer300, LeaseTerm" & _
    "FROM NewDailySales" & _
    "WHERE DSKey = " & RecordNo & ";"
    
    DoCmd.RunSQL strSQL
    

Exit_butMoveNDStoBO_Click:
    Exit Sub

Err_butMoveNDStoBO_Click:
    MsgBox Err.Description
    Resume Exit_butMoveNDStoBO_Click
    
End Sub

The error message I get is "Line1:Incorrect Syntax near 'DSKey'."

 

[pbaldy]

I don't use ADP's, but you're certainly going to have a problem with spaces there...or more specifically, the lack of them (like "FROM NewDailySalesWHERE DSKey...").

 

[pbaldy]

BTW, the way to execute an SP with a parameter is:

Exec NameOfSP 'Parameter1', 'Parameter2'

 

[OtheymAverian]

Thank you for pointing out the lack of spaces so quickly.... i added in the line breaks (" & _) at the end of each line...

So from my original code I could do something like?

Private Sub butMoveNDStoBO_Click()
On Error GoTo Err_butMoveNDStoBO_Click

    Dim RecNo As Long
    Dim SQL As String
    
    RecNo = InputBox(Prompt:="Enter Record Number")
    SQL = "EXEC usp_MoveNDS_to_BO"
    
    DoCmd.RunSQL SQL 'RecNo'
    

Exit_butMoveNDStoBO_Click:
    Exit Sub

Err_butMoveNDStoBO_Click:
    MsgBox Err.Description
    Resume Exit_butMoveNDStoBO_Click
    
End Sub

 

[OtheymAverian]

Putting spaces at the end of my lines fixed everything!! thank you, can't believe i caused such a blunder... part of the learning process i guess.

 

[pbaldy]

No, more like:

SQL = "EXEC usp_MoveNDS_to_BO '" & RecNo & "'"

DoCmd.RunSQL SQL

 

[pbaldy]

Sorry, started typing my last post then got a phone call. Looks like you've got it sorted. That mistake with the spaces is a common one that we all make when we start out.