Adding a second listbox to my form

[David Ball]

Hi,

I have a listbox (List10) on a form that has a command button (cmdOpenQuery6) that runs a report (rptJC_Manhours_JC_DC_SI_2) via a query (qryJC_Manhours_JC_DC_SI_3).
The row source of List10 is SELECT DISTINCT [tblSMSdata].[Project] FROM tblSMSdata UNION SELECT "ALL" FROM tblSMSdata

The listbox contains Project names and when I select one (or several) it opens the report showing records for the project/s selected (the field in the query and table is called Project).
The idea is that I can select a Project or Projects from the listbox and see only those records. This works well but I would also like to be able to select the week ending date so that I can see records by Project for a particular reporting week.
To this end I have set up another listbox (List11) on the same form that pulls dates from the Reporting Week Ending field.
The row source is SELECT DISTINCT [Reporting Week Ending] FROM tblSMSdata;
This is where I start to get well out of my depth and after a few attempts to modify my code to get this working I realise that I need help.

How would I modify the code below to allow me to select Projects and a Reporting Week Ending date and then run the report?

Private Sub cmdOpenQuery6_Click()
On Error GoTo Err_cmdOpenQuery6_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()
strSQL = "SELECT * FROM qryJC_Manhours_JC_DC_SI"
'Build the IN string by looping through the listbox
For i = 0 To List10.ListCount - 1
If List10.Selected(i) Then
If List10.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & List10.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Project] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qryJC_Manhours_JC_DC_SI_3"
Set qdef = MyDB.CreateQueryDef("qryJC_Manhours_JC_DC_SI_3", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenReport "rptJC_Manhours_JC_DC_SI_2", acViewReport
'Clear listbox selection after running query
For Each varItem In Me.List10.ItemsSelected
Me.List10.Selected(varItem) = False
Next varItem
Exit_cmdOpenQuery6_Click:
Exit Sub
Err_cmdOpenQuery6_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdOpenQuery6_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery6_Click
End If
End Sub

Thanks very much

Dave

 

[isladogs]

The row source of List10 is SELECT DISTINCT [tblSMSdata].[Project] FROM tblSMSdata UNION SELECT "ALL" FROM tblSMSdata

I presume you are trying to get a list of unique projects from tblSMSData in List10 and that the UNION part is to have an extra row called ALL

Change this to:

SELECT DISTINCT [tblSMSdata].[Project] FROM tblSMSdata;

Then have a command button next to the listbox 'Select All' with code:

   With Me.LstClasses
       'fill List
       For I = 0 To .ListCount - 1
            .Selected(I) = True
       Next
    End With

You could also have a second button cmdNone labelled 'Clear All' with code:

   With Me.LstClasses
       'fill List
       For I = 0 To .ListCount - 1
            .Selected(I) = False
       Next
    End With

The rest of the code could be simplified if you are happy doing this

You then mention a second listbox with week ending dates
Why not combine these in one listbox:

SELECT DISTINCT [Project], [Reporting Week Ending] FROM tblSMSdata;

 

[David Ball]

Thanks, but I don't understand how I could have the Projects and Reporting Week Ending in one list box. I would have a list with Projects and dates? And I then select the projects I want in my report and the reporting week dates in the same list?

 

[isladogs]

That was what I meant.

Even if you don't like that idea, I'd get rid of the UNION so you can make the rest of your multiselect IN code more understandable

 

[David Ball]

I still don't know how to set this up so that I can make a selection from each listbox and run the report.

 

[jdraw]

David,

You may get some ideas from this material at Martin Green's site.

 

[David Ball]

Thanks jdraw and ridders, I will check out the material suggested by jdraw and see if I can figure this out