Apply Filter to SubForm

[zashaikh]

Hello

I have a Keyword Search which applies a filter to my form. I am able to search through all of the text in the main form. However, how do I search through my subforms? The text below is the SQL code for my search function.

Private Sub btnSearchAll_Click()
Dim strCriteria As String

strCriteria = "FindAllWords(ARTICLE,""" & Me.txtAllKeywords & """)"



DoCmd.OpenForm "DatabaseSearch", _
WhereCondition:=strCriteria, _
WindowMode:=Normal
End Sub


DatabaseSearch is the name of the form I am opening. However, I have 5 subforms within this form.

txtAllKeywords is the text field where I enter the word I want to search.

ARTICLE is the name of the Field I am searching through. How do I specific that I want it to search through the Field of a Specific Table?

Also, can someone explain to be what the triple quotation marks mean after ARTICLE and txtAllkeywords? Or does it have something to do with the search module that I downloaded for this?

Thanks!

[zashaikh]

Also!!!

I have spaces in my field names (yes, I know that is bad). But when I am writing SQL, how do I write a field name that has a space.

For example, if I have Business Name, do I write "Business Name" or Business_Name, etc?

[andy49]

"[business name]"


Sent from my iPhone using Tapatalk

[zashaikh]

Do I include those quotations as well?

[andy49]

No the square brackets group the field name



Sent from my iPhone using Tapatalk

[Micron]

strCriteria = "FindAllWords(ARTICLE,""" & Me.txtAllKeywords & """)"
Also, can someone explain to be what the triple quotation marks mean after ARTICLE and txtAllkeywords? Or does it have something to do with the search module that I downloaded for this?

I would have thought that syntax would not work for I understood the syntax to be (when using this form of quotation)
strCriteria = "FindAllWords(ARTICLE,"""" & Me.txtAllKeywords & """")" as this might be explained by saying Access sees the second double quote and says, 'that's the end of the string - oh wait, here's a 3rd one so it's not. Then the fourth, which tells it that it IS the end, so what's left is the 3rd double quote. The result is that it's interpreted as (I'll substitute the field or control for a value) strCriteria = "FindAllWords(ARTICLE,"Invoicing")". I guess the way you've written it provides a double quote around the field value if you say it works. Often, it's written this way strCriteria = "FindAllWords(ARTICLE, '" & Me.txtAllKeywords & "')" to avoid confusion and accidentally dropping a double quote. In case you don't know, text values in expressions must be enclosed in either single or double quotes depending on the expression being created. Dates have their own delimiter (#). The reason I speculate on your method is that I only have ever used the latter method.

If the first answer solves your issue, great. If not, post back 'cause I don't know what FindAllWords is in Access 2016 (looks like a custom function to me) so I can't see how the answer will get you to filter your subforms . Hope it works for you though.

[zashaikh]

Thank you. Im not the one who created the code. So im having difficulty understanding it. I believe the code works off of a module. The code for the module is pasted below

Option Compare Database
Option Explicit

Public Function FindAnyWord(varFindIn, strWordList As String) As Boolean

Dim var
Dim aWords

aWords = Split(strWordList, ",")

For Each var In aWords
If FindWord(varFindIn, var) Then
FindAnyWord = True
Exit Function
End If
Next var

End Function
Public Function FindAllWords(varFindIn, strWordList As String) As Boolean

Dim var
Dim aWords

aWords = Split(strWordList, ",")

For Each var In aWords
If Not FindWord(varFindIn, var) Then
FindAllWords = False
Exit Function
Else
FindAllWords = True
End If
Next var

End Function
Public Function FindWord(varFindIn As Variant, varWord As Variant) As Boolean

Const PUNCLIST = """' .,?!:;(){}[]-—/"
Dim intPos As Integer

FindWord = False

If Not IsNull(varFindIn) And Not IsNull(varWord) Then
intPos = InStr(varFindIn, varWord)

' loop until no instances of sought substring found
Do While intPos > 0
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(varFindIn) = Len(varWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord), 1)) > 0 Then
FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0 Then
' is it at end of string or followed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord), 1)) > 0 Then
FindWord = True
Exit Function
End If
End If
End If

' remove characters up to end of first instance
' of sought substring before looping
varFindIn = Mid(varFindIn, intPos + 1)
intPos = InStr(varFindIn, varWord)
Loop
End If

End Function

[Micron]

FindAnyWord receives what I don't know (a memo textbox, a simple textbox - some sort of batch of words likely) AND a list of comma separated words to search for (strWordList). It splits the list into an array and passes each member of the array (a single word from the list) to the function FindWord in order to execute a search once for each list member. For each list member (word), that function looks for the word using a list of punctuation characters and returns either True (found) or False (not found) which makes the first function either true or false. What I don't see is an association that makes word1 found, word2 not found, word3 found, etc. as a 2 dimensional array, so the test seems to be "did it find ANY of the words from the list?". If not, each test will return false. What I don't see is what if the 2nd to last test was true and the last false? Looks to me like the answer you'd get is "none of the words were found" (false). I must be wrong about that! As for the 2nd function, it returns true if all the words in the list are found, else false. You haven't shown what calls either the first or second function, so that's about all I can tell youl
Hope that helps.