Result of strSQL in VBA (Access 2003)

Question

I'm making a form to add some records to my database & I've some problem. If user fill the field called 'Product' I'd like to inform him by making visible one picture that this product is in database. (I just want to inform him - not to ban option of adding)

I've this code, and unfortunately it give me an error (Run-time error no operator)

Private Sub ProductText_LostFocus()
Dim cnn as New ADODB.Connection
Dim strSQL as String
Dim rs as New ADODB.Recordset
Set cnn = CurrentProject.Connection
strSQL = "Select Product FROM Shopping WHERE Product = " & ProductText.Value
Set rs = cnn.Execute(strSQL)
If rs.RecordCount > 0 then
ShowPicture.Visible = True
End if
End sub

What I do wrong?

If "Pruduct" is not numeric, then the value needs to be enclosed in single-quotes. `...WHERE Product = '" & ProductText.Value & "'"` — Tim Williams, Nov 09 '11 at 01:05
OK, Thanks it doesn't message any errors, but I don't see the effect - the picture is still not visible (no matter if product is in database or not) and for example color of TextField doesn't change too red. What's wrong?! — ChrisParker, Nov 09 '11 at 01:23
Test for rs.EOF not Recordcount. If there's no match then rs.EOF will be True. — Tim Williams, Nov 09 '11 at 04:30
Don't assume Access (ACE, Jet, whatever) is immune to [SQL injection](http://stackoverflow.com/questions/512174/non-web-sql-injection/). — onedaywhen, Nov 09 '11 at 08:54

score 1 · Answer 1 · edited May 23 '17 at 11:57

If you use a Command object with strongly-typed Parameter objects, you will not have to worry about including or escaping quotes in your parameter values, plus you get immunity from SQL injection.

Here I've simplified your code somewhat and have assumed your Product column is NVARCHAR(255) i.e. variable-width Unicode (without compression) text:

Private Sub ProductText_LostFocus()
  Dim cmd As New ADODB.Command
  With cmd
    Set .ActiveConnection = CurrentProject.Connection
    .CommandText = "Select Product FROM Shopping WHERE Product = ?;"
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter(, adVarWChar, , 255, ProductText.Value)
    If Not .Execute().EOF Then
      ShowPicture.Visible = True
    End If
  End With
End Sub

competent_tech · Answer 2 · 2011-11-09T16:25:44.017

0

You have a couple of different issues.

First, don't create new instances of cnn and rs since they will either be set or created later:

Dim cnn as ADODB.Connection
Dim strSQL as String
Dim rs as ADODB.Recordset

Second, you need to enclose the product in quotes in the SQL (you may need to do additional work if producttext contains single quotes):

strSQL = "Select Product FROM Shopping WHERE Product = '" & ProductText.Value & "'"

edited Nov 09 '11 at 16:25

answered Nov 09 '11 at 02:37

competent_tech

44,465
11
90
113

2

`OpenRecordset` is a DAO method. For ADO, `Execute` should work. But I'm reluctant to edit your answer to correct this because I think your advice about handling quotes is not correct i.e. they should be using a `Command` object with `Parameter` objects, allowing the OLE DB provider escape quotes, etc. Don't assume Access (ACE, Jet, whatever) is immune to [SQL injection](http://stackoverflow.com/questions/512174/non-web-sql-injection/). – onedaywhen Nov 09 '11 at 08:59
Sorry, I had a flashback to vb6 days when I saw recordset. I have corrected the answer. And I totally agree about the command object recommendation and SQL injection, but I was just trying to answer the specific question without correcting all of the issues with the code (they also should have exception handling, should close the recordset on completion, and should really change the query such that it returns a single value (such as count(1)) so that they can use the adExecuteRecord option to return a Record instead of a Recordset). – competent_tech Nov 09 '11 at 16:33
Good point about using `COUNT()` and error handling but I can't seem to use `adExecuteRecord` option to return a Record: with the Command object I get the error, "Object or provider is not capable of performing requested operation" and with the Connection object I get, "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." – onedaywhen Nov 10 '11 at 08:39

Result of strSQL in VBA (Access 2003)

2 Answers2