MultiSelect Listbox

[Letica via AccessMonster.com]

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..

 

[Douglas J. Steele]

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?

 

[Letica via AccessMonster.com]

Hi,

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

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..

 

[Douglas J. Steele]

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

(no e-mails, please!)

Hi,

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

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..

 

[Letica via AccessMonster.com]

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.

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]

 

[Douglas J. Steele]

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

(no e-mails, please!)

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.

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..