Vincent Labrecque Vincent Labrecque - 6 months ago 46
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!


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