query programming noobie question

[Pierre]

I'm farely new with Access although a "moderately power user "in VBA for
Excell.

I have a form from which, when I change the value of the "target group"
(drop down list) I want the results to display all the records matching the
group, in decrementing order of date.

the SQL query I came up with is:

Private Sub Target_Group_Change()

Dim SQLstr As String

SQLstr = "SELECT * FROM [tbl BW Group Tuning]" _
& " WHERE [tbl BW Group Tuning].GroupID = """ & Me![Target Group] & """" _
& " ORDER BY [tbl BW Group Tuning].[Mod Date] DESC;"

'DoCmd.RunSQL SQLstr

End Sub

It works fine but... it does nothing. If I put a watch on "[tbl
BW...].GroupID", I get an error "Access can not find the '|' field required
in your expression".

I think I should have some kind of link to the table but I looked everywhere
in the docs I have and couldn't find the answer. I tried to play with the
recordset thing but I just can't figure it out and I get "missing operator"
or "syntax error", etc.
Thanks.
Pierre.

 

[John Spencer]

The question is where do you assign the SQL string you have built.

To the form you are currently on.
Me.RecordSource = SQLstr

Or to a listbox on the form
Me.lstBox.RowSource = SQLstr

Or do you want to open a query with that string as its SQL? Not a good
choice, you should not work directly in queries. And if that was the case I
would simply design a query and give it parameter referencing the control on
the form to filter the records. Then use

DoCmd.OpenQuery "MyTuningQuery"

The SQL for the saved query would look like
SELECT * FROM [Tbl BW Group Tuning]
WHERE GroupID = [FORMS]![NameOfYourForm]![Target_Group_Change]
ORDER BY [tbl BW Group Tuning].[Mod Date] DESC

If you changed the value in your form, you would need to close the query and
reopen it or force a requery

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County