Solved

Sending Multiselect listbox selections to query

Posted on 2008-06-13
8
696 Views
Last Modified: 2013-11-27
I have tried implementing the code provided in the following solution for multiselect listbox:
http://www.experts-exchange.com/Database/Miscellaneous/Q_21989025.html

My code is attached to the After Update property of the listbox, & looks like the following:
Private Sub List10_AfterUpdate()
Dim stritems As String
For Each var In Me!List10!ItemsSelected
  stritems = stritems & "," & Me!List10!Columns(0, var)
Next
End Sub

According to the solution, this should produce "a comma separated list of all items" selected in the listbox.  However, when I run the code I get an MS Access code that it can't execute the code in design mode.
Should the VBA code work, would the selected items be passed to a query field as the following criteria:
Forms!Drilldown.List10_AfterUpdate
0
Comment
Question by:BBRRGG
  • 4
  • 3
8 Comments
 
LVL 10

Assisted Solution

by:therealmongoose
therealmongoose earned 85 total points
ID: 21784510
The code should work however not if the form is in design mode - you'll need to go into form view to test....

To use the string as a query criteria, you'll probably need to rebuild the queries sql rather than passing the string as below - note strItems has been declared outside the update event and the code to build and open the sql statement put into a separate event for a command button - not sure how you want to use this, but you may not want the sql adjusted every time the user selcts something on the list...



Option Compare Database
 
Dim stritems As String
 
Private Sub List10_AfterUpdate()
 
 
For Each var In Me!List10!ItemsSelected
  stritems = stritems & "'" & Me!List10!Columns(0, var) & "' or "
Next
'removes the last "or " from the string
stritems = Mid(stritems, 1, Len(stritems) - 3)
 
End Sub
 
Private Sub command1_click()
 
Dim dbs As Database
Dim qdf As QueryDef
Dim strsql As String
 
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryYourQueryName")
 
strsql = "Select * from yourtablename where yourtablename.criteriafield = " & stritems & ";"
qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing
 
DoCmd.OpenQuery "qryYourQueryName"
 
 
End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:frankytee
ID: 21784526
a comma separated list will confuse sql that it's a list of fields.
where abouts in the query are you trying to add this list? i'm assuming its in the where clause.
if so then it should be used in the "IN" section of the WHERE clause
for eg
dim sql as string
your current code....
'then
sql  = "select .... from .... where whateverfield IN (" & stritems & ")"
etc
0
 

Author Comment

by:BBRRGG
ID: 21787226
Thank you much for your feedback.  TheRealMongoose: I gave it a shot with the code you provided, however received a Run-time Error 451 when I tried to make a selection in the listbox ("Property let procedure not defined and property get procedure did not return an obect.")  When I pressed the button associated with the new code you added, it launched the query but with no records, even though there were records highlighted in the listbox (I'm guessing because stritems was never assigned anything with the runtime error?)  
Thanks for any additional suggestions you may be able to provide.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 19

Expert Comment

by:frankytee
ID: 21788290
as you have a comma delimited string,
strsql = "Select * from yourtablename where yourtablename.criteriafield = " & stritems & ";"
will not work.
the equal (=) sign is only valid for a single value and not valid for a comma delimited string which has many values.
you need to use the IN clause as per my previous post.
strsql = "Select * from yourtablename where yourtablename.criteriafield IN (" & stritems & ")"

this assumes your comma delimited string contains numeric values, for eg:
1,2,3,4 etc
if it contains alpha or date values then you each value needs to be enclosed with single quotes ' for alpha, and # for date

so this part of your current code
Dim stritems As String
For Each var In Me!List10!ItemsSelected
  stritems = stritems & "," & Me!List10!Columns(0, var)
Next

need to change to (if alpha field):
Dim stritems As String
For Each var In Me!List10!ItemsSelected
  stritems = stritems & ",'" & Me!List10!Columns(0, var) & "'"
Next
'clean up leading ","
if left(stritems,1) = "," then
 stritems = trim(mid(stritems ,2,len(stritems)))
end if

or if the field is a date, need to change to :
For Each var In Me!List10!ItemsSelected
  stritems = stritems & ",#" & Me!List10!Columns(0, var) & "#"
Next
'clean up leading ","
if left(stritems,1) = "," then
 stritems = trim(mid(stritems ,2,len(stritems)))
end if

--------------------------------------------------------------------------------------------------------------------------
if you use the = sign then the field itself must be specified with each OR
ie .... where myfield = 'a' or myfield = 'b' or myfield = 'c' etc

generally the IN clause will execute more quickly than using the multiple OR.
0
 

Author Comment

by:BBRRGG
ID: 21790197
Thank you much franytee.  I 've made your suggested changes but when I select records in the listbox I still get an error 451 on the line:
For Each var In Me!List10!ItemsSelected

When I remove the Me! in this line, I receive a 'type mismatch' error message on the same line.  My listbox contains lines of text that can be multiple words long (ie. up to 15 words long).  Thanks again for all of your helpful suggestions!
0
 
LVL 19

Accepted Solution

by:
frankytee earned 90 total points
ID: 21791337
>My listbox contains lines of text that can be multiple words long (ie. up to 15 words long).
do the individual entries in your list box contain comma's? if so then its going to be really messy as they will confuse access/sql.

try replacing Me!List10!ItemsSelected
with
Me.List10.ItemsSelected

also at the end of your FOR loop type:
debug.print "stritems = "  & stritems
and post the result to see what your string is.

also after strsql = "Select * from yourtablename where yourtablename.criteriafield IN (" & stritems & ")"
type
debug.print "strsql = "  & strsql
and post that as well
0
 

Author Comment

by:BBRRGG
ID: 21793846
Yes there are multiple commas within each record in the listbox.  

I've changed Me!List10!Columns to Me.List10.Columns, and added your debug.print commands but unfortunately it does not make it to these lines.  Rather I'm getting "Compile error: method or data member not found" where the debugger points to Private Sub List10_AfterUpdate() in yellow, & the cursor highlights "Columns" in Me.List10.Columns.

If the multiple commas is a new issue, please reply "new issue" and I'll award points for this question, reissue a new question, and post a link to the new question here.  Thanks!
0
 

Author Closing Comment

by:BBRRGG
ID: 31467135
Thanks, please see this post for a link to related question about listbox records with commas.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question