Access asking for a query parameter unnecessarily? (Access VBA and SQL)

[NeoPa]

To make this Injection-proof, simply add the following code after line #5 :

Dim strCheck As String

If Replace(Replace(Forms!frmMain![txtSearch], _
                   """", _
                   ""), _
           "'", _
           "") <> Forms!frmMain![txtSearch] Then
    'Handle SQL Injection here
End If

PS. Alternatively, you may decide simply to strip out any quote characters and pass that string along, instead of advertising that your code is on to them ;)

[patjones]

Thanks to the both of you for addressing my issue. I will absorb this overnight, and let you know how it works out!

Pat

[ChipR]

An alternative is the Instr function.

If Instr(Forms!frmMain![txtSearch], """") > 0 Or _
   Instr(Forms!frmMain![txtSearch], "'") > 0 Then

[patjones]

Thank you all. Things work quite well.

ADezii -

I put all the code in the Click event for the command button as you suggested, and removed the parameter formalism. I also really like the tests for existence of qdf and rst at the end of the sub and have added those as well.

NeoPa -

I like the Replace method, but am simply assigning those nested Replace statements to a string variable and then putting that into the SQL string rather than doing the test.

I appreciate the time and effort. Thanks so much.

[NeoPa]

It's always a pleasure working with someone who's prepared to get involved Pat. Responding so fully, as you do, keeps everyone in the picture and everyone feeling valued.

BTW I hope you agree with me that ADezii's post #15 is the most apposite for the question (as I've selected it).

[patjones]

ADezii's code is basically what I implemented and consider the best solution.

I'm still not sure why the parameter didn't work though. I usually use unbound forms, pulling the data from the tables with ADO and then assigning text box values item by item referencing the recordset. I've found that ADO makes use of parameters straightfoward.

Thanks!

[ChipR]

I recommend you try using bound forms the next chance you get. Being a programmer before a database administrator, I started with unbound forms and lots of code, but have learned enough to use bound forms. Funny how the "easy" way was not as easy as just writing code. Anyway, the result is much cleaner and faster, and I have redone most of my forms.

[patjones]

Indeed, I am using a bound form for this project...reall y my first time using one. It does seem to cut down on the code quite a bit. It also makes edits really easy.

Thanks for your insight Chip.