Multiple Listbox selectin and query criteria

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
0
Utf
3/17/2010 3:28:01 PM
access 16762 articles. 3 followers. Follow

18 Replies
1164 Views

Similar Articles

[PageSpeed] 29

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

Robbro wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
KenSheridan
3/17/2010 4:46:35 PM
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; 


"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Robbro wrote:
> >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
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> 
> .
> 
0
Utf
3/17/2010 5:40:03 PM
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

Robbro wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
KenSheridan
3/17/2010 6:15:40 PM
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."

"Robbro" wrote:

> 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; 
> 
> 
> "KenSheridan via AccessMonster.com" wrote:
> 
> > 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
> > 
> > Robbro wrote:
> > >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
> > 
> > -- 
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> > 
> > .
> > 
0
Utf
3/17/2010 6:33:01 PM
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.....

"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Robbro wrote:
> >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
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> 
> .
> 
0
Utf
3/17/2010 7:19:02 PM
The insertion of unwanted line breaks is something you have to watch out for
in newsgroups, which is why I split the constant declaration over two lines
by means of the underscore continuation character:

Const MESSAGETEXT = _
     "No customers selected.  Open report for all customers?"

This should be entered in the procedure as two lines, pressing the Enter key
at the end of the first line after the underscore character.  It is
interpreted as a single line of code, however.

The error sounds more like the code has not been entered correctly in the
event procedure, however.  There are a number of ways, but one is as follows:

1.  Select the button in form design view.

2.  Select its On Click event procedure in its properties sheet.

3.  Click the ‘build’ button; that’s the one on the right with 3 dots.

4.  Select ‘Code Builder’

5.  Add the code between the two following lines:

Private Sub customerselect2_Click()

And 

End Sub

Ken Sheridan
Stafford, England

Robbro wrote:
>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???
>[quoted text clipped - 108 lines]
>> > >
>> > >Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
KenSheridan
3/17/2010 9:07:55 PM
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????
0
Utf
3/18/2010 12:56:01 PM
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

Robbro wrote:
>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????

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
KenSheridan
3/18/2010 1:26:10 PM
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!

"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Robbro wrote:
> >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????
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> 
> .
> 
0
Utf
3/18/2010 3:15:03 PM
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

"Robbro" wrote:

> 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!
> 
> "KenSheridan via AccessMonster.com" wrote:
> 
> > 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
> > 
> > Robbro wrote:
> > >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????
> > 
> > -- 
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> > 
> > .
> > 
0
Utf
3/20/2010 6:00:01 AM
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

Soboths wrote:
>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????

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
KenSheridan
3/20/2010 11:49:42 AM
Thank you for your help in detailing explanation. You are the best. I will 
put the code together and run it.

Soboths
"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Soboths wrote:
> >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????
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> 
> .
> 
0
Utf
3/20/2010 3:37:01 PM
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


"Soboths" wrote:

> Thank you for your help in detailing explanation. You are the best. I will 
> put the code together and run it.
> 
> Soboths
> "KenSheridan via AccessMonster.com" wrote:
> 
> > 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
> > 
> > Soboths wrote:
> > >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????
> > 
> > -- 
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> > 
> > .
> > 
0
Utf
3/20/2010 4:51:01 PM
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

Soboths wrote:
>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????

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
KenSheridan
3/20/2010 6:35:18 PM
I will try again with your suggestion. Thanks again.

"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Soboths wrote:
> >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????
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> 
> .
> 
0
Utf
3/21/2010 4:31:01 PM
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.

"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Soboths wrote:
> >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????
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> 
> .
> 
0
Utf
3/21/2010 10:12:01 PM
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

Soboths wrote:
>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????

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
KenSheridan
3/22/2010 4:11:09 PM
I will try with your suggestions
Thank you again Ken.

"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Soboths wrote:
> >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????
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> 
> .
> 
0
Utf
3/23/2010 6:00:01 AM
Reply:

Similar Artilces:

Removing multiple hyperlinks
I've got data copied into my spreadsheet that includes hyperlinks. Ther are thousands of these in a column. I can remove individually by right-clicking and selecting Hyperlink an Remove. I can then arrow down and do a control Y, cell by cell, What I want to do is highlight the column and be able tp remove th hyperlinks all at once. I've exhausted myself peering through knowlegebases and groups, bu cannot find any way to make this work. Thanks for any input you can provide -- Message posted from http://www.ExcelForum.com Hi one way: Use the following macro to remove hyperlinks...

Create a list with multiple criteria
I have a data base with 1,000's of addresses w/zipcodes (Sheet 1 columns A to E, E being zipcodes). After setting a base address I get all of the zipcodes within a given radius (in this case 70 zipcodes), listed in Sheet 2, Column A. Next, I use COUNTIF (in column B) to find how many addresses are in each zipcode, in this example there are 46 addresses within the 70 zipcodes. What I want to do is create a list in sheet three that will list each with the data from Sheet 1 columns A to E). Any help would be appreciated. Ronbo ...

Charting multiple multiples
Hopefully I can explain this in a way that will be understood. Data: Place A Place B Place C FWD Back FWD BACK FWD Back Org_1 1 2 3 2 1 3 Org_2 2 5 4 6 1 4 I would like to place the data so that FWD and Back data is stacked on top of each other in different colors, and placed in one row on a 3-D chart as Org-1 and a second row as Org_2. Any ideas? Thanks. If you arrange your data like this: Back FWD Org_1 Place A 2 1 Place B 2 3 Place C 3 1 Org_2 Place A 5 2 ...

Multiple Hyperlinks In Same Shape Doesn't Work
Can anyone tell me how I can get multiple hyperlinks in the same shape to work? I have no problem inserting the hyperlinks, but they all point to only one. I scoured these posts, and found this (http://groups.google.com/groups?q=hyperlink+group:microsoft.public.visio.*&hl=en&lr=&ie=UTF-8&oe=UTF-8&group=microsoft.public.visio.*&selm=3eea6467%241%40news.microsoft.com&rnum=2), which doesn't appear to work. Thanks in advance-- RAD Um, which version of Visio are you using? As far as I know, at least Visio 2002 and Visio 2003 support multiple hyperlinks. Can't...

Attempting to display only certain records related to user criteria input
I have an Excel spreadsheet with 100 records. Each record contains columns of information with one of those columns being age. What would like to do is have the user fill in a cell at the top of the pag and then have only those records meeting that criteria be displayed. For example, "Show records where age exceeds ____". If the user input 34 in the blank, then only those records where the value in the ag column exceeds 34 would be displayed. If the user then changes thei input to 15, then only those records where the value in the age colum exceeds 15 would be displayed. At fi...

Counting dates with specific criteria
Hi, I have a column with various dates, I need some formulae to count different criteria. The criteria are: 1. Dates in the current month. 2. Dates last month. 3. dates in a calender year. 4. Dates in a financial year. Each criteria will be shown in seperate cells on a different worksheet on Excel 2003, also the current month is always the current month as we progress through the year. Thanks in advance, -- Steve Try these... 1. Dates in the current month. =SUMPRODUCT(--(TEXT(A2:A21,"myyyy")=TEXT(NOW(),"myyyy"))) 2. Dates last month. =SUMPRO...

Multiple Profiles, multiple accounts, multiple machine configuring?
Is there a vb or related method of adding *both* profiles *and* email accounts to multiple users logins from the administrator account? I have a network which is a workgroup (NOT a domain), in which there are about 15 users that are replicated on each of 5 machines. I'd like to be able to add all 15 Mail Profiles, and all 15 email accounts to each of the 15 logins on these 5 machines--obviously with as little work as possible. The mail is all retrieved from the same Exchange Server, but these machines *cannot* be added to a domain (don't ask!). I'd hate to have to log into ...

Date in the query is always short date
I want to capture the LONG date from a query, so I create a parameter for the date (mm ddd yy) . In its properties, it's format is long date, BUT it always shows up the short date. WHY? Dates are stored as decimal number counting days from 12/31/1899 midnight with time as the decimal fraction of a day. Formats are just different ways to display the information. So you got to set the format. In design view of the query click the field of the grid that has the datetime field, right click, scroll down and select Properties. Click 'Format' and then in the pull down...

DAP query question
I have a bunch of data access pages set up. Working showing the data for a particular data. I have new plans and need to have a date range choosen by a user on the page itself, then that data to be shown for the entered range. so the query would be a select query summing with the date criteria being between [startdate] and [enddate] problem is how do i either create a calendar control or two drop downs on the page itself that the user can change to view the nessisary data. Im assuming a command button will also be needed. is there a way to pass the values from controls on a dap to the que...

distributing data into multiple cells
hii i have to prepare an exel sheet of some 2000 products..i have to copy d data from the net and then prepare the sheet,with 4 different columns for 4 diff types of entries..now when i copy data nad paste is as text.it all goes in a single cell in a row .how can i distribute the data l into multiple cells????????? -- jaspreet ------------------------------------------------------------------------ jaspreet's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23821 View this thread: http://www.excelforum.com/showthread.php?threadid=374772 can you show some samp...

Suppressing Values in Autofilter Criteria List
Hello, I'm not a VBA guy, but I think what I'm trying to do can only get done with some lines of code. Any help would be appreciated. I've a fairly large data set that I'd like to be able to apply advanced filters to. I would like the source table for criteria to update automatically based on values from other criteria entered. For example: Column A could be Region. Column B could be job. Column C could be Salary Grade. I would like, should I choose East Region, for the list of possible jobs to be then updated automatically to exclude any jobs that are ...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Is there a way to sort multiple columns with a tab or something?
I have a 4 column spreadsheet. I want to be able to click the heading for each of the columns to hav them sort by that column if clicked. How do I do that -- Message posted from http://www.ExcelForum.com Hi why not used the soprt icon for this. Note: This could screw up your sorting if Excel does not recognize your database columns correctly -- Regards Frank Kabel Frankfurt, Germany > I have a 4 column spreadsheet. > > I want to be able to click the heading for each of the columns to have > them sort by that column if clicked. How do I do that? > > > --- > Mess...

Sending to multiple addresses
How do I send an email to multiple addresses having only the recipient see their own email address? Michelle wrote: > How do I send an email to multiple addresses having only > the recipient see their own email address? Put the addresses into the BCC: field. Note that many e-mail programs will decide such mails are spam though. >-----Original Message----- >How do I send an email to multiple addresses having only >the recipient see their own email address? >. Open your new email and go to tools, select recipient, when your address book opens select (highlight) the a...

Need help building query
I'm not that familiar to Access and would appreciate some help building the following SQL query. I have two tables, and if two categories match within the tables, then I would like a third category entry updated to the first table. 1st Table: Working 2nd Table: Original Categories (columns) that should match between table 1 and 2: Pt Acct # Charge Cd If the entires for a line match, then update: Lawson # (take from Original table and enter into line for Working table) So, basically if the (Pt Acct #) AND (Charge Cd) in the (Original) table match the (Pt Acct #) AND (Charge Cd) in...

Exchange 2000 Distribution Lists
Hello We are running an Exchange 2000 environment in Exchange Native Mode and Windows Mixed Mode. We just converted from Exchange 5.5 Is there any way to configure multiple people to be owners or editors of the distribution lists? This was possible in 5.5, but we cannot find a means of accomplishing this in 2000 Any help would be appreciated Thank yo Jason "Jason P.S." <jason.pruden-shebaylo@investorsgroup.com> wrote: >We are running an Exchange 2000 environment in Exchange Native Mode and Windows Mixed Mode. We just converted from Exchange 5.5. > >Is there any w...

multiple selection
How can I select multiple records into a list box? I want that for append identical records (for select records) in another table. Many thanks Florin Use a subform instead of a list box. In a subform bound to a related table, you can append as many rows as applicable. It is possible (but not simple) to use an unbound multi-select list box that is populated in the Current event of the form, and executes Append, Delete, and Update query statements based on the ItemsSelected if they are changed. You will also have to find a way to respond to the user "undoing" changes. -- Al...

Need to have a dropdown list containing multiple rows
I am setting up an form and need to create a dropdown list that will contain 8 sets of data. Each set needs to contain 3 rows of data (name, street address & city/state/zip. The goal is to allow the user to click onto the cell, see the dropdown and select which address set to use. This would then place the data into three rows suitable for mailing. I have never attempted anything like this and am in need of assistance. Thank you. --- Message posted from http://www.ExcelForum.com/ Pat What you ask is easily done using Data Validation drop-down list and a few Vlookups but........Ex...

Multiple domains #6
Hi, Having done some research I have found that it is possible to send and receive email from multiple domains with Exchange 2003. I have a few questions that I would like cleared up if anyone can help. 1. Is it really as simple as configuring a recipient policy for the new domain and then changing the mail record for that domain to point to the proper IP address? 2. If I set up a new account in AD, and need this account to be associated with the second email domain, will the default SMTP email be for the first domain? If so, can I just edit this directly? 3. If I set the default SMTP add...

Combine multiple tables into one
I took over a database in which the last person created a new table for everyday worth of data (ie. Feb-21-08 is named 022108). There are over 100 tables in the single database with the same columns and table attributes, I would like to combine all of these tables into one where I have a Date_Key field in place of the table name. I figured out how to query the table names using the following: SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name; Now I j...

forwarding mail to multiple external addresses
I have a user on our Exchange Server 2003 that wants his email to be forwarded to 2 different external email addresses and not have the original mail stored in his exchange mailbox at all. I can set this up to froward to only one external email address by creating a contact in Active Directory with an external email address and then forwarding to that contact. However, it won't allow me to forward to multiple contacts. Please help as this seems like a pretty common request from several users. I'm not sure, as I haven't tested it before, but you might be able to create a ...

Help Regarding an update query
Hi, iam trying to run an update query. the situation is as follows, i have linked excel sheets in my table. so i am using the update query to update all the records that i have them in my access tables. i tried this before many times and i was successful in doing that. But in some tables, it gave me an error message "Type mismatch in expression". i checked each and every field in my access table and in the excel sheets and they are all with the correct format. any help?? any ideas? On Fri, 31 Aug 2007 08:47:16 -0700, Samer <samoora44@gmail.com> wrote: >Hi, iam trying to r...

Finding Data in multiple worksheets
Using Excel 2000, how can I find data in multiple worksheets; example: any number between 200 & 299 in 32 worksheets? If you mean you want to find a single number across 32 worksheets, group your worksheets (click on the first, ctrl-click on subsequent (or even shift-click)). Then Edit|Find But I think I'd use Jan Karel Pieterse's FlexFind: http://www.oaltd.co.uk/MVP/ If you meant you wanted to find all 100 numbers (200-299) in all 32 sheets, then I think I'd have a macro search for each number and report the results on a new worksheet. "mark@southwestconst.com&quo...

Update Query in VBA
Hello, I am using Access 2003. I am trying to run an update query through VBA. My SQL is this: UPDATE CostSummarySheet SET CostSummarySheet.Active = Forms!fsubLOA!Active WHERE (((CostSummarySheet.Proj_Name)=[Forms]![frmCSSheetEdit]![Proj_Name])); and the code I am attempting to use is this: Dim db As DAO.Database Dim strSql As String strSql = "UPDATE CostSummarySheet SET [Active] = [Forms]![frmCSSheetEdit]![fsubLOA]![Active] WHERE [Proj_Name]= [Forms]![frmCSSheetEdit]![Proj_Name];" Set db = DBEngine(0)(0) db.Execute strSql, dbFailOnError ...

Inputbox with Listbox
Hello, I have a listbox of names with a row.source. I'd like, if possible, to create a VBA macro that would, after clicking on a name, trigger an inputbox that would enable me to add a $ value in the corresponding row (of the name clicked) in Column E. Jeff Jeff Made a few presumptions. 1) It's an activeX listbox on the worksheet (called ListBox1) 2) The 'listrange' (filling the list box' is in Range("A1:A11")) 3) This is only rough code. No error checking etc. 4) I am using the 'click' event of the control Private Sub ListBox1_Click() Dim sName...