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

  • Thread starter Thread starter Jack G
  • Start date Start date
J

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 said:
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
Click to expand...

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
 
Thanks, Rick. That works great.

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

Jack

Rick Brandt said:
Jack said:
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
Click to expand...

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
Click to expand...
 
Back
Top