Just can't see what's WRONG with this SQL statement??

[Jack G]

I'm trying to set up a continuous form such that clicking on the heading
will sort on that field. I set up the following code for the click event:

Private Sub ProjectNumberHeading_Click()
Dim strSQL As String

strSQL = "SELECT MainQuery.[ProjectNumber], MainQuery.[ClientName],
MainQuery.[ProjectName] " & _
"FROM MainQuery " & _
"ORDER BY MainQuery.[ProjectNumber];"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
ProjectNumberHeading.FontBold = True
End Sub

But it doesn't work - the error message keeps saying:

"Run-time error '2342':
A RunSQL action requires an argument consisting of an SQL statement"

I've checked, double-checked and triple-checked, and can't see what's wrong.
I've printed the value of strSQL into the Immediate Window, pasted it into
the SQL view of a query, and it works fine that way. Can anyone see my
stupid mistake?

Thanks,
Jack

 

[Jack G]

Thanks, Rick. That works great.

(I like to try to make everything I do as difficult as possible.)

Jack

I'm trying to set up a continuous form such that clicking on the
heading will sort on that field. I set up the following code for the
click event:
Private Sub ProjectNumberHeading_Click()
Dim strSQL As String

strSQL = "SELECT MainQuery.[ProjectNumber], MainQuery.[ClientName],
MainQuery.[ProjectName] " & _
"FROM MainQuery " & _
"ORDER BY MainQuery.[ProjectNumber];"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
ProjectNumberHeading.FontBold = True
End Sub

But it doesn't work - the error message keeps saying:

"Run-time error '2342':
A RunSQL action requires an argument consisting of an SQL statement"

I've checked, double-checked and triple-checked, and can't see what's
wrong. I've printed the value of strSQL into the Immediate Window,
pasted it into the SQL view of a query, and it works fine that way.
Can anyone see my stupid mistake?

Thanks,
Jack

RunSQL is for APPEND, UPDATE, or DELETE queries only. Even if RunSQL
actually did do something with your SQL statement that would not affect
the display on your form (why would it?).

All you need to do is manipulate the OrderBy property of your form.

Me.OrderBy = "[ProjectNumber]"
Me.OrderByOn = True

or

Me.OrderBy = "[ProjectNumber] DESC"
Me.OrderByOn = True