Control query code with a text box

[David McKnight]

I'm have problems with code below as when I run it I get a 3061 error "Too
few parameters. Expect 1." I believe it is choking on the Me.txtValueof in
the WHERE clause.

What am I missing?


Private Sub Command14_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb()

' Load querydef if it exists; create it if it doesn't.
On Error Resume Next ' disable error-handling
Set qdf = db.QueryDefs("query3")
On Error GoTo Err_Handler ' establish error-handling
If qdf Is Nothing Then
Set qdf = db.CreateQueryDef("query3")
End If

' Set the query's SQL
qdf.SQL = _
"SELECT Table1.Yearof, Table1.Dateof, " & _
"Table1.Nameof, Table1.Valueof FROM Table1 " & _
"INNER JOIN Table5 " & _
"ON Table1.Yearof = Table5.Yearof " & _
"WHERE (((Table1.Valueof)= Me.txtValueof));"

strSql = "DELETE * FROM [query3 DB]"
db.Execute strSql, dbFailOnError
strSql = "INSERT INTO [query3 DB] SELECT [query3].* FROM [query3]"
db.Execute strSql, dbFailOnError

Exit_Point:
Set qdf = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point



End Sub

 

[Allen Browne]

Concatenate teh value into the string, e.g.:
"WHERE Table1.Valueof = " & Me.txtValueof & ";"

If ValuleOf is a Text field (not a Number field), you need extra quotes:
"WHERE Table1.Valueof = """ & Me.txtValueof & """;"