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 / Forms / July 2007

Tip: Looking for answers? Try searching our database.

Select particular managers for a report

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
pareez - 10 Jul 2007 16:48 GMT
Working on a database which has a table that stores managers.  I'm trying to
create a form that will allow the user to select 1, a few or all managers to
display in a report.  Selecting only one manager or all managers, I know how
to do but selecting a few managers, ie. 2 of the 5 managers, I'm not sure
what approach to take.  I've been using a combo box so far for allowing the
user to select all or 1 manager only.  What's the easiest or best approach to
accomodate selecting a few managers?
Steve - 10 Jul 2007 17:09 GMT
Since you only have a small number of managers, a multiselect listbox might
work best. You can make multiple selections and use the selections as
criteria in your query that the report is based on.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdatasheet.com

> Working on a database which has a table that stores managers.  I'm trying
> to
[quoted text clipped - 8 lines]
> to
> accomodate selecting a few managers?
pareez - 10 Jul 2007 17:34 GMT
If I use that listbox in the criteria of my query, does the query
automatically know what items are selected on the list box?

> Since you only have a small number of managers, a multiselect listbox might
> work best. You can make multiple selections and use the selections as
[quoted text clipped - 17 lines]
> > to
> > accomodate selecting a few managers?
Klatuu - 10 Jul 2007 18:18 GMT
The query does not know.  You have to modify the query programmatically to
get it included.  Here is a function that will create a string you can use to
include the selections in the query:

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

You can use this to filter the report by one or more managers:

   strRptFilter = "[MANAGER] = " & BuildWhereCondition(Me.lstMgrs.Name)
   DoCmd.OpenReport "SomeReport", , , strRptFilter
Signature

Dave Hargis, Microsoft Access MVP

> If I use that listbox in the criteria of my query, does the query
> automatically know what items are selected on the list box?
[quoted text clipped - 20 lines]
> > > to
> > > accomodate selecting a few managers?
 
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



©2009 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.