Solved

Sending Multiselect listbox selections to query

Posted on 2008-06-13
8
691 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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