Need Help with SQL in Code

[mattc66 via AccessMonster.com]

I am not sure how to do this. Below is my code and it's not working. I am
setting the double click event on the Name1 feild. When they double click I
am wanting to store the data from the Name, Group and Code. Then incorporate
that in an SQL statement to display the results. Can I get some help on how
to do this.

Thanks

Private Sub NAME1_DblClick(Cancel As Integer)

Dim stGroup As String
Dim stName As String
Dim stCode As String
Dim strSQL As String

stGroup = Me.Group1
stName = Me.NAME1
stCode = Me.CODE

strSQL = _
"Select * From ICSTOCK_C1.* " & _
"WHERE (((ICSTOCK_C1.stGroup)="stName"))"

DoCmd.RunSQL strSQL


End Sub

 

[Allen Browne]

There are several things to think about here, Matt.

As you probably found, you can't RunSQL on a SELECT query (i.e. that's for
action queries only.) You can OpenQuery in a saved query, but that means
saving your SQL statement (or perhaps assigning it to the SQL property of a
QueryDef you saved for this purpose.)

In the end, queries are not the interface in Access, so you are probably
better off creating a form to show the search results. The form can be in
Continuous View or Datasheet view if you like. In fact, you could display
the results in the very form you are using to get the user's input. Just
filter it so it matches whatever the user typed.

You also need to consider what you want to do if the user left some boxes
blank. For example, if they entered a Name1, but not a CODE, do you want to
show all the ones matching the name matches anyway? The code:
stGroup = Me.CODE
will fail (invalid use of Null) if the control is null. And, of course, you
have to design the WHERE clause so it ignores the boxes left null.

So, if you want to build a form where the user can enter several values to
match, and you filter the results, here's the low-down on how it's done:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Download the example.
Pull it apart to see how to build the filter string.
Good luck.