List box to set q parameter

D

Danny

Can some one pls help with the following error message

"Invalid SQL statement expected 'delete' 'insert' 'procedure' or insert'

The code is as follows



Thanks
Danny

strSQL = "Sub CoCode.* FROM CONS Selection Q "
strWhere = "Where Sub CoCode IN( "
For i = 0 To CoCode_List_Box.ListCount - 1
If CoCode_List_Box.Selected(i) Then
strWhere = strWhere & "'" & CoCode_List_Box.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub
 
V

Van T. Dinh

*

strSQL = "Sub CoCode.* FROM CONS Selection Q "

What is the "Sub" at the start of the String? Did you mean "SELECT"?

* You should also explain "CONS Selection Q" also because I can see that
this is the next error you have when you open the Query.

* You use the qualifier CoCode in "CoCode.*" which means that CoCode must be
in the FROM clause but I couldn't see it in your SQL???

Check the JET SQL Reference section of Access Help, especially the keyword
SELECT and the examples for using SELECT.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top