Re: Passing arguments into a code based query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 [Table].

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 [Table] " & _
"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 [Table] " & _
"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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Ray" <Ray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C5CCC90A-6337-4BA0-934E-332C8728E2B7@xxxxxxxxxxxxxxxx
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 [Table] 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


.

Quantcast