How to dynamicly use a text box to query in vba a excel

Question

i want to use a textbox to dynamically change the query

What am i doing wrong?

Set rs = conn.Execute("SELECT vfrhma000 *from vfrhma000 where maenume = textbox1.value")

Answer 1

You are sending textbox1.value as search criteria. Everything within the quotes is left untouched by VBA.

If you want to get the content of the textbox, write it like this:

Set rs = conn.Execute("SELECT * from vfrhma000 where maenume = " & textbox1.value)

Now, the content of the textbox will be read by the VBA runtime and concatenated to your SQL statement.

If you are searching for a string, you will need to put (single) quotes around your search string:

Set rs = conn.Execute("SELECT * from vfrhma000 where maenume = '" & textbox1.value & "'")

In any case, you should be aware that if the textbox is used to get the search criteria from the user, you are vulnerable to SQL injection. Unless you are just playing around, you should take that serious. Have a look to https://stackoverflow.com/a/49509616/7599798 to get an idea of how to use parameters to a query.

Update If the sql statement gets more complicated, it's a good idea to write it in a variable before executing it. If it fails, you can dump the content to the immediate window, check if it looks okay and try to execute it directly against the database (new query for access, SQL*Plus or SQL developer for Oracle, SQL Management Studio for SQL Server ...)

Dim sql as string, searchValue as string
searchValue = Worksheets("sheet1").Range("e9").Value
sql = "SELECT * from vfrhma000 where maenomi = '%" & searchValue & "%'"
Debug.Print sql
Set rs = conn.Execute(searchValue)

In your case, maybe you have to change the = into a like. Also, you may want to check if cell E9 contains a valid search term.