Filtering related data on a subform (1 Viewer)

tinyevil777 · Aug 3, 2012

Morning all, hope everyone is dandy.

I've implemented some code taken from this old post http://www.access-programmers.co.uk/forums/showthread.php?t=121662

I am using Access 2007, and I have this working fine, however I now want to filter on a field that is related to the data in the subform, and then display the relevant records.

Below is a snapshot of the relevant fields in my two tables:

Table name: POHeads
ID
OrderNo
Supplier
OldID

Table name: POLines
ID
POHeadsID
PartDesc

Currently, I have 3 unbound combo boxes filtering on OrderNo, Supplier and OldID from the POHeads. The subform returns data from POHeads only.

I now want an unbound text box where I can enter a part descrption, and it will return all data from POHeads, where the part number entered in the text box is LIKE a part number in POLines.ItemDesc where the POHeads.ID = POLines.POHeadsID.

In my code below, my attempt doesn't filter anything. The event AfterUpdate is set, but the data that is returned is exactly the same.

Code:

If Nz(Me.SrchDescription, "<All>") > "<All>" Then  'POLines Description
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "[POLines].[ItemDesc] = '" & Me.SrchDescription & "'"
    bFilter = True
    End If

Any help or suggestions for better practice would be greatly appreciated!

Below is my code in its intirety:

Code:

Private Sub Form_Load()
Call RunFilter
End Sub

Private Sub RunFilter()

    Dim strFilter       As String
    Dim bFilter         As Boolean

bFilter = False
strFilter = ""
    

If Nz(Me.SrchOrderNo, "<All>") > "<All>" Then   'Order Number
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "OrderNo = '" & Me.SrchOrderNo & "'"
    bFilter = True
    End If
    
    
If Nz(Me.SrchOldOrderNo, "<All>") > "<All>" Then    'Old Order Number
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "OldID = '" & Me.SrchOldOrderNo & "'"
    bFilter = True
    End If
    
If Nz(Me.SrchProjectNo, "<All>") > "<All>" Then  'Project Number
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "PrjNo = '" & Me.SrchProjectNo & "'"
    bFilter = True
    End If
    
If Nz(Me.SrchSupplier, "<All>") > "<All>" Then  'Supplier
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "Supplier = '" & Me.SrchSupplier & "'"
    bFilter = True
    End If
    
If Nz(Me.SrchDescription, "<All>") > "<All>" Then  'POLines Description
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "[POLines].[ItemDesc] = '" & Me.SrchDescription & "'"
    bFilter = True
    End If
    
If bFilter Then
    Me.[POList-SF].Form.OrderBy = ""
    Me.[POList-SF].Form.Filter = strFilter
    Me.[POList-SF].Form.FilterOn = True
    Else
    Me.[POList-SF].Form.FilterOn = False
    End If

End Sub

Private Sub SrchDescrption_AfterUpdate()
Call RunFilter
End Sub


Private Sub SrchOldOrderNo_AfterUpdate()
Call RunFilter
End Sub

Private Sub SrchOrderNo_AfterUpdate()
Call RunFilter
End Sub

Private Sub SrchProjectNo_AfterUpdate()
Call RunFilter
End Sub

Private Sub SrchSupplier_AfterUpdate()
Call RunFilter
End Sub

tinyevil777 · Aug 3, 2012

Also, if it's any use, below is the Record Source for the subform:

Code:

SELECT DISTINCT dbo_POHeads.ID, dbo_POHeads.OrderNo, dbo_POHeads.Supplier, dbo_POHeads.OLDID, dbo_POHeads.Removed
FROM dbo_POHeads LEFT JOIN dbo_POLines ON dbo_POHeads.ID = dbo_POLines.POHeadID
WHERE (((dbo_POHeads.Removed) Is Null))
ORDER BY dbo_POHeads.OrderNo DESC;

tinyevil777 · Aug 3, 2012

Any help on this whatsoever would be greatly appreciated... Thank you!

Filtering related data on a subform (1 Viewer)

tinyevil777

Registered User.

tinyevil777

Registered User.

tinyevil777

Registered User.

Similar threads

Users who are viewing this thread