Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sending Multiselect listbox selections to query

Posted on 2008-06-13
8
Medium Priority
?
719 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 10

Assisted Solution

by:therealmongoose
therealmongoose earned 340 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

610 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