Passing Parameters to SQL (1 Viewer)

[Angelflower]

I have a stored procedure that inserts a new line to a table when pressed from a button on the form. The code behind the form is grabbing the Utility_read_ID as a variable and passing that to the stored procedure via a Pass-Through query.

The code below seems to work with the exception that I keep getting a Run-time error ‘3219’ invalid operation yet the new line is added to the table as expected. The error is on the Set rstAddRead = qdf.OpenRecordset(). If I take this line out of the code no new line is added to the table but if I keep it then I get the desired results. What can I do to make this line happy and not throw the error? Tks!

Code behind the button:
Private Sub cmd_AddRead_Click()
'Declare needed variables and collections
Dim db As DAO.Database
Dim rstAddRead As DAO.Recordset
Dim strUtility_read_ID As Integer
Dim strAddRead As String
Dim qdf As QueryDef

'Assign values to variables based on parameter selections on the main menu.
strUtility_read_ID = [Forms]![electricity_reading]![utility_read_ID]


'Build SQL string to pass into procAddReading based on parameters.
strAddRead = "EXEC procAddReading " & "'" & strUtility_read_ID & "'"

' Set database variable to current database.
Set db = CurrentDb

'Open QueryDef object.
Set qdf = db.QueryDefs("cmd_procAddReading")

'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strAddRead

'Allow the stored procedure to return a result set to be bound.
qdf.ReturnsRecords = False

Debug.Print strAddRead

'Open Recordset object as a query def.
Set rstAddRead = qdf.OpenRecordset()

This is what the Debug window says for the debug.PrintstrAddRead
EXEC procAddReading '66'


This is the Stored Procedure:
USE [Utilities]
GO
/****** Object: StoredProcedure [dbo].[procAddReading] Script Date: 08/19/2013 07:48:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 08/15/2013
-- Description: Add new line to the utility_reading
-- =============================================
ALTER PROCEDURE [dbo].[procAddReading]
-- Add the parameters for the stored procedure here
@utility_read_ID int = 0

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.


SET NOCOUNT ON;

-- Insert statements for procedure here

INSERT INTO [Utilities].[dbo].[utility_reading]
([utility_read_ID]
,[transaction_date]
,[from_reading_date]
,[to_reading_date]
,[current_reading]
,[prior_reading]
,[usage_provided]
,[usage_calculated]
,[reading_provided]
,[divisor]
,[multiplier]
,[reading_to_standard]
,[calc_type]
,[notes])

SELECT utility_reading.utility_read_ID
, GETDATE () as transaciton_date
, dateadd(M,+1,max(from_reading_date)) as from_reading_date
, dateadd(day,-1,dateadd(M,+2,max(from_reading_date))) as to_reading_date
, '' as current_reading
, '' as prior_reading
, '' as usage_provided
, '' as usage_calculated
, '' as reading_provided
, utility_reading.divisor
, utility_reading.multiplier
, '' as reading_to_standard
, utility_reading.calc_type
, '' as notes


FROM utility_reading

WHERE utility_reading.utility_read_ID = @utility_read_ID

group by utility_read_ID, divisor, multiplier, calc_type

order by utility_read_ID asc



END

 

[Angelflower]

This is what I ended up doing and it seems to work:

Private Sub cmd_AddRead_Click()
'Declare needed variables and collections
Dim db As DAO.Database
Dim strUtility_read_ID As Integer
Dim strAddRead As String
Dim qdf As QueryDef

'Assign values to variables based on parameter selections on the main menu.
strUtility_read_ID = [Forms]![electricity_reading]![utility_read_ID]

DoCmd.SetWarnings False

'Build SQL string to pass into procAddReading based on parameters.
strAddRead = "EXEC procAddReading " & "'" & strUtility_read_ID & "'"

' Set database variable to current database.
Set db = CurrentDb

'Open QueryDef object.
Set qdf = db.QueryDefs("cmd_procAddReading")

'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strAddRead

'Allow the stored procedure to return a result set to be bound.
qdf.ReturnsRecords = False

DoCmd.OpenQuery "cmd_procAddReading"

Refresh

End Sub