Multi Select List Box to filter Report 1

[ssecca26]

i spend whole week to solve this problem but until now i cant solve it, can someone help me please.

i have this in a form

lstArea ---- Area 1-4
lstCP - CP01 - 10
Name - 4 Names
cmbfrom - date from
cmbto - date to
sections - section in my table - fieldnames(Section 1- 5)
cmdpreview - preview a report


i want a report to filter my choices in the above criterias, if the box is empty means all


please help me, i really need to get this code.


Thanks!

 

[ssecca26]

should i remove the source from the report?

 

[FancyPrairie]

I don't see a Where clause in your SELECT statement that would filter the records based on the report you showed in a previous post.

I think I would set up a report criteria form with several multi-select list boxes and a 2 textboxes that represent a range.

The 2 textboxes would represent the begin and end dates for filtering on the Project Week (i.e. weeks 3-7)

I would have one multi-select list box that lists the Areas from which the user can filter the records (i.e. Area 3, Area 4...)

A second list box would list the Names from which the data could be filtered. Like wise for CP, Secion 1, etc.
Your report criteria screen my look something like this:

_____________      _____________
Begin Week         End Week

_____________     ______________
 List of Areas      List of Names

  -----------------------
  | print report button |
  -----------------------

With this approach, the users can filter the report however they want. If they don't select anything, the report will reflect everything your Select clause pulls.

However, if the user entered 3 for the Begin Week and 6 for the End Week and selected Area 3 and Area 4, then the report would only show records from week 3 thru week 6 where the Area = "Area 3" or "Area 4".

BuildWhere would return something like this:

(Project_Week Between 3 and 6) AND (Area In ("Area 3","Area 4"))

 

[ssecca26]

how can i build a where clause? in my query?

 

[FancyPrairie]

It doesn't matter. Try this simple test. Create a new form and add a list box to it. Set the "Row Source Property Type" property to "Value List". In the "Row Source" property enter 5 different Areas that exist in your table, separated by semicolons (ex. "Area 3";"Area 4"). Set the Tag Property of the list box to: Where=[Weekly Report].Area,string.

Now place a button on the form and in the "On Click" event property select from the drop down "[Event Procedure]". Then click the ... button to the right of the property box. The code window should pop up. Modify the click procedure for the button to look like this.

Private Sub NameOfYourCommandButton_Click()

    Docmd.OpenReport "YourReportName",acViewPreview,,BuildWhere(Me)

End Sub

Now, open the form and select one or more areas from the list box and click on your button. The report should reflect only those records whose Area equals the one(s) you selected in the list box.

Once you understand how that works, try adding another list box for Name, etc. Once you get those working try adding 2 text boxes to represent your start and end project weeks.

I'm assuming you do not have a table of Areas and a table of Names, etc. So, once you get the above working, you may want to change the Row Source of your list boxes to a Query that select the Areas from you [Week Report] table.

 

[ssecca26]

Thanks FancyPrairie!

now it is working and i figure out the problem, i have a control source in my property.


Thank you so much!

hope you wont get tired on answering my questions in the future til i got all my database working. Thanks again!

 

[ssecca26]

Now my form before is working,, i now know the logic of that one,

i encounter again another problem in the same report,
i have the section list which list all the section in my table like this

Section_1....text
Section_1details..memo
Section_2...text
Section_2details....memo
and so on

then in my wuery report form i have a list like this one as choices

Section_1
Section_2
Section_3
so on

if i tick section_1 it will only show the value of section 1.

as of now when i tick a section it will show everything with that a specific area,cp and name.

your help is very much appreciated.


Thanks!

 

[FancyPrairie]

Section_1 is the name of one of your fields. What you want to filter on is the value of Section_1. For example, you might include another listbox on your form that contains the possible values of Section_1. For example, "10 Days; 20 days;". So, if the user selects "10 Days", only those records where the value of Section_1 contains "10 days" will be displayed.

 

[ssecca26]

no i want to display all the value of each section it (no criteria).

for example i tick area 1, cp 1, name malu, section 2

it will only show the value of area 1, cp 1, for malu and the value of section 2.

if i tick section 5 and 7 it will show the value of section 5 and 7.

 

[FancyPrairie]

I went back and reviewed what you are trying to do. In your first post, you indicated that you have several list boxes:
lstArea ---- Area 1-4
lstCP - CP01 - 10
Name - 4 Names
cmbfrom - date from
cmbto - date to
sections - section in my table - fieldnames(Section 1- 5)

The Tag Property for lstArea should be Where=[Weekly Report].Area,string

The Tag Property for lstCP should be Where=[Weekly Report].CP,string

The Tag Property for Name should be Where=[Weekly Report].Name,string

If I understand you correctly, by default, your Select statement looks like this:

SELECT [Weekly Report].ID, [Weekly Report].Project_Week, [Weekly Report].Area, [Weekly Report].CP, [Weekly Report].Name, [Weekly Report].Section 1, [Weekly Report].Section 1_Details, [Weekly Report].Section 2, [Weekly Report].Section 2_Details, [Weekly Report].Section 3, [Weekly Report].Section 3_Details, [Weekly Report].Section 4, [Weekly Report].Section 4_Details
FROM [Weekly Report];

However, if the user Selects Section 1 and Section 3, then your select statement would look like this:

SELECT [Weekly Report].ID, [Weekly Report].Project_Week, [Weekly Report].Area, [Weekly Report].CP, [Weekly Report].Name, [Weekly Report].Section 1, [Weekly Report].Section 1_Details, [Weekly Report].Section 3, [Weekly Report].Section 3_Details
FROM [Weekly Report];

Am I understanding you correctly?

If so, you will have to build your SQL statement on the fly.

 

[ssecca26]

yes correct how can i build my sql to fly?

 

[FancyPrairie]

I'm assuming that you have the listboxes setup as I described in my previous post (i.e. lstArea, lstCP, Name). (Not necessary for my example below to work, but you would be able to see what's going on better.)

I also assume that you have a list box that contains the list "Section 1", "Section 2", "Section 3", etc. I'm going to assume that this listbox is named lstSection.

I'm also going to assume that the form that contains these controls is name "frmReportCriteria".

Set the Recordsource Property of the report to blanks (this is, you don't want a Recordsource). In the OnOpen event of the report, add the following code. Close and save the report. Now open up your form, select your criteria and launch the report like this:
Docmd.OpenReport "YourReportName",acPreview

The code in the OnOpen event will display a messagebox showing you what the Select statement looks like.

    Dim strWhere As String
    Dim varItem As Variant
    Dim strSQL As String
    Dim strSection As String
    Dim frm As Form
    
    Set frm = Forms!frmReportCriteria
    
    strSection = ""
    For Each varItem In frm!lstSection.ItemsSelected
        strSection = strSection & "[Weekly Report]." & frm!lstSection.ItemData(varItem) & ", [Weekly Report]." & frm!lstSection.ItemData(varItem) & "_Details, "
    Next varItem
        
    strSQL = "SELECT [Weekly Report].ID, [Weekly Report].Project_Week, [Weekly Report].Area, [Weekly Report].CP, [Weekly Report].Name "
    If (strSection <> "") Then
        strSection = Mid(strSection, 1, Len(strSection) - 2)
        strSQL = strSQL & ", " & strSection
    End If
    
    strSQL = strSQL & " FROM [Weekly Report] "
    strWhere = MMC_BuildWhere(frm)

    If (strWhere <> "") Then strSQL = strSQL & " WHERE " & strWhere

    MsgBox strSQL    
    Me.RecordSource = strSQL

 

[ssecca26]

i can still need the previous code i build in module right? i will just add this one. am i correct?

 

[FancyPrairie]

That is correct. But, I screwed up. In the code in the previous post I called the routine MMC_BuildWhere. Change that to BuildWhere instead of MMC_BuildWhere.

 

[ssecca26]

Dim strWhere As String
Dim varItem As Variant
Dim strSQL As String
Dim strSection As String
Dim frm As Form

Set frm = Forms!frmReportCriteria

strSection = ""
For Each varItem In frm!lstSection.ItemsSelected
strSection = strSection & "[Weekly Report]." & frm!lstSection.ItemData(varItem) & ", [Weekly Report]." & frm!lstSection.ItemData(varItem) & "_Details, "
Next varItem

strSQL = "SELECT [Weekly Report].ID, [Weekly Report].Project_Week, [Weekly Report].Area, [Weekly Report].CP, [Weekly Report].Name "
If (strSection <> "") Then
strSection = Mid(strSection, 1, Len(strSection) - 2)
strSQL = strSQL & ", " & strSection
End If

strSQL = strSQL & " FROM [Weekly Report] "
----- strWhere = MMC_BuildWhere(frm)

If (strWhere <> "") Then strSQL = strSQL & " WHERE " & strWhere

MsgBox strSQL
Me.RecordSource = strSQL


i got the error here (with ----) compile error Sub or function not defined.

 

[ssecca26]

i change it and a error pop up "The action or method is invalid the form or report isn't bound to a table or query

 

[FancyPrairie]

I don't know. It works for me. Did you place the code in the OnOpen event of the report?

 

[ssecca26]

yes i paste it in the onopen event.

 

[FancyPrairie]

Add the word Stop to the line above "Set frm = forms!frmReportCriteria". Now open the form and launch the report. The program will pause execution when it encounters the word Stop. You can then examine variables in the Debug Intermediate Window by typing ?variableName or use the Debug Watch window. To execute the code one line at a time, press F8. To continue execution of the code till it encounters the next stop or breakpoint or ends, press F5.

This should help you determine what the problem is.

 

[ssecca26]

really is not working,,, theres no other way?

 

[FancyPrairie]

I had a problem with Access so I wasn't able to open any reports. Consequently, I couldn't test the code in a report just simulated in a form. Got Access fixed so I could open a report, so I tested the code. Did not get the error message you did. However, if your report has a field for each of the Sections, then your query must also include them. So, I rewrote the query so that if a Section is not selected, "N/A" will be displayed in that column.

Replace your OnOpen event code with the following: Remember, this goes in the OnOpen event of the Report not the form.

Private Sub Report_Open(Cancel As Integer)

    Dim strWhere As String
    Dim strSQL As String
    Dim strSection As String
    Dim frm As Form
    Dim i As Integer
    
    Set frm = Forms!form1
    
    strSection = ""
    
    For i = 1 To 4  '4 represents the total number of sections
        If (frm!lstSection.Selected(i - 1)) Then
            strSection = strSection & "[Weekly Report].[" & frm!lstSection.ItemData(i - 1) & "], [Weekly Report].[" & frm!lstSection.ItemData(i - 1) & "_Details], "
        Else
            strSection = strSection & """N/A"" As [" & frm!lstSection.ItemData(i - 1) & "], ""N/A"" AS [" & frm!lstSection.ItemData(i - 1) & "_Details], "
        End If

    Next
    
    strSQL = "SELECT [Weekly Report].ID, [Weekly Report].Project_Week, [Weekly Report].Area, [Weekly Report].CP, [Weekly Report].Name "
    If (strSection <> "") Then
        strSection = Mid(strSection, 1, Len(strSection) - 2)
        strSQL = strSQL & ", " & strSection
    End If
    
    strSQL = strSQL & " FROM [Weekly Report] "
    strWhere = BuildWhere(frm)

    If (strWhere <> "") Then strSQL = strSQL & " WHERE " & strWhere

    MsgBox strSQL
    Me.RecordSource = strSQL
    
End Sub