Vincent Labrecque Vincent Labrecque - 3 months ago 16
SQL Question

Why do I get an error code in this VBA for Access code?

I'm trying to make a form in Access where I will have a "search as you type" function to find a customer, and then I'll double click the correct match in a listbox in order to update the customer info textboxes. I succeeded the "search as you type" part, but not the listbox double click update form part.

I keep getting the following RunSQL error: "A RunSQL statement requires an argument consisting of an SQL statement"

The thing is that the SQL statement works just fine in a query.

Here's the code:

Private Sub lstClient_DblClick(Cancel As Integer)
Dim selectedItem, strSQL, strSQL1 As String
Dim i As Integer

i = lstClient.ListIndex
selectedItem = lstClient.ItemData(i)
txtCustName.Value = selectedItem

strSQL = "SELECT tblClient.[Addresse] FROM tblClient WHERE tblClient.[Nom] ='" & selectedItem & "';"

strSQL1 = "SELECT tblClient.[Addresse] FROM tblClient WHERE tblClient.[Nom] ='Bernache Funeral home';"

DoCmd.RunSQL (strSQL1)
Text191.Value = strSQL1
End Sub


The second statement (strSQL1) is the one I tried in the query in order to make sure it worked, and it did.

Thanks in advance!

Answer

Try something like

Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL1)
Text191.Value = rs.Fields("Addresse").Value

Set rs = Nothing
Set db = Nothing