By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,599 Members | 1,326 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,599 IT Pros & Developers. It's quick & easy.

list box and parameters

rcollins
100+
P: 234
I need my List box to carry over selected items to a report. Here is what I have
Expand|Select|Wrap|Line Numbers
  1. ([Forms]![CommentCriteria]![List2])
but this doesnt work. In fact with this, I get an empty report. Need help please
Jun 23 '09 #1

✓ answered by NeoPa

Sometimes, a bit of code can paint a thousand words. From this at least I can follow where you need a little more direction.

DoCmd.OpenReport has a parameter that you haven't used, called WhereCondition (not to be confused with FilterName). This is the fourth parameter. Parameters can also be passed by name if preferred :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.OpenReport(ReportName:=stDocName, _
  2.                       View:=acPreview, _
  3.                       WhereCondition:=strWhere)
Before the call is made you need to translate the various items in your ListBox, using ItemsSelected (in a loop) to produce a string formatted like a SQL WHERE clause but without the "WHERE " part.
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2. Dim varX As Variant
  3.  
  4. With Me.List0
  5.     For Each varX In .ItemsSelected
  6.         strWhere = strWhere & "," & .ItemData(varX)
  7.     Next varX
  8.     strWhere = "[SurveyCode] In(" & Mid(strWhere,2) & ")"
  9.     Call DoCmd.OpenReport(ReportName:=stDocName, _
  10.                           View:=acPreview, _
  11.                           WhereCondition:=strWhere)
  12. End With
From your posted SQL, your problem is that ItemsSelected is not a string value so would be entirely unusable in SQL directly. My earlier code illustrates how a SQL string should be set up as it works on very similar lines. Also, as the SQL that the report is built on is encapsulated within the report object itself, any change to the SQL would have to be entered as the property .RecordSource of the report.

Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,709
There isn't really much of a question here to work with. It would help a lot if you expressed more clearly what you're attempting and what you hope to achieve.

I will take a stab that you have a Multi-Select ListBox control on a form and you want only those records associated with the selected items to be included in some report or other.

With so little info to work with I can only suggest a solution in very general terms of course :
Use VBA to build up a filter (SQL WHERE clause without the word WHERE) of only the selected items. Use the .ItemsSelected property of the ListBox control to identify the required items.

When you have prepared the filter string pass it as the WhereCondition parameter to the OpenReport() function you use to open the report.
Jun 26 '09 #2

rcollins
100+
P: 234
ok, I think i am a little confused. I thought I could put something in the query when the report opened that would look at what I selected from the list box. This is how I do my combobox. I have List0 for the list of items (which is already set as multple select) and the frield I am using in the report OPComments is SurveyCode. So my understanding from you is I would put it before:
Expand|Select|Wrap|Line Numbers
  1.     stDocName = "OPComments"
  2.     DoCmd.OpenReport stDocName, acPreview
  3.  
Here is what I am trying and It doesnt work. Do I have some syntax wrong?
Expand|Select|Wrap|Line Numbers
  1. select OPComments.SurveyCode where OPComments.SurveyCode = List0.ItemsSelected
Jun 26 '09 #3

NeoPa
Expert Mod 15k+
P: 31,709
@rcollins
Let me start by saying that ignoring my two comments about :
  1. Using the CODE tags.
  2. Posting in terms that make sense.
is not a great way to proceed.

As a long time member I'm trying not to slap an infraction onto your account, but you make this harder than it should be. When responding to a thread I prefer not to have to read up the whole thread again to work out what you're talking about.

What am I supposed to make of that (quoted)? Which query? Which report?

I will look at getting some answers for you, but please be more careful in future as this is frankly irritating (and totally unnecessary).
Jun 28 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
Sometimes, a bit of code can paint a thousand words. From this at least I can follow where you need a little more direction.

DoCmd.OpenReport has a parameter that you haven't used, called WhereCondition (not to be confused with FilterName). This is the fourth parameter. Parameters can also be passed by name if preferred :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.OpenReport(ReportName:=stDocName, _
  2.                       View:=acPreview, _
  3.                       WhereCondition:=strWhere)
Before the call is made you need to translate the various items in your ListBox, using ItemsSelected (in a loop) to produce a string formatted like a SQL WHERE clause but without the "WHERE " part.
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2. Dim varX As Variant
  3.  
  4. With Me.List0
  5.     For Each varX In .ItemsSelected
  6.         strWhere = strWhere & "," & .ItemData(varX)
  7.     Next varX
  8.     strWhere = "[SurveyCode] In(" & Mid(strWhere,2) & ")"
  9.     Call DoCmd.OpenReport(ReportName:=stDocName, _
  10.                           View:=acPreview, _
  11.                           WhereCondition:=strWhere)
  12. End With
From your posted SQL, your problem is that ItemsSelected is not a string value so would be entirely unusable in SQL directly. My earlier code illustrates how a SQL string should be set up as it works on very similar lines. Also, as the SQL that the report is built on is encapsulated within the report object itself, any change to the SQL would have to be entered as the property .RecordSource of the report.
Jun 28 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
As a last comment, that code assumes the [SurveyCode] field is numeric (I had to guess as this fundamental information was not available). If it turns out that this is a textual field instead, then simply replace line #6 in the posted code with :
Expand|Select|Wrap|Line Numbers
  1. strWhere = strWhere & ",'" & .ItemData(varX) & "'"
Notice the only difference here is the single-quotes around the values. This tells the SQL engine to treat the contents as a textual value rather than a numeric or date/time one.
Jun 28 '09 #6

NeoPa
Expert Mod 15k+
P: 31,709
A new (related) question was posted in here which has been split off as ListBox Problem.
Sep 10 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.