Working witth three listboxes for report criteria

[ugenya]

I am working on a report that that is linked to four tables.
They all use auto ID but the also have some unique data.
Project table - has Pcode
Ptype table - has typeCode
Methods table - has methCode
Method Details table - MethDetail

Project is linked to PType table using auto ID PType table is linked to Method table via an auto ID
Meth Details table is linked to Method via auto id

I have a query that joins these table --used the query wizard and I have used this query to design a report called Methods Report.
Now, I want to create 3 list boxes as follows:
First listbox is for filtering PCode from project table
Second listbox is for filtering methCode from the Methods table
Third listbox is for filtering a 'yes' or 'no' from the pType table

The goal is to select one or more projects, then select one or more methCode and then select a yes or no from pType table. once all these criterion are selected, user then clicks preview to view report.

I am at a lose on how to do this. Can you please assist. I was able get one listbox to preview the report but I am not able to get all three listbox selections to work.


Thank you so much in advance.

Bibi

 

[LisaWay]

The following assumes that you named your listboxes "Pcode", "typeCode", and "methCode", you named your pushbutton "CmdPreview", and that you named your report "MyReport" which is based on a query named "MyQuery". You will probably need to adjust the query so that it matches your table structure. I did the best I could.

Attach the following code to the OnClick event of button "CmdPreview":

Private Sub CmdPreview_Click()

    Dim qry As QueryDef
    
    ' Modify the query definition.
    Set qry = CurrentDb.QueryDefs![MyQuery]

    qry.SQL = "SELECT Project.Pcode, Ptype.typeCode, Methods.methCode, [Method Details].MethDetail" & _
        " FROM (([Method Details] INNER JOIN Methods ON [Method Details].ID = Methods.methCode) INNER JOIN Ptype ON Methods.ID = Ptype.typeCode) INNER JOIN Project ON Ptype.ID = Project.Pcode" & _
        " WHERE (((Project.Pcode) In (" & GetList("Pcode") & ")) AND ((Ptype.typeCode) In (" & GetList("typeCode") & ")) AND ((Methods.methCode) In (" & GetList("methCode") & ")));"
    
    qry.Close
    
    ' Preview the report.
    DoCmd.OpenReport "MyReport", acViewPreview
    
End Sub

You will also need the following routine in your form:

Private Function GetList(cntlName) As String

    Dim varItm As Variant, intI As Integer
    Dim myStr As String

    For Each varItm In Me(cntlName).ItemsSelected
        myStr = myStr & Me(cntlName).Column(1, varItm) & ","
    Next varItm
    
    myStr = Left(myStr, Len(myStr) - 1)
    GetList = myStr
    
End Function

 

[ugenya]

Thanks LisaWay. I see one problem already with my query. I will modify query and test again.
Thanks a bunch!