Why do I receive no value error message, when parameters do have v

[WoollyBear]

I have constructed the following piece of code, triggered from a command
button. Despite several attempts the following error message is displayed,
"No value given for one or more required parameters". I have debugged the
SELECT statement and used the Immediate window to verify that the two
parameter fields do contain values. Does anybody have any ideas please?

' Declare the ADO variable for the tbl_Referral_Data recordset
Dim rstbl_RefSEN As New ADODB.Recordset
' Declare the tbl_Referral_Data SQL statement variable
Dim RefSENSQLStmt As String
' Declare variable for closed referral indicator and set to "Y"
Dim ref_ClosedInd As String
ref_ClosedInd = "Y"
' Select the required data from tbl_Referral_Data and display the SEN data
on the form
RefSENSQLStmt = "SELECT * FROM tbl_Referral_Data WHERE
Child_ID = " & temp_ChildID & " AND Ref_Completed_Ind =" & ref_ClosedInd
rstbl_RefSEN.Open RefSENSQLStmt,
CurrentProject.Connection, adOpenDynamic, adLockReadOnly
If Not rstbl_RefSEN.EOF Then

 

[Dirk Goldgar]

I have constructed the following piece of code, triggered from a command
button. Despite several attempts the following error message is displayed,
"No value given for one or more required parameters". I have debugged the
SELECT statement and used the Immediate window to verify that the two
parameter fields do contain values. Does anybody have any ideas please?

' Declare the ADO variable for the tbl_Referral_Data recordset
Dim rstbl_RefSEN As New ADODB.Recordset
' Declare the tbl_Referral_Data SQL statement variable
Dim RefSENSQLStmt As String
' Declare variable for closed referral indicator and set to "Y"
Dim ref_ClosedInd As String
ref_ClosedInd = "Y"
' Select the required data from tbl_Referral_Data and display the SEN data
on the form
RefSENSQLStmt = "SELECT * FROM tbl_Referral_Data WHERE
Child_ID = " & temp_ChildID & " AND Ref_Completed_Ind =" & ref_ClosedInd
rstbl_RefSEN.Open RefSENSQLStmt,
CurrentProject.Connection, adOpenDynamic, adLockReadOnly
If Not rstbl_RefSEN.EOF Then

I don't know what data type Child_ID is, but Ref_Completed_Ind appears to be
a text field -- at least, you're trying to supply a text value for it.
Therefore, that value has to be enclosed in quotes within the SQL statement.
Try this:

RefSENSQLStmt = _
"SELECT * FROM tbl_Referral_Data WHERE Child_ID = " & _
temp_ChildID & _
" AND Ref_Completed_Ind ='" & ref_ClosedInd & "'"

It may not be obvious, but I've included single-quotes (') around the value
from ref_ClosedInd. If Child_ID also happens to be a text field, you'd need
to do something similar for temp_ChildID, too.

 

[WoollyBear]

Dirk,

You are a star! The Child_ID was no problem as it is integer. Many thanks
for this, it was something I was totally unaware of.