Searching a table to find a certain ID: error 80040e10 no value for one or more parameters

In Access 2007, I am searching a table to find a certain ID. The ID address is given in a text box by the user IDTxt.

I get error 80040e10 no value for one or more parameters.

Private Sub Search_Click()

 'here a new part I'm not sure about...
Dim cn As ADODB.Connection
Dim rsQ As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rsQ = New ADODB.Recordset

Dim strSQL As String

Dim ID开发者_开发百科_number As Integer
Dim blnExists As Boolean
blnExists = False
ID_number = IDTxt.Value

strSQL = "SELECT * FROM Table1  WHERE ID = ID_number;"
rsQ.Open strSQL, cn, adOpenStatic

If rsQ.RecordCount <> 0 Then
     ' Found it
    blnExists = True
    MsgBox "found"
Else
    MsgBox "not found"
End If

End Sub

---

This should be:

rSQL = "SELECT * FROM Table1 WHERE ID = " & ID_number

[Note: it would be better practice to use a parameterised query to protect against SQL injection.]

---

Your passing a string without substituting the value;

strSQL = "SELECT * FROM Table1  WHERE ID = ID_number;"

needs to be

strSQL = "SELECT * FROM Table1  WHERE ID = " & ID_number

as ID_number is only in the context of a VBA variable outside the string.

(Your also not performing type checking on ID_number so text in an unrestricted textbox could error, and for string parameters is an injection vulnerability)

Also note that RecordCount can return -1 depending on cursor location/type.

---

Suggestion: use a Command object to supply the parameter value e.g.

Dim cn As ADODB.Connection
Dim rsQ As ADODB.Recordset

Set cn = .ActiveConnection

Set rsQ = New ADODB.Recordset

Dim strSQL As String

Dim ID_number As Integer
Dim blnExists As Boolean
blnExists = False
ID_number = IDTxt.Value

strSQL = "SELECT * FROM Table1  WHERE ID = ID_number;"

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = cn
    .NamedParameters = True
    .CommandType = adCmdText
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("ID_number", adInteger, , , ID_number)
    Set rsQ = .Execute
End With

If rsQ.RecordCount <> 0 Then
     ' Found it
    blnExists = True
    MsgBox "found"
Else
    MsgBox "not found"
End If