Passing variable into SQL statement

[Caryh]

Hi! I have a public function in my VB app that when called looks at the value of a cbo box which is a customers name and searches a record set to find the matching record for that customer and enter there email address into another txt box. The problem is if I try to pass the customer name into the where part of the SQL string as a string variable it gives me an error "Run-time-error 3061 Too few parameters Expected.1" If I type in the customer name it works fine. The following is the code:

Public Sub GetEmail()

Dim db As Database
'This is the object that will hold the connection
'to our database

Dim rs As Recordset
'This is the object that will hold a set of
'records coming back from the database

Dim SQLString As String
'This is just to temporarily hold the SQL string

Dim customer As String
'This is temporary to hold the customer name

Set db = OpenDatabase("c:\madisonhd.mdb&quot

customer = cboReportedBY.Text

SQLString = "SELECT * From Customer_Info WHERE Customer_Info.Customer_name = customer ;"

Set rs = db.OpenRecordset(SQLString)

txtCustEmail.Text = rs.Fields("custemail&quot

rs.Close
'Close the Recordset

db.Close
'Close the Database

End Sub


If I run it as above I get the error, If I specify the customer name like:

SQLString = "SELECT * From Customer_Info WHERE Customer_Info.Customer_name = 'Bob Jones' ;"

It runs correctly populating the txt box with the persons email address. I'm sure it's something simple, please advise.

 

[scking]

Very simply;

SQLString = "SELECT * From Customer_Info WHERE Customer_Info.Customer_name = '" & customer & "';"

The put a trap on the next line by selecting it an hitting {F9}. Inspect the value of the string viable in the debug/immediate window by entering:

?SQLString
SQLString = "SELECT * From Customer_Info WHERE Customer_Info.Customer_name = 'Bob Jones';"

And this is what it should show. Enter {F5} to continue the function.

Steve King

Steve King
Growth follows a healthy professional curiosity

 

[Caryh]

Steve you rock! sorry to be such a newby, routing and switching is more my thing.

 

[ETID]

Try this........

SQLString = "SELECT * From Customer_Info WHERE Customer_Info.Customer_name = " + customer + " ;"