Filter subform with cobmo box columns, based on textbox value

[Ihussein]

Hello,
I’m using the following code to filter subform in main form, my subform source object is based on query, and there are some columns of the subform has relationship with another table, as you see in the below code it’s related to district table, and I’m using lookup to display district name, but in my vendor table it stores DistrictID which is a number. My problem is that below code is working with if I typed the DistrictID as number but not working if typed district name.
Any suggestion support please.
Regards

Private Sub txtKeyWord_Change()
' If there is no filter criteria, disable filtering '
If Len(Trim(txtKeyWord.Text)) = 0 Then
DS.Form.FilterOn = False
Exit Sub
End If
Dim strFilterDS As String
strFilterDS = "[VendorName] LIKE '*" & Trim$(txtKeyWord.Text) & "*'" _
& " or [District_FK] LIKE '*" & Trim$(txtKeyWord.Text) & "*' "
DS.Form.Filter = strFilterDS
DS.Form.FilterOn = True
End Sub

 

[arnelgp]

Private Sub txtKeyWord_Change()
' If there is no filter criteria, disable filtering '
If Len(Trim(txtKeyWord.Text)) = 0 Then
DS.Form.FilterOn = False
Exit Sub
End If
Dim strFilterDS As String
Dim strKeyword as string
strKeyword = Trim(Me.txtkeyword.Text)
If IsNumeric(strkeyworkd) Then
strFilterDS = "[District_FK] LIKE '*" & strKeyWord & "*'"
Else
strFilterDS = "[VendorName] LIKE '*" & strKeyWord & "*'"
End If
DS.Form.Filter = strFilterDS
DS.Form.FilterOn = True
End Sub

 

[Ihussein]

Thanks arnelgp for your prompt reply, but the code you have posted is giving the same result the code that I my code above.
Here are more details and clarifications on my current case.
Districts Table:
District DistrictName
1 Ankwa
2 Anlmi
3 Barca
my Vendor table has one to many relationship with District table District_FK as number field data type.
Vendors Table:
VendorID VendorName District_FK ……etc
0124 Adams 2
0125 Addlink 1
0126 Mark 2
0127 Narim 3
All the way until the very last record
To be more user friendly, I has set lookup property to District_FK column, which it’s displaying the DistrictName but not numbers, “but still the original data of District_FK column are stored as numbers”
Vendors Table after lookup:
VendorID VendorName District_FK ……etc
0124 Adams Anlmi
0125 Addlink Ankwa
0126 Mark Anlmi
0127 Narim Barca
Now the issue is. If I started typing DistrictName as letters in txtKeyWord it not filtering the subform. But if I typed District number it’s filtering the sub form. I don’t want to filter the sub form by District but to be able to filter by DistrictName.
Hope it clarifies
Regards

 

[arnelgp]

what is the "real" tablename of your vendor?

 

[arnelgp]

Private Sub txtKeyWord_Change()
    ' If there is no filter criteria, disable filtering '
    If Len(Trim(txtkeyword.Text)) = 0 Then
        DS.Form.FilterOn = False
        Exit Sub
    End If
    Dim strFilterDS As String
    Dim strKeyword As String
    Dim strVendor As String
    strKeyword = Trim(Me.txtkeyword.Text)
    If IsNumeric(strkeyworkd) Then
        strFilterDS = "[District_FK] LIKE '*" & strKeyword & "*'"
    Else
        strVendor = Nz(DLookup("[COLOR=Blue]VendorID[/COLOR]", "[COLOR=Blue]Vendor[/COLOR]", "[COLOR=Blue]VendorName[/COLOR] Like '*" & strKeyword & "*'"), "9999")
        strFilterDS = "[VendorName] LIKE '*" & strVendor & "*'"
    End If
    DS.Form.Filter = strFilterDS
    DS.Form.FilterOn = True
End Sub

replace the blue-colored text with correct fieldname, table name.

 

[Ihussein]

Still not filtering by DistrictName
By the way the If (Condition) has Syntax error not same as per the defined variable. I have corrected it but still not achieving the required result.

Once a gain thanks for the initiative

 

[Ihussein]

Thanks arnelgp it works like nothing, I was just confused with Dlookup
Apology for the inconvenience made
Regards