Solved

Sending Multiselect listbox selections to query

Posted on 2008-06-13
8
668 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

758 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

22 Experts available now in Live!

Get 1:1 Help Now