Solved

Sending Multiselect listbox selections to query

Posted on 2008-06-13
8
678 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now