Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / October 2007

Tip: Looking for answers? Try searching our database.

Multi List Box help

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Chad - 03 Oct 2007 07:31 GMT
Hello, I am having so much trouble with the multi select list box on a form.
I want to be able to select a few names from the list box and have it run a
query which brings up a report. I just cant figure out the code for the list
box. The list box name is "list1" and my report name is
"rpt_EmployeeComparison" and my query for the report is
qry_EmployeeComparison if that matters. Here is the code I have in the button
for the list box as of now and it works but its bringing up all the names in
the list box because I dont have the code for the multiselct. What and where
in the code I have as of now would I add? Thanks!!


Private Sub cmdEmployeeComparison_Click()
On Error GoTo Err_cmdEmployeeComparison_Click

   Dim stDocName As String
   Dim stLinkCriteria As String

   If ValidDates() Then
       If Me.list1 & "" <> "" Then
           stDocName = "rpt_EmployeeComparison"
           DoCmd.OpenReport stDocName, acPreview
       Else
           MsgBox "Select Employee for Individual Reports.", vbOKOnly,
"Employee Not Selected"
       End If
   End If

Exit_cmdEmployeeComparison_Click:
   Exit Sub

Err_cmdEmployeeComparison_Click:
   MsgBox Err.Description
   Resume Exit_cmdEmployeeComparison_Click
End Sub
Klatuu - 03 Oct 2007 19:14 GMT
I feel your pain.  It took me a while to work this out because I had to work
with 7 list boxes the first time I had to do this.
The basic concept is you need to read through the ItemsSelected collection
of the list box and build a string you can use as the Where argument of the
OpenReport method.
Here is a function that will do that.  You just pass the control to it and
it read through the list and builds part of the string:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

   Set ctl = Me.Controls(strControl)
   
   Select Case ctl.ItemsSelected.Count
       Case 0  'Include All
           strWhere = ""
       Case 1  'Only One Selected
           strWhere = "= '" & _
               ctl.ItemData(ctl.ItemsSelected(0)) & "'"
       Case Else   'Multiple Selection
           strWhere = " IN ("
           
           With ctl
               For Each varItem In .ItemsSelected
                   strWhere = strWhere & "'" & .ItemData(varItem) & "', "
               Next varItem
           End With
           strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
   End Select
   
   BuildWhereCondition = strWhere
   
End Function

Here is a modified version of your code (untested) that you can try. You
will have to change SOME_FIELD to the actual name of the field you are
filtering on.

Private Sub cmdEmployeeComparison_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim strWhere As String

   On Error GoTo Err_cmdEmployeeComparison_Click

   If ValidDates() Then
       strWhere = BuildWhere(Me.list1)
       If Len(strWhere) > 0 Then
           strWhere = "Where SOME_FIELD " & strWhere
           stDocName = "rpt_EmployeeComparison"
           DoCmd.OpenReport stDocName, acPreview, ,strWhere
       Else
           MsgBox "Select Employee for Individual Reports.", vbOKOnly,
"Employee Not Selected"
       End If
   End If

Exit_cmdEmployeeComparison_Click:
   Exit Sub

Err_cmdEmployeeComparison_Click:
   MsgBox Err.Description
   Resume Exit_cmdEmployeeComparison_Click
End Sub

Signature

Dave Hargis, Microsoft Access MVP

> Hello, I am having so much trouble with the multi select list box on a form.
> I want to be able to select a few names from the list box and have it run a
[quoted text clipped - 30 lines]
>     Resume Exit_cmdEmployeeComparison_Click
> End Sub
Chad - 04 Oct 2007 06:26 GMT
Thank you for the reply! I tried the code you added to what I had already and
im getting an error (Compile error: Sub of Function not defined) and its
highlighting this word in the code (BuildWhere). Im totaly lost.... Thanks!
Signature

Newbies need extra loven.........

> I feel your pain.  It took me a while to work this out because I had to work
> with 7 list boxes the first time I had to do this.
[quoted text clipped - 98 lines]
> >     Resume Exit_cmdEmployeeComparison_Click
> > End Sub
Klatuu - 04 Oct 2007 14:51 GMT
Make it a Public function in a standard module or put it in the form's module.
Signature

Dave Hargis, Microsoft Access MVP

> Thank you for the reply! I tried the code you added to what I had already and
> im getting an error (Compile error: Sub of Function not defined) and its
[quoted text clipped - 102 lines]
> > >     Resume Exit_cmdEmployeeComparison_Click
> > > End Sub
Chad - 04 Oct 2007 23:59 GMT
Im sorry but im not quite sure what you mean? Thanks!
Signature

Newbies need extra loven.........

> Make it a Public function in a standard module or put it in the form's module.
>
[quoted text clipped - 104 lines]
> > > >     Resume Exit_cmdEmployeeComparison_Click
> > > > End Sub
Klatuu - 05 Oct 2007 14:55 GMT
A standard module is a VBA code module you create.  In the main database
window, select Modules and click on new.  The VBA editor will open with a
blank module.  Paste the code in there.  Save the module.  Do not name the
module the same name as the function.
Signature

Dave Hargis, Microsoft Access MVP

> Im sorry but im not quite sure what you mean? Thanks!
>
[quoted text clipped - 106 lines]
> > > > >     Resume Exit_cmdEmployeeComparison_Click
> > > > > End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.