Multiple Listbox selectin and query criteria

[Robbro]

I've read many of the posts here about using multiple list box selections to
filter queries yet I still cant get it to work. I'll post my understanding
then my code (which I am totally clueless about) and see which is at fault.
My table is called "sales", my query is "sales summary Query", my filter
form is " Filter Report", my listbox is "customerselect" and set to simple
multi select

I then have a hidden control named "customerselect2" with on click set to
event procedure, then in the procedure I've pasted the code from

http://www.mvps.org/access/forms/frm0007.htm

and tried to modify it to my needs as below

Private Sub customerselect2_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form![Filter Report]
Set ctl = frm!customerselect
strSQL = "Select * from Sales where [Customer]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [Customer]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))

End Sub

Which I'm sure I've botched in some way.
In my query then I've put "In ([Forms]![Filter Report]![customerselect2])"
in the criteria under customer. When I click my button to run my report its
filtered down to nothing. I should add that I have finally successfully
added filters from this same form for date and plant selection which do
successfully work, so I have the basic process down, the multi-list box thing
is just too complicated so far for me.
Let me know if I need to provide more information.

Thanks

 

[Robbro]

Getting an error at the rowsource for the listbox. I have no CustomerID. Is
that something I would have to add???

Whats originally in my listbox that successfully lists every customer just
once is:

SELECT sales.Customer FROM sales GROUP BY sales.Customer;

To restrict the report’s underlying query by means of a parameter you’d need
to assign only the string expression for the WHERE clause to the hidden
control on the form, not the entire SQL statement, for which you'd need
additional code, and then reference the text box as a parameter. But I’d
suggest that you filter the report itself when opening it from the button on
your form.

To do this set up the list box like this:

For its RowSource property:

SELECT CustomerID, Customer FROM Customers ORDER BY Customer;

For other properties:

Name: customerselect
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
MultiSelect: Simple or Extended as preferred.

Add a button to the form to open the report, called rptCustomers in this
example, with the following in its Click event procedure:

Const MESSAGETEXT = _
“No customers selected. Open report for all customers?â€
Dim varItem As Variant
Dim strCustomerIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.customerselect

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCustomerIDList = strCustomerIDList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strCustomerIDList = Mid(strCustomerIDList, 2)

strCriteria = "[CustomerID] In(" & strCustomerIDList & ")"

DoCmd.OpenReport "rptCustomers", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
If MsgBox(MESSAGETEXT, vbYesNo + vbQuestion, "Warning") = vbYes Then
DoCmd.OpenReport "YourReportName", _
View:=acViewPreview
End If

If you are using other controls on the from as parameters to restrict the
report’s query these will still work when opening the report via this button;
the filtering of the report by customer will be complementary to the
restriction in the query.

Ken Sheridan
Stafford, England

I've read many of the posts here about using multiple list box selections to
filter queries yet I still cant get it to work. I'll post my understanding
then my code (which I am totally clueless about) and see which is at fault.
My table is called "sales", my query is "sales summary Query", my filter
form is " Filter Report", my listbox is "customerselect" and set to simple
multi select

I then have a hidden control named "customerselect2" with on click set to
event procedure, then in the procedure I've pasted the code from

http://www.mvps.org/access/forms/frm0007.htm

and tried to modify it to my needs as below

Private Sub customerselect2_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form![Filter Report]
Set ctl = frm!customerselect
strSQL = "Select * from Sales where [Customer]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [Customer]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))

End Sub

Which I'm sure I've botched in some way.
In my query then I've put "In ([Forms]![Filter Report]![customerselect2])"
in the criteria under customer. When I click my button to run my report its
filtered down to nothing. I should add that I have finally successfully
added filters from this same form for date and plant selection which do
successfully work, so I have the basic process down, the multi-list box thing
is just too complicated so far for me.
Let me know if I need to provide more information.

Thanks

--



.

 

[Robbro]

Ok, I've tried adding a unique id# to each customer on a small sample to see
if I could get this to work.

First I've set up everything just as you said, and named it accordingly,
then copy/paste into the on click event area, but whenI click the button I
get

"The expression On Click you entered as the event property setting produced
the following error: Expected: end of statement"

I suspect it may have to do with formatting on this forum, as I know it
split ctrl.ItemData(varItem) into 2 rows and made them red, meaning error I
guess. I put them back together and now I think the error is somewhere in
Const MESSAGETEXT = _
"No customers selected. Open report for all customers?"
as when I change anything in that area then move the cursor away it
highlights customers and gives me "Compile error: Expected: end of
statement". I cant come up with how to fix this apparently....


Second if I delete the parts related to the warning above and just try to
make it work without that, changing absolutely nothing else I get
"The expression On Click you entered as the evne property setting produced
the following error: Invalid outside procedure."

Getting an error at the rowsource for the listbox. I have no CustomerID. Is
that something I would have to add???

Whats originally in my listbox that successfully lists every customer just
once is:

SELECT sales.Customer FROM sales GROUP BY sales.Customer;

To restrict the report’s underlying query by means of a parameter you’d need
to assign only the string expression for the WHERE clause to the hidden
control on the form, not the entire SQL statement, for which you'd need
additional code, and then reference the text box as a parameter. But I’d
suggest that you filter the report itself when opening it from the button on
your form.

To do this set up the list box like this:

For its RowSource property:

SELECT CustomerID, Customer FROM Customers ORDER BY Customer;

For other properties:

Name: customerselect
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
MultiSelect: Simple or Extended as preferred.

Add a button to the form to open the report, called rptCustomers in this
example, with the following in its Click event procedure:

Const MESSAGETEXT = _
“No customers selected. Open report for all customers?â€
Dim varItem As Variant
Dim strCustomerIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.customerselect

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCustomerIDList = strCustomerIDList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strCustomerIDList = Mid(strCustomerIDList, 2)

strCriteria = "[CustomerID] In(" & strCustomerIDList & ")"

DoCmd.OpenReport "rptCustomers", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
If MsgBox(MESSAGETEXT, vbYesNo + vbQuestion, "Warning") = vbYes Then
DoCmd.OpenReport "YourReportName", _
View:=acViewPreview
End If

If you are using other controls on the from as parameters to restrict the
report’s query these will still work when opening the report via this button;
the filtering of the report by customer will be complementary to the
restriction in the query.

Ken Sheridan
Stafford, England

I've read many of the posts here about using multiple list box selections to
filter queries yet I still cant get it to work. I'll post my understanding
then my code (which I am totally clueless about) and see which is at fault.
My table is called "sales", my query is "sales summary Query", my filter
form is " Filter Report", my listbox is "customerselect" and set to simple
multi select

I then have a hidden control named "customerselect2" with on click set to
event procedure, then in the procedure I've pasted the code from

http://www.mvps.org/access/forms/frm0007.htm

and tried to modify it to my needs as below

Private Sub customerselect2_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form![Filter Report]
Set ctl = frm!customerselect
strSQL = "Select * from Sales where [Customer]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [Customer]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))

End Sub

Which I'm sure I've botched in some way.
In my query then I've put "In ([Forms]![Filter Report]![customerselect2])"
in the criteria under customer. When I click my button to run my report its
filtered down to nothing. I should add that I have finally successfully
added filters from this same form for date and plant selection which do
successfully work, so I have the basic process down, the multi-list box thing
is just too complicated so far for me.
Let me know if I need to provide more information.

Thanks

--



.

 

[Robbro]

Tried setting the listbox up as 1 column and using only names and pasting
this, still getting the same "invalid outside procedure" error.
I feel like I'm close, I just dont have the knowledge to troubleshoot these
most likely minor errors.....

Its better to have a unique numeric column as a key rather than a name as the
latter can be duplicated. You can simply add an autonumber CustomerID column
to the table as its primary key, and make sure its also included in the
report’s query. You don’t need to show it in the report, however.

If you do use the Customer name as the key then amend the code as follows to
wrap the text values in quotes characters when the value list is built:

Const MESSAGETEXT = _
“No customers selected. Open report for all customers?â€
Dim varItem As Variant
Dim strCustomerIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.customerselect

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCustomerIDList = strCustomerIDList & _
",â€â€" & ctrl.ItemData (varItem) & “â€â€â€
Next varItem

' remove leading comma
strCustomerIDList = Mid(strCustomerIDList, 2)

strCriteria = "[CustomerID] In(" & strCustomerIDList & ")"

DoCmd.OpenReport "rptCustomers", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
If MsgBox(MESSAGETEXT, vbYesNo + vbQuestion, "Warning") = vbYes Then
DoCmd.OpenReport "YourReportName", _
View:=acViewPreview
End If

Ken Sheridan
Stafford, England

Getting an error at the rowsource for the listbox. I have no CustomerID. Is
that something I would have to add???

Whats originally in my listbox that successfully lists every customer just
once is:

SELECT sales.Customer FROM sales GROUP BY sales.Customer;

To restrict the report’s underlying query by means of a parameter you’d need
to assign only the string expression for the WHERE clause to the hidden

[quoted text clipped - 100 lines]

Thanks

--



.

 

[Robbro]

Ok, I kept getting errors, but I think I have finally got it. Part of my
problem was the spaces in my query and report names, which I read were a
no-no, so I removed them, then I had some errors due to the quotations and
finally got it flowing with :

Private Sub rptCustomers_Click()


Dim varItem As Variant
Dim strCustomerIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.customerselect

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCustomerIDList = strCustomerIDList & _
"," & Chr(34) & ctrl.ItemData(varItem) & Chr(34)
Next varItem

' remove leading comma
strCustomerIDList = Mid(strCustomerIDList, 2)

strCriteria = "[Customer] In(" & strCustomerIDList & ")"

DoCmd.OpenReport "salessummaryReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else

DoCmd.OpenReport "salessummaryReport", _
View:=acViewPreview
End If





End Sub


Now I never could get the Const MESSAGETEXT part to work, It always wanted
to give me an error on the word customer...... Are there supposed to be
parenthesis or somethign around that or double quotes????

 

[Robbro]

I've went with no message and by default, if nothing is chosen, then all
customers show up, which will normally be the case.

Anyway this report is looking great! Thanks for all your help, kinda
surprised I got it to work with basically no knowledge
The blind copy/pasting the knowledgable!

I’ve no idea why you had problems with the MESSAGETEXT constant declaration.
The quotes characters simply delimit the literal string in the usual way,
just the same as if you assign a literal string to a variable. Its merely
assigning it to a constant in this case. If you want the message try just
using the literal string as the MsgBox function’s argument rather than a
constant:

If MsgBox(“No customers selected. Open report for all customers?â€, vbYesNo +
vbQuestion, "Warning") = vbYes Then

This should be entered as single line of course.

Ken Sheridan
Stafford, England

Ok, I kept getting errors, but I think I have finally got it. Part of my
problem was the spaces in my query and report names, which I read were a
no-no, so I removed them, then I had some errors due to the quotations and
finally got it flowing with :

Private Sub rptCustomers_Click()


Dim varItem As Variant
Dim strCustomerIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.customerselect

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCustomerIDList = strCustomerIDList & _
"," & Chr(34) & ctrl.ItemData(varItem) & Chr(34)
Next varItem

' remove leading comma
strCustomerIDList = Mid(strCustomerIDList, 2)

strCriteria = "[Customer] In(" & strCustomerIDList & ")"

DoCmd.OpenReport "salessummaryReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else

DoCmd.OpenReport "salessummaryReport", _
View:=acViewPreview
End If

End Sub

Now I never could get the Const MESSAGETEXT part to work, It always wanted
to give me an error on the word customer...... Are there supposed to be
parenthesis or somethign around that or double quotes????

--



.

 

[Soboths]

Hello Robbro & Ken,
I am trying to get the multi select list box to work just like Robbro. I
have follow the conversation and the explanation from both of you which
really helpful to me. But I get confused and seem to miss understanding with
the where clause below:
Dim strCustomerIDList As string
strCustomerIDList = strCustomerIDList & "," & ctrl.ItemData (varItem)
Do I need to have a field call CustomerIDList? or where do I get this field
from?

Your help is much appreciated

I've went with no message and by default, if nothing is chosen, then all
customers show up, which will normally be the case.

Anyway this report is looking great! Thanks for all your help, kinda
surprised I got it to work with basically no knowledge
The blind copy/pasting the knowledgable!

I’ve no idea why you had problems with the MESSAGETEXT constant declaration.
The quotes characters simply delimit the literal string in the usual way,
just the same as if you assign a literal string to a variable. Its merely
assigning it to a constant in this case. If you want the message try just
using the literal string as the MsgBox function’s argument rather than a
constant:

If MsgBox(“No customers selected. Open report for all customers?â€, vbYesNo +
vbQuestion, "Warning") = vbYes Then

This should be entered as single line of course.

Ken Sheridan
Stafford, England

Ok, I kept getting errors, but I think I have finally got it. Part of my
problem was the spaces in my query and report names, which I read were a
no-no, so I removed them, then I had some errors due to the quotations and
finally got it flowing with :

Private Sub rptCustomers_Click()


Dim varItem As Variant
Dim strCustomerIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.customerselect

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCustomerIDList = strCustomerIDList & _
"," & Chr(34) & ctrl.ItemData(varItem) & Chr(34)
Next varItem

' remove leading comma
strCustomerIDList = Mid(strCustomerIDList, 2)

strCriteria = "[Customer] In(" & strCustomerIDList & ")"

DoCmd.OpenReport "salessummaryReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else

DoCmd.OpenReport "salessummaryReport", _
View:=acViewPreview
End If

End Sub

Now I never could get the Const MESSAGETEXT part to work, It always wanted
to give me an error on the word customer...... Are there supposed to be
parenthesis or somethign around that or double quotes????

--



.

 

[Soboths]

Thank you for your help in detailing explanation. You are the best. I will
put the code together and run it.

Soboths

strCustomerIDList is a variable which stores a comma-separated value list of
values, in this case customer IDs, but they can be anything and you can call
the variable anything you wish. The way the code works is that it loops
through all the items selected in the list box and builds the string,
assigning each item one by one to the variable with the line:

strCustomerIDList = strCustomerIDList & "," & ctrl.ItemData (varItem)

This assumes that the values are of a number data type, but if they are a
text data type each value needs to be wrapped in quotes characters when the
string is built, so it would be:

strCustomerIDList = strCustomerIDList & ",â€â€" & ctrl.ItemData (varItem) &
“â€â€â€

In this the pairs of contiguous quotes characters within each literal string
delimited by quotes is interpreted as a literal quotes character.

If you still have problems post back with details of the table and fields,
including their data types, from which the list box draws its list, along
with the name of the report or form which you want to open filtered to the
selections.

Ken Sheridan
Stafford, England

Hello Robbro & Ken,
I am trying to get the multi select list box to work just like Robbro. I
have follow the conversation and the explanation from both of you which
really helpful to me. But I get confused and seem to miss understanding with
the where clause below:
Dim strCustomerIDList As string
strCustomerIDList = strCustomerIDList & "," & ctrl.ItemData (varItem)
Do I need to have a field call CustomerIDList? or where do I get this field
from?

Your help is much appreciated

I've went with no message and by default, if nothing is chosen, then all
customers show up, which will normally be the case.

[quoted text clipped - 58 lines]

to give me an error on the word customer...... Are there supposed to be
parenthesis or somethign around that or double quotes????

--



.

 

[Soboths]

Ken,

the code seems to run without error but it return with empty data set. I'm
running a qry that pull all the transactions by the stock location that based
on user's selection from a form as a set of criteria passing to the qry.
Please see below:
------
Private Sub RunQryTest_Button_Click()

Dim varItem As Variant
Dim strSLocationList As String
Dim strCriteria As String
Dim ctrl As Control
Dim stDocName As String

Set ctrl = Me.List44

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected

strSLocationList = strSLocationList & ",â€â€" & ctrl.ItemData(varItem)
& “â€â€â€


Next varItem

strCriteria = "[TBL_SLocation] In(" & strSLocationList & ")"


stDocName = "Qry_TBL_Transactions_By_Date"
DoCmd.OpenQuery stDocName, acNormal, acEdit




End If


End Sub

-----
Thanks

Soboths

Thank you for your help in detailing explanation. You are the best. I will
put the code together and run it.

Soboths

strCustomerIDList is a variable which stores a comma-separated value list of
values, in this case customer IDs, but they can be anything and you can call
the variable anything you wish. The way the code works is that it loops
through all the items selected in the list box and builds the string,
assigning each item one by one to the variable with the line:

strCustomerIDList = strCustomerIDList & "," & ctrl.ItemData (varItem)

This assumes that the values are of a number data type, but if they are a
text data type each value needs to be wrapped in quotes characters when the
string is built, so it would be:

strCustomerIDList = strCustomerIDList & ",â€â€" & ctrl.ItemData (varItem) &
“â€â€â€

In this the pairs of contiguous quotes characters within each literal string
delimited by quotes is interpreted as a literal quotes character.

If you still have problems post back with details of the table and fields,
including their data types, from which the list box draws its list, along
with the name of the report or form which you want to open filtered to the
selections.

Ken Sheridan
Stafford, England

Hello Robbro & Ken,
I am trying to get the multi select list box to work just like Robbro. I
have follow the conversation and the explanation from both of you which
really helpful to me. But I get confused and seem to miss understanding with
the where clause below:
Dim strCustomerIDList As string
strCustomerIDList = strCustomerIDList & "," & ctrl.ItemData (varItem)
Do I need to have a field call CustomerIDList? or where do I get this field
from?

Your help is much appreciated

I've went with no message and by default, if nothing is chosen, then all
customers show up, which will normally be the case.
[quoted text clipped - 58 lines]
to give me an error on the word customer...... Are there supposed to be
parenthesis or somethign around that or double quotes????

--



.

 

[Soboths]

I will try again with your suggestion. Thanks again.

You can't pass filter a query in this way. You'll need to create a form or
report based on the query and then open the form or report from the code.
You can use a form in continuous forms or datasheet view to show multiple
rows, or in single form view to show one record at a time.

So first take out any parameter which references the list box from the query.
You can still reference other controls on the form if necessary, such as text
boxes or combo boxes, but a multi-select list box cannot be referenced
directly by a query. There are ways of doing it via a hidden text box in the
form as an intermediary control, but I generally filter a form or report. If
you do want to investigate how to do it directly in a query take a look at:

http://support.microsoft.com/kb/100131/en-us

Having created a form or report bound to the query you need to open the form
or report in the code by first removing the lines:

stDocName = "Qry_TBL_Transactions_By_Date"
DoCmd.OpenQuery stDocName, acNormal, acEdit

And inserting a line such as the following to open a report in print preview:

DoCmd.OpenReport "YourReportName", View:=acViewPreview, WhereCondition:
=strCriteria

Or for a form:

DoCmd.OpenForm "YourFormName", WhereCondition:=strCriteria

BTW I noticed some 'smart quotes' had crept into the code. I think this
probably originates from me using Word to draft my replies, and currently
using a different machine from my usual one on which smart quotes are turned
off. If you paste the code with the smart quotes in it into the VBA window
it might cause a problem, so be sure to change any to normal quotes.

Ken Sheridan
Stafford, England

Ken,

the code seems to run without error but it return with empty data set. I'm
running a qry that pull all the transactions by the stock location that based
on user's selection from a form as a set of criteria passing to the qry.
Please see below:
------
Private Sub RunQryTest_Button_Click()

Dim varItem As Variant
Dim strSLocationList As String
Dim strCriteria As String
Dim ctrl As Control
Dim stDocName As String

Set ctrl = Me.List44

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected

strSLocationList = strSLocationList & ",""" & ctrl.ItemData(varItem)
& """"


Next varItem

strCriteria = "[TBL_SLocation] In(" & strSLocationList & ")"


stDocName = "Qry_TBL_Transactions_By_Date"
DoCmd.OpenQuery stDocName, acNormal, acEdit



End If


End Sub

-----
Thanks

Soboths

Thank you for your help in detailing explanation. You are the best. I will
put the code together and run it.

[quoted text clipped - 44 lines]

to give me an error on the word customer...... Are there supposed to be
parenthesis or somethign around that or double quotes????

--



.

 

[Soboths]

I do not quite sure to understand the logics behind the code. My table is
called "TBL_Transaction_All", my query is "Qry_TBL_Transactions_By_Date", my
filter form is "Receipts_Selection", my list box is "SLocation" and set to
simple multi select. On my “Receipts_Selection†form I have transaction date
box, transaction type and stock location for users to select. On the form, I
have a button call “Submit†for users to run the
“qry_TBL_Transactions_By_Date†after all selections have been made and then
user can export the result as an excel format which all filtered from
“Receipts_Selection†form. On query, that selection will be the parameters to
Qry_TBL_Transactions_By_Date. This would work just fine when the list box set
to none, but then when I set list box to simple multi select the result is
empty. Please help.

You can't pass filter a query in this way. You'll need to create a form or
report based on the query and then open the form or report from the code.
You can use a form in continuous forms or datasheet view to show multiple
rows, or in single form view to show one record at a time.

So first take out any parameter which references the list box from the query.
You can still reference other controls on the form if necessary, such as text
boxes or combo boxes, but a multi-select list box cannot be referenced
directly by a query. There are ways of doing it via a hidden text box in the
form as an intermediary control, but I generally filter a form or report. If
you do want to investigate how to do it directly in a query take a look at:

http://support.microsoft.com/kb/100131/en-us

Having created a form or report bound to the query you need to open the form
or report in the code by first removing the lines:

stDocName = "Qry_TBL_Transactions_By_Date"
DoCmd.OpenQuery stDocName, acNormal, acEdit

And inserting a line such as the following to open a report in print preview:

DoCmd.OpenReport "YourReportName", View:=acViewPreview, WhereCondition:
=strCriteria

Or for a form:

DoCmd.OpenForm "YourFormName", WhereCondition:=strCriteria

BTW I noticed some 'smart quotes' had crept into the code. I think this
probably originates from me using Word to draft my replies, and currently
using a different machine from my usual one on which smart quotes are turned
off. If you paste the code with the smart quotes in it into the VBA window
it might cause a problem, so be sure to change any to normal quotes.

Ken Sheridan
Stafford, England

Ken,

the code seems to run without error but it return with empty data set. I'm
running a qry that pull all the transactions by the stock location that based
on user's selection from a form as a set of criteria passing to the qry.
Please see below:
------
Private Sub RunQryTest_Button_Click()

Dim varItem As Variant
Dim strSLocationList As String
Dim strCriteria As String
Dim ctrl As Control
Dim stDocName As String

Set ctrl = Me.List44

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected

strSLocationList = strSLocationList & ",""" & ctrl.ItemData(varItem)
& """"


Next varItem

strCriteria = "[TBL_SLocation] In(" & strSLocationList & ")"


stDocName = "Qry_TBL_Transactions_By_Date"
DoCmd.OpenQuery stDocName, acNormal, acEdit



End If


End Sub

-----
Thanks

Soboths

Thank you for your help in detailing explanation. You are the best. I will
put the code together and run it.

[quoted text clipped - 44 lines]

to give me an error on the word customer...... Are there supposed to be
parenthesis or somethign around that or double quotes????

--



.

 

[Soboths]

I will try with your suggestions
Thank you again Ken.

As I said in my last post, you cannot refernce a multi-select list box as a
parameter in a query. A multi-select list box has no value; it has an
ItemsSelected collection which is a collection of variants which reference
each selected row. You can filter a form or report in the way I described,
but to use the values selected in a multi-select list box to restrict a query
you need a more complex approach. First add an unbound text box,
txtSLocationList, to the form and set its Visible property to False (No).
Then amend your code as follows:

Dim varItem As Variant
Dim strSLocationList As String
Dim ctrl As Control
Dim stDocName As String

Set ctrl = Me.List44

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSLocationList = strSLocationList & _
",""" & ctrl.ItemData(varItem) & """"
Next varItem

strSLocationList = Mid(strSLocationList,2)

Me.txtSLocationList = strSLocationList

stDocName = "Qry_TBL_Transactions_By_Date"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If

You now need to include a couple of functions in a standard module. These
are the InParam function and GetToken function from method 2 at the following
site:

http://support.microsoft.com/kb/100131/en-us

In your query, in query design view, remove the parameter which refernces the
list box (leave the other parameters in place), and in the 'field' row of a
blank column enter:

InParam([SLocation],Forms!Receipts_Selection!txtSLocationList)

I've assumed the column is named SLocation for this example. Uncheck the
'show' check box for the column and in the 'criteria' row enter:

True

BTW the above method is more reliaable than the simpler method 1 at the above
site as method 1 can return specious rows if the value in a field is a
substring of one of the values sought.

Ken Sheridan
Stafford, England

I do not quite sure to understand the logics behind the code. My table is
called "TBL_Transaction_All", my query is "Qry_TBL_Transactions_By_Date", my
filter form is "Receipts_Selection", my list box is "SLocation" and set to
simple multi select. On my “Receipts_Selection†form I have transaction date
box, transaction type and stock location for users to select. On the form, I
have a button call “Submit†for users to run the
“qry_TBL_Transactions_By_Date†after all selections have been made and then
user can export the result as an excel format which all filtered from
“Receipts_Selection†form. On query, that selection will be the parameters to
Qry_TBL_Transactions_By_Date. This would work just fine when the list box set
to none, but then when I set list box to simple multi select the result is
empty. Please help.

You can't pass filter a query in this way. You'll need to create a form or
report based on the query and then open the form or report from the code.

[quoted text clipped - 83 lines]

to give me an error on the word customer...... Are there supposed to be
parenthesis or somethign around that or double quotes????

--



.