Selecting mutiple choices from either a list box or combo box? 11

[Nate1749]

I have a non-normalized .csv file that we get from an external source. It's then linked to an access table. I think have queries that do a Group By to pull out unique instances for certain columns and then that populates the combo/list box's on a form. The selections made on the form is the criteria for a report that is ran. Currently everything works fine, except now I'm trying to figure out how the user can select multiple criteria.

For instance, in cboLocation there is Online, DuPage, Kane.
Currently I can only select one choise and run the report; I'm trying to figure out how I could possibly select two or all three. I changed the combo box to a list box and tried holding ctrl, but no luck.

-Nate

 

[idd]

Nte,

To start with lets try and see if we can configure the list box to allow multi-selects.

on the list boxes properties, select the tab "Other" on this tab there should be a property for "Multi Select" you can select from

None
Simple or
Extended.

this is waht the differences are:

None
The user can only select one item.

Simple
Clicking on an item selects it and clicking again on it deselects it.

Extended
Standard Windows Style, Cliking an item selects just that One. Holding down CTRL while Clicking multi selects or deselects.

HTH

Idd

 

[Nate1749]

Awesome this works great... Do you think that since this will be the criteria in a column that will drive the report, that access will be smart enough to query out both of them correctly if two are selected?

I guess I will know for sure when I get that far in a day or two. Thanks!

-Nate

 

[jebry]

Hi Nate!

To answer your question, Access isn't that smart. The value of a multiselect list box is always null so, if it referenced in a query, the value read will always be null. The most common way to drive a report from a multiselect list box is put code behind a button which the user will click when they are done selecting. Like this:

Dim varRow As Variant
Dim strCriteria As String

strCriteria = ""
For Each varRow In YourList.ItemsSelected
strCriteria = strCriteria & "YourField = '" & YourList.Columns(0, varRow) & "' Or "
Next varRow

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

DoCmd.OpenReport "YourReport", , , strCriteria

hth


Jeff Bridgham
bridgham@purdue.edu

 

[Nate1749]

ok I'm a little lost...

the names are...

frm74 = name of form with list boxes on it

lbLocation = name of list box (only doing one list box right now) with multiple criteria (using simple mode) able to be selected

qryOBTReports = name of query that pulls all fields from .csv file, and has criteria calling on frm74.

rpt74 = name of report that is previewed when button is pushed on frm74. Report is ran from qryOBTReports.

The criteria in qryOBTSReports for the location field is..
(Like "*" & [Forms]![frm74]![lbLocation] & "*&quot Or (nz([Forms]![frm74]![lbLocation],"&quot="&quot

The formula pulls what is selected, but if nothing is selected (null), then it pulls everything. You were right that the list box response is always null since everything is always being pulled right now. This is the formula I used for combo boxes (not sure if it requires any modification for list boxes).


The event procedure, on click, for the button that will preview the report rpt74 on frm74, has the following code behind it...


Dim varRow As Variant
Dim strCriteria As String

strCriteria = ""

For Each varRow In lbLocation.ItemsSelected
strCriteria = strCriteria & "Location = '" & lbLocation.Columns(0, varRow) & "' Or "
Next varRow

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

DoCmd.OpenReport "rpt74", , , strCriteria


What is YourList and YourField supposed to be replaced with (from previous post)? I've tried using lbLocation for YourList and Location (name of field in query w/criteria) for YourField, and I receive the error "Compile Error - Method or data member not found," for the portion of code that in in bold above (highlighted in yellow in debugger).


-Nate

 

[Mecha]

Hi

I am trying to do a similar thing with my database.
i have a number of country names and other critera that i would like to be able to select more than one of to generate the rpoart with.

I dont know much about code and am using a SET VALUE macro for the okay button on my form.

Does this code replace that macro? when i tried, i couldnt even get the error message that Nate wrote about.
Do you need to add more code to get the information sent to the query?

I am also slightly confussed about which names to put where

Thanks

....Carly

 

[idd]

Nate and Mecha,

This is the code I use behind a button on the form holding the list box. The criteria for the report is created through the code and passed on to the report as the "Where" criteria

A few notes

The code I used was orignally taken from a response to a question on this site. I have modified it slightly to meet my needs but the main part of it, is still the same.

This method shown below creates the criteira for the report. Although the report may/will be based on a query and many other criteria may be specified in the query, the one criteria you are using your lisbox for will be passed through as a Where string when the report is opened.

-----------------------------------------------------------------
start of code
-----------------------------------------------------------------

'set up a few variables to assign the form, listbox, report and error messages to.
'If you make use of this code the only places you'll need to change the code will be the
'form, listbox and report names assigned to the variables, and of course
'the error message and the STR string in the beginning.

Dim RepTo As String
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim str As String

'name of the form with ListBox
Set frm = Forms!FRM_Lbl_Cent_Select
'name of the report you wish to open
RepTo = "Rpt_Labels_Cent"
'The beginning of the Where criteria.
'Change the [Centre_Id] to the
'name of the field you need to check
str = "[centre_Id] in ("

'name of the ListBox
Set ctl = frm.MyLBox
'The following IF....THEN.....ELSE.....END statement
'Checks to see if there are any selections, if not then
it brings up an error message stating the obvious
'if there are selections made then it adds them to the STR
'Which will be used as the Where Criteria for the open report Command
If ctl.ItemsSelected.Count = 0 Then
MsgBox "No Centres have been selected," & (Chr(13)) & (Chr(13)) & _
"Please select at least one Centre from the list", vbExclamation, _
"Selection Error"

Else
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
str = str & ctl.ItemData(varItem) & ", "
Next varItem

'Trim the end of strSQL to get rid of the trailing comma and then replaces with a )
'to complete the string
str = Left$(str, Len(str) - 2)
str = str & &quot"

'Finally open the desired report using STR as the where string
DoCmd.OpenReport RepTo, acViewPreview, , str

End If


-----------------------------------------------------------
End of Code
-----------------------------------------------------------


Hope that Helps.

Idd

 

[Mecha]

Hi idd

Thanks for all your help, but it this code doesnt seem to work for me. when i push the okay button on the form, instead it asks for parameter values for the choices i made in the list box. If i push Ok with nothing enetered, it does at least open the report, but it is empty. If i type in the choices again into the space on the pop up, then it does work

Is there also a way to remove the If = 0 error criteria? Sometimes i dont want to choses anything. can you just delete that line?

I was also hoping to be able to use this multi select function for more than one of the criteria on the form

here are some more details
form name - frmReport
list boxes/text boxes on form - boxCountry, boxType, boxManufactuer, boxModel etc (i havent decided which ones will be list boxes or combo boxes yet, depending if this code works for more than one)
table which query is based on - tblProduction
query which report is based on - qryProduction
report name - rptProduction

I am using this expression in the query.
Like "*" & [Forms]![frmreport]![boxCountry] & "*" Or nz([Forms]![frmreport]![boxCountry],""="&quot

what am i doing wrong??

Thanks in advance

.....Carly

 

[idd]

Mecha,

You can remove the few lines which check if there is no data selected. This should allow all data to appear on the report. The lines you will need to remove are:

------------------------------------------------
Start of Code
--------------------------------------------------

If ctl.ItemsSelected.Count = 0 Then
MsgBox "No Centres have been selected," & (Chr(13)) & (Chr(13)) & _
"Please select at least one Centre from the list", vbExclamation, _
"Selection Error"

Else


---------------------------------------------------
End of Code
---------------------------------------------------


You will also need to remove the end if from the bottom of the procedure.

The next thing you probably need to do is, first test if this works from one criteria, which will be one list boxes values. make sure that you have assigned the listbox name to the variable for the listbox in the code. This might be the cause for the pop up asking for the information.

Where you need to do this is. the line where it reads

set ctl = frm.MYLBox

change the word MYLBox to the name of your list box.
so it might read something like this

set ctl = frm.BoxCountry

if your listbox name is BoxCountry.

also if you are using this method to filter your criteria through the the use of a listbox then you will not need to put the criteria mentioned in your response above in the query, you can leave it blank and let the code do all the work.

let me know how you get on.

HTH

Idd

 

[Oliver2003]

Hi idd,

I wondered if you could help me - I am trying to use your code above, It works great if the listbox has one column with the ID's in but my List box has 8 columns and the ID values I wish to create the string from are in the 2nd column along (i.e. column 1)

Can your code get the values from the second column

Thankyou

 

[Nate1749]

I receive

Syntax error (missing operator) in query expression '([Location] in (DuPage, Online, Tinley Park))'.


I'm assuming this is because I'm setting the name of the field to the name of the field in the query in which this column is being ran from. If this is wrong, what should the field name be?

so I have...

str = "[Location] in ("

 

[Nate1749]

actually I am having the same problem as Carly... If I select no items I get the pop-up (that's fine).

What's strange is the inconsistency I'm receiving between either the pop-up for parameter, or the syntax error; there are 3 items in the list.

If I select one item, I get the parameter value pop-up; this happens if I select just the 1st or just the 2nd item. If I just select the 3rd I receive the syntax error above.

If I select the top two I receive the parameter value pop-up.
If I select the bottom two I receive the syntax error. And If I select the top and bottom item I receive the syntax error.

-Nate

 

[Mecha]

Hi

I tried just removing the lines, but then the report doesnt run at all then.
If there a way to say
If ctl.ItemsSelected.Count = 0 Then "select all"??

Would it be better to use another control that when you click it, it automatically selects all? I tried doing like a keypress function to slect all but then form would not even run anymore

I tried manually selected each counrty name, but then the program just does nothing at all. you click the ok button and nothing happens and the form closes,
Can you overload it by sending too much info? Up to about 8 country names it will work, but i have about 13

Also i checked the code for the name of the litstbox, but all that seems correct so i still dont know what is causing the parameter entry popup.

It seems i have a big problem database here, doesnt want to do anything i tell it to.

Thanks

.....Carly

 

[idd]

Hi all,

Sorry I wasnt around much of yesterday, I had four exams in the afternoon, and I must they that I'm sure they all went fine, they were just ECDL Exams, (European Computer Driving License) Simple stuff but it doesnt hurt to have the qualification does it.

anyway.

Mecha.

What do you have in your query. Are you setting any criteria in your query ?

Mecha and Nate
If you want the report to show all when no items are selected form the listbox i.e. everything the query which it is based on would display without the listbox criteria then add the following code to replace the existing docmd.openreport command at the bottom, this should do the the trick.(assuming you are using the code I have given previously.)


If ctl.ItemsSelected.Count = 0 Then
DoCmd.OpenReport RepTo, acViewPreview
else
DoCmd.OpenReport RepTo, acViewPreview, , str
end if

Nate, I don't know what is causing the syntax error but I think it might be that you are probably missing a comma or a parenthesis, or something in the code. could you post your code please, that is the code behind the button. Also include Once more the name of you list box, Form and Report

Mecha if you are having the above problem please post your code and other details too.

Mecha and Nate
The parameter entry pop up is probably caused by some criteria set in your queries.

Could you let me know what your query has as its criteria if any ?

Idd

 

[idd]

Oliver,

There is a way of using the second, or any, column of the row.

There is command called

Column

quite a surprise hey!

but i'm not sure how to use it. I have looked at the help file but really havent understood too well.

If you do find out how to and its not from this thread then please could you post your finding on this thread as it would be quite useful.


sorry I wasnt much help.

Idd

 

[Mecha]

Hi

Well, i think i have been fiddling with the code too much cause now it doesnt work at all. The annoying thing is i kept a copy of it before i started to change it but even if i put it back to the original it still wont work
I am not getting any error messages at all now, but clickling the OK button does nothing.

This is what i have now behind the OK button

Private Sub OK_Click()
Dim RepTo As String
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim str As String

Set frm = Form!frmReport
RepTo = "rptProduction"
str = "[country] in ("

Set ctl = frm.boxCountry

If ctl.ItemsSelected.Count = 0 Then
DoCmd.OpenReport RepTo, acViewPreview

Else
For Each varItem In ctl.ItemsSelected
str = str & ctl.ItemData(varItem) & ", "
Next varItem

str = Left$(str, Len(str) - 2)
str = str & &quot"

DoCmd.OpenReport RepTo, acViewPreview, , str
End If
End Sub

And this is what is in the query
I took out all the
Like "*" & [Forms]![frmreport]![boxCountry] & "*" Or nz([Forms]![frmreport]![boxCountry],""="&quot
criteras i had in their previously casue i thought it might help, but it didnt.

SELECT [tblproduction forecast].Country, [tblproduction forecast].TYPE, [tblproduction forecast].MFR, [tblproduction forecast].SEG, [tblproduction forecast].PLATF, [tblproduction forecast].MODEL, [tblproduction forecast].[2003], [tblproduction forecast].[2004], [tblproduction forecast].[2005], [tblproduction forecast].[2006], [tblproduction forecast].[2007], [tblproduction forecast].[2008], [tblproduction forecast].[2013], [tblproduction forecast].GROUP, [tblproduction forecast].ASS, [tblproduction forecast].[ASS/ PROD]
FROM [tblproduction forecast]
ORDER BY [tblproduction forecast].Country, [tblproduction forecast].TYPE, [tblproduction forecast].MFR, [tblproduction forecast].MODEL;

A bit more background, it is a database with production forecast figures for cars produced in asia (and will have sales figures if i can get this part working first)
I need to be able to generate report by country, manufacturer etc, and sometimes by more than one i.e. japan and china, and sometimes all of asia.

what did i do wrong???
It will still work if i go back and do it the old way with the "like" criterias in the query but then i can only select one item from the boxes, and it will only open if the report is opened first. If i cant select more than one it is not going to be worth it.


Thanks in advance

......Carly

 

[Mecha]

Okay, i decided to fiddle some more and i think i have got it working better, at least it will generate the reports now

Here is the (almost) working code. It will now do report when nothing is selected in the list box.
Thanks Idd

Private Sub OK_Click()
Dim RepTo As String
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim str As String
Set frm = Forms!frmReport
RepTo = "rptProduction"
str = "[country] in ("
Set ctl = frm.boxCountry
If ctl.ItemsSelected.Count = 0 Then
DoCmd.OpenReport RepTo, acViewPreview
Else
For Each varItem In ctl.ItemsSelected
str = str & ctl.ItemData(varItem) & ", "
Next varItem
str = Left$(str, Len(str) - 2)
str = str & &quot"
DoCmd.OpenReport RepTo, acViewPreview, , str
End If
End Sub

The pesky parameter pop up will not go away. I have removed all criteria in the "country" field of the query, and returned the
Like "*" & [Forms]![frmreport]![boxField] & "*" Or nz([Forms]![frmreport]![boxField],""="&quot
to the rest of the criteria which are not list boxes.

Apart from that, all i need to know how to do now is to add more and more list boxes to this code.
I have field for Manufacturer, model, type that need the same treatment as above.

Idd, do i just duplicate the code using different names?

Thanks

.....Carly

 

[idd]

Mecha,

I'm sort of stuck and don't know what to say at the moment. If I had a bit more time on my hands I would have asked you to mail me the db so I could play around with it. But unfortunately I'm a bit pushed for time today and possibly tomorrow, I have to get a written report done for a director. If I get this done by today then I should be free (hopefully) tomorrow.

In the meantime i'm hoping some expert will look at this thread and shed some light in a short time so that your problem can be resolved.

I wish I could have been more help.

Idd

 

[Nate1749]

Carly,
You don't get a syntax error when you select an item in your field and then click the button to run the report? My code is identical to yours (except names of forms & fields) & if I don't select anything the report runs everything, which is fine. But if I select something I get a syntax error; I also have nothing in the criteria section of the query.

-Nate

 

[Nate1749]

Oliver: This code may you more than me... This is from a sample database in a book I bought; it's a listbox where you can select multiple authors and then click edit and it will filter out whatever is selected. I tried changing my code to this as much as I code, but I get a new error now.


Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!lstAName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstAName.ItemsSelected
' Grab the AuthorID column for each selected item
strWhere = strWhere & Me!lstAName.Column(0, varItem) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the authors form filtered on the selected authors
gstrWhereAuthor = "[AuthorID] IN (" & strWhere & &quot"
DoCmd.OpenForm FormName:="frmAuthors", WhereCondition:=gstrWhereAuthor
' Hide the New button, but show the Show All button
Forms!frmAuthors!cmdAddNew.Visible = False
Forms!frmAuthors!cmdShowAll.Visible = True
DoCmd.Close acForm, Me.Name
End Sub