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

 

[Ecniv]

Oliver :
listbox.Column(column[,row]) (starts at 0)
So column 2 would be listbox.column(1)

Mecha:
Private Sub OK_Click()
Dim RepTo As String
Dim frm As Form, ctl As Control
Dim lngItem As Long
Dim strWhere As String
'--- Don't use Str as string as Str is a function!!

'On Error Resume Next
'(no error handling yet???!!)

'---- testing on form code
Set frm = Me
' Set frm = Forms!frmReport

'---- report - and filter
RepTo = "rptProduction"
'Set ctl = frm.boxCountry
Set ctl = List0

If ctl.ItemsSelected.Count = 0 Then
MsgBox "running Report"
' DoCmd.OpenReport RepTo, acViewPreview
Else

For lngItem = 0 To (ctl.ListCount - 1)
If ctl.Selected(lngItem) Then
strWhere = strWhere & IIf(Len(strWhere) = 0, "", ",&quot & ctl.Column(0, lngItem)
End If
Next
strWhere = "[country] in (" & strWhere & &quot"
'---- for testing purposes
MsgBox strWhere

' DoCmd.OpenReport RepTo, acViewPreview, , strWhere
End If
End Sub

Note - I've remarked out some lines - you'll have to tweak as neccessary

Vince

 

[Oliver2003]

Hi Nate1749 and Ecniv thanks for the replys - very helpful

Nate1749 your code is just what I wanted it works great

thank you - oliver

 

[Nate1749]

Mecha: did you ever get this working 100%? I'm still struggling with it and was hoping you could post your code if your already there. Thanks,

-Nate

 

[idd]

Sorry,

I had forgotten about this one,

if you can send me a copy of your db right now nate, i'm free for a bit i'll see what I can do.

Make sure you remove any sensitive data,

I cant guarantee a solution but its worth a try.

Idd.

 

[idd]

whoops i forgot to mention my e-mail address

here it is

iadmani@lec.ac.uk

 

[Nate1749]

You've got mail. Thanks,

-Nate

 

[Mecha]

Sorry guys,
havent had a chance to try this out yet.

Will get back to you!

Thanks

Carly

 

[Mecha]

Okay,

i have had a play with the new code, thanks Vince,

but.....

the parameter boxes still remain.

I was reading that they can be caused by something in the report like sorting and grouping, not the form. I dot have and sorting or grouping so that was not the answer, so I tried playing around with the report, deleting all refernces to the [country] field ad then replacing them again, but to no avail. I made a completely new report but that didnt do anything either

I even tried using a different field [type] to see if it was a problem with the [country] field specifically, but that didnt work either.

Nate, how did yours go?

Idd, i will send you a copy, just have to cut the size down a bit first

Thanks

Carly

 

[Nate1749]

No progress, the more and more I play with it, the more and more I get away from what I originally had working... Just checked work email and received a message from our exchange server....

lec.ac.uk: Name server timeout


.... so idd has not yet received my e-mail... says it will keep trying for next 4 days...

-Nate

 

[idd]

Nate i got your mail, as I came in this morning.

will have a look at it now

idd

 

[idd]

Nate

I had a bit of a go with your db, I see what you mean about the dialog box, popping up, I had the same problem some time ago before I found the code which I use now, and suggested above to you. But for some reason it doesnt work with your db.

I'm not knowledgeable enough to know why it doesnt work.

I'm sorry I wont be able to be much help on this one.

Where's BillPower, DanVlas, JeremyNYC, or LittleSmudge when you need them.

Idd

 

[Poteen]

GIVE A MAN A FISH...........

I think you'VE gotten away from the core problem!. This is an issue that you will meet again and again. You need to develop a standard algorithmic approach that you can Plug&Play the next time!!!!!!!!!!!!!!

DEFINITION:
You need to pick up values from the combo that will be used later for a query.

ACTION:
(A)
Activate the EXTENDED select property (or programmatically MULTISELECT = 2)
(B)
Bind column(0) to the primary index field of the underlying table. The index number of each selected item then be the same as the record number in the underlying table. See where I'm going???.
(C)
When multiple selections are made in a list box, you can determine which items are selected by using the SELECTED property of the control. The SELECTED property is an array of values from 0 to the LISTCOUNT property value minus 1.
(D)
Loop through LISTITEM(N) where N is 0 to LISTCOUNT-1
If LISTITEM(N).SELECTED =1 then add that particular value of N to an array or any other method of choice for later reference.
(E)
You now have an array with the list of table index values for the selected items................

CAST AWAY!!!!!
JOHN

 

[idd]

John

Some Sample code might be useful to our friends Nate andMecha who might then be able to try it out.

I have a standard sub which I use and have modified over time and it still works but for some reason it doesnt work with either of the problems that Nate and Mecha are experiencing.

Idd

 

[jebry]

Hi Nate and Carly!

Haven't checked this thread for a while and it has gotten huge! If you want to send me a copy of your dbs with a brief explanation of the trouble (instead of having to read through all of this thread) I'll be happy to look at them for you. I am sure we can get the problems solved.

P.S. I will need the databases to be zipped because our email security will not let .mdb file through.



Jeff Bridgham
bridgham@purdue.edu

 

[Nate1749]

poteen: we've gotton away from the combo box idea and moved to the list box. Also, I have simple selected rather than extended; does this really make a difference? I thought this was only a difference to the user. Any code similar to this would be helpful. If your teaching a guy to fish you gotta at least give him a stick =)

jerby: you've got mail.

 

[Mecha]

Hi,

been looking after sick boyfriend all weekend,
jerby, will send you mail very shortly

thanks

Carly

 

[Nate1749]

thanks to idd, I've finally got this working.

I ran NUMEROUS checks against this code, and it seemed flawless everytime. After understanding how the strings work, I still wouldn't have figured out how to feed it correctly into a format for the criteria. Regardless, thanks again Jeff!! The code below is if the field is TEXT, if it's numerical Jeff gave me the code for that as well (only glanced at it but seems almost identical).

Now that we've got one listbox feeding correctly into the criteria, I'm going to try to add more.... until then, here's the code.

names you'll need to change
rpt74 = name of report
lbCampus = name of listbox
[Student Campus] = name of field in query
------------------------------------------------

Private Sub btnRPT74_Click()
On Error GoTo Err_btnRPT74_Click


Dim RepTo As String
Dim ctl As Control
Dim varRow As Variant
Dim strWhere As String

RepTo = "rpt74"
strWhere = ""


Set ctl = Me!lbCampus

If ctl.ItemsSelected.Count = 0 Then
MsgBox "Running Report"
DoCmd.OpenReport RepTo, acViewPreview

Else
For Each varRow In ctl.ItemsSelected
If strWhere = "" Then
strWhere = "[Student Campus] In ('" & ctl.Column(0, varRow) & "'"
Else
strWhere = strWhere & ", '" & ctl.Column(0, varRow) & "'"
End If
Next varRow

strWhere = strWhere & &quot"
DoCmd.OpenReport RepTo, acViewPreview, , strWhere
End If

Exit_btnRPT74_Click:
Exit Sub

Err_btnRPT74_Click:
MsgBox Err.Description
Resume Exit_btnRPT74_Click

End Sub

 

[Nene75]

I am trying to accomplish the same thing for seleceting multiple month field from the list box. But when I select multiple month for searching records "Enter Parameter Value" prompt pops and asks for entering the same selected month from the list box. Though the results provides what's been selected from the list box but why do I also receive the "Enter parameter Value" prompt?

Any help provided would be appreciated!
Thanks!

I am using this code : Please let me know what i am doing wrong?....


Private Sub searchResults_Click()
On Error GoTo Err_searchResults_Click
Dim monthNames As String
Dim varRow As Variant

monthNames = ""

For Each varRow In LstMonth.ItemsSelected
monthNames = monthNames & "Month = " + LstMonth.ItemData(varRow) + " Or "
Next varRow
varRow = 0
monthNames = Left$(monthNames, Len(monthNames) - 3)

Dim stDocName As String

stDocName = "frmAdvanceSearchResults"
DoCmd.OpenForm stDocName, , , monthNames
Exit_searchResults_Click:

Exit Sub

Err_searchResults_Click:
MsgBox err.Description
Resume Exit_searchResults_Click

End Sub

And this is what i used in Query
SELECT AllReports.Author, *
FROM AllReports
WHERE (((AllReports.Author) Like &quot;*&quot; & IIf([Forms]![AdvanceSearch]![Author]<>&quot;&quot;,[Forms]![AdvanceSearch]![Author],&quot;*&quot; & &quot;*&quot & &quot;*&quot AND ((AllReports.Month) Like IIf([Forms]![AdvanceSearch]![Month]<>&quot;&quot;,[Forms]![AdvanceSearch]![Month],&quot;*&quot) AND ((AllReports.Year) Like IIf([Forms]![AdvanceSearch]![year]<>&quot;&quot;,[Forms]![AdvanceSearch]![year],&quot;*&quot) AND ((AllReports.TypeOfReport) Like IIf([Forms]![AdvanceSearch]![TypeOfReport]<>&quot;&quot;,[Forms]![AdvanceSearch]![TypeOfReport],&quot;*&quot) AND ((AllReports.ResearchArea) Like IIf([Forms]![AdvanceSearch]![ResearchArea]<>&quot; &quot;,[Forms]![AdvanceSearch]![ResearchArea],&quot;*&quot) AND ((AllReports.Title) Like &quot;*&quot; & IIf([Forms]![AdvanceSearch]![Title]<>&quot;&quot;,[Forms]![AdvanceSearch]![Title],&quot;*&quot; & &quot;*&quot & &quot;*&quot AND ((AllReports.ReportNumber) Like &quot;*&quot; & IIf([Forms]![AdvanceSearch]![ReportNumber]<>&quot;&quot;,[Forms]![AdvanceSearch]![ReportNumber],&quot;*&quot; & &quot;*&quot & &quot;*&quot AND ((AllReports.Keywords) Like &quot;*&quot; & IIf([Forms]![AdvanceSearch]![Keywords]<>&quot;&quot;,[Forms]![AdvanceSearch]![Keywords],&quot;*&quot; & &quot;*&quot & &quot;*&quot)
ORDER BY AllReports.Year DESC;

 

[Nate1749]

nene75: we were having the same problem (if you wanted to read the whole thread)... Regardless, I'm no code expert, but copy what I posted above; it works great!

Code is compliments of idd =)

-Nate

 

[idd]

Hi all,

I'm glad nate has resolved his problem, it took 21 days but, he persevered and got the solution in the end. I guess the saying is true

patience is a virtue.

I cant take full credit for the code I supplied in this thread as mentioned in my posting on June 4th I got the code as a result of a question posted on this web-site. I went in search for the post and found it, it is

thread702-96918

I think it was Jerry Dennison who was helping to solve the problem. Three cheers for Jerry.

Hip Hip....Hooray, Hip Hip....Hooray, Hip Hip....Hooray

Nate, I'm a bit confused as to how I helped you, cos you had tried the code I posted and it didnt work I also tried to get it work with your DB but it didnt work, so what did you do that was different.

For those of you who are still having difficulties, you may wish to try an alternative method which is decribed in this FAQ

faq701-1927


Idd