MultiSelect Listbox

I've a MultiSelect listbox (named : lbLocation)in my form (named:101 :
Inventories). After selecting the items in this listbox and click the
RunQuery command button, the following code will be executed :

Private Sub RunQuery_Click()
Dim frm As Form, ctl As Control
Dim varLoc As Variant
Dim strSQL As String
Set frm = Forms![101 : Inventories]
Set ctl = frm!lbLocation
strSQL = "Select * from [101 : Inventories] where Location="
For Each varLoc In ctl.ItemsSelected
strSQL = strSQL & "'" & ctl.ItemData(varLoc) & "' OR Location="
Next varLoc
strSQL = Left$(strSQL, Len(strSQL) - 13)
DoCmd.RunSQL strSQL
End Sub

But, it stops at "DoCmd.RunSQL strSQL" and prompts following error message :
Run-time error ‘2342’
A RunSQL action requires an argument consisting of an SQL statement.

Please help..

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

As the error is trying to tell you, you cannot use RunSQL with a Select
query: it only works with Action queries (Update, Insert Into, Delete)

What are you hoping will happen when you run the Select query?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Letica via AccessMonster.com" u48292@uwe wrote in message
news:9ddd8ac633b2c@uwe...
I've a MultiSelect listbox (named : lbLocation)in my form (named:101 :
Inventories). After selecting the items in this listbox and click the
RunQuery command button, the following code will be executed :

Private Sub RunQuery_Click()
Dim frm As Form, ctl As Control
Dim varLoc As Variant
Dim strSQL As String
Set frm = Forms![101 : Inventories]
Set ctl = frm!lbLocation
strSQL = "Select * from [101 : Inventories] where Location="
For Each varLoc In ctl.ItemsSelected
strSQL = strSQL & "'" & ctl.ItemData(varLoc) & "' OR Location="
Next varLoc
strSQL = Left$(strSQL, Len(strSQL) - 13)
DoCmd.RunSQL strSQL
End Sub

But, it stops at "DoCmd.RunSQL strSQL" and prompts following error message
:
Run-time error '2342'
A RunSQL action requires an argument consisting of an SQL statement.

Please help..

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

Hi,

It will list out the inventories in the location/s selected by the user in
the MultiSelect listbox.

Douglas J. Steele wrote:
As the error is trying to tell you, you cannot use RunSQL with a Select
query: it only works with Action queries (Update, Insert Into, Delete)

What are you hoping will happen when you run the Select query?

I've a MultiSelect listbox (named : lbLocation)in my form (named:101 :
Inventories). After selecting the items in this listbox and click the
[quoted text clipped - 20 lines]

Please help..

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

List where? Are you hoping to display a recordset? It's generally frowned
upon to allow users to interact directly with tables or queries: you should
always use a form.

If you don't care about that advice, you can always create a query (look up
CreateQueryDef) with the SQL you've generated and execute it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Letica via AccessMonster.com" u48292@uwe wrote in message
news:9de667d1b2f5e@uwe...
Hi,

It will list out the inventories in the location/s selected by the user in
the MultiSelect listbox.

Douglas J. Steele wrote:
As the error is trying to tell you, you cannot use RunSQL with a Select
query: it only works with Action queries (Update, Insert Into, Delete)

What are you hoping will happen when you run the Select query?

I've a MultiSelect listbox (named : lbLocation)in my form (named:101 :
Inventories). After selecting the items in this listbox and click the
[quoted text clipped - 20 lines]

Please help..

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

The users do not interact directly with tables or queries.

The MultiSelect listbox in the form. User can select the locations in this
box. After selecting the select items and clicking the "RunQuery" command
button, the code will be executed, the query (named: 101 Inventories) will
run and result will be displayed.



Douglas J. Steele wrote:
List where? Are you hoping to display a recordset? It's generally frowned
upon to allow users to interact directly with tables or queries: you should
always use a form.

If you don't care about that advice, you can always create a query (look up
CreateQueryDef) with the SQL you've generated and execute it.

Hi,

[quoted text clipped - 11 lines]

Please help..

--
Message posted via http://www.accessmonster.com

Displayed for what purpose, though? If all you care about is a read-only
list, why not use the query as the RecordSource for a report? Then, they
can print it if they want to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Letica via AccessMonster.com" u48292@uwe wrote in message
news:9df17ae89b671@uwe...
The users do not interact directly with tables or queries.

The MultiSelect listbox in the form. User can select the locations in
this
box. After selecting the select items and clicking the "RunQuery"
command
button, the code will be executed, the query (named: 101 Inventories) will
run and result will be displayed.



Douglas J. Steele wrote:
List where? Are you hoping to display a recordset? It's generally frowned
upon to allow users to interact directly with tables or queries: you
should
always use a form.

If you don't care about that advice, you can always create a query (look
up
CreateQueryDef) with the SQL you've generated and execute it.

Hi,

[quoted text clipped - 11 lines]

Please help..

--
Message posted via http://www.accessmonster.com