tinyevil777
Registered User.
- Local time
- Today, 13:43
- Joined
- Dec 10, 2010
- Messages
- 137
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.
Any help or suggestions for better practice would be greatly appreciated!
Below is my code in its intirety:
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