Using a subtable for criteria to a main table report

Here’s the scenario:

You have 2 related tables:  Projects and Machinery.  You wish to see a listing of projects where the location of the Machinery is in China.  Yet there are multiple machinery items in multiple locations for Projects, and you only wish to see a unique listing of projects.

For instance, in the example below, there are two pieces of machinery for ‘Project A’.  MachineryID 1 is in China and MachineryID 2 is in Africa.  Both of these are related to ‘Project A’.

The situation:

DCPost715

The solution:

Use the ‘In’ Clause to create a subquery in the filter for the report.  This sounds complex but is actually quite simple once you get the syntax correct of the In Clause.  Here’s how I did this:

strFilter = strFilter & ” AND ProjectID In (SELECT ProjectID FROM tbl_Machinery WHERE MachineryLocation = ‘” & cboMachineryLocation & “‘)”

Here’s the complete code for the preview button above.

Private Sub cmdPreview_Click()

Dim strFilter As String

If Not IsNull(cboProjectType) Then

strFilter = ” AND ProjectType = ‘” & cboProjectType & “‘”

End If

If Not IsNull(cboMachineryLocation) Then

strFilter = strFilter & ” AND ProjectID In (SELECT ProjectID FROM tbl_Machinery WHERE MachineryLocation = ‘” & cboMachineryLocation & “‘)”

End If

If Len(strFilter) > 5 Then

strFilter = Right(strFilter, Len(strFilter) – 5)

End If

DoCmd.OpenReport “rpt10Project Listing”, acViewPreview, , strFilter

End Sub

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *