Passing arguments into a code based query

[Ray]

I have a control button on an Access form that calls a macro in a Module:
---
Private Sub Command33_Click()
CreateDocs
End Sub
---
Sub CreateDocs()
Dim objConn As New Connection
Dim objRS As New Recordset

objConn.ConnectionString = c_DBCon
objConn.Open
objRS.Open c_SQL, objConn
'Remainder of subroutine
---
c_DBCon and c_SQL are constant strings that contain datasource info and a
query respectively. c_SQL Looks like:
Const c_SQL = "SELECT * FROM

WHERE hesainst = [Text52] "
with [Text52] referring to the content of a text box on the form. When
clicking the control, I get teh error "Invalid column name 'Text52'." I
didn't think I was asking for a colmn name, but a particular record. Any
idaes what it wrong?

The code works fine if i replace [Text52] with the string I'm trying to look
up, but the string will change frequently.

Thanks for any help you can offer.

Ray

 

[Douglas J. Steele]

I don't believe you can refer to form controls in SQL strings in ADO. And
even if you could, your reference is incorrect: you'd need to use
Forms!NameOfForm!Text52 rather that [Text52]. What's happening is that it's
interpretting [Text52] as being a field in

.

Try:

Sub CreateDocs()
Dim objConn As Connection
Dim objRS As Recordset
Dim strSQL As String

Set objConn = New Connection
objConn.ConnectionString = c_DBCon
objConn.Open
Set objRS = New Recordset
strSQL = "SELECT * FROM

" & _
"WHERE hesainst = " & Me.[Text52]
objRS.Open c_SQL, objConn
'Remainder of subroutine

This assumes hesainst is a numeric field. If it's text, use:

strSQL = "SELECT * FROM

" & _
"WHERE hesainst = " & _
Chr$(34) & Me.[Text52] & Chr$(34)

Note that I've changed the declarations for objConn and objRS. Using the New
keyword in the declaration like you were isn't actually a good idea in VBA.