Hello, Sorry for Posting another topic about my same (docmanager) database, but this is a seperate problem I need some help with. This database so far has some code written to search for articles based on text. And based on other parameters.
There s code that has already been written so I should need to use this code as basic. But it is kind of a head scratcher:
Code:
Private Sub Knop12_Click()
Dim strSQL As String
Dim strFilter As String
Dim rstX As DAO.Recordset
Dim strKeywords As String
Dim blnFilter As Boolean
Dim i As Integer
Dim y As Variant
Dim strValue1 As String
Dim strValue2 As String
Dim strValue3 As String
Dim strValue4 As String
Dim strValue5 As String
Dim strValue6 As String
On Error GoTo Err_Knop12_Click
blnFilter = False
strFilter = "1=1"
If chkKeyword.Value = 0 And LenB(txtKeyWords.Value) > 0 Then
strFilter = strFilter & "ID IN (SELECT ID_ARTICLE FROM ARTICLE_KEYWORDS WHERE ID_KEYWORD IN (select ID FROM KEYWORDS WHERE FILTER_CHECK = TRUE))"
blnFilter = True
End If
If LenB(txtArticleName.Value) > 0 And chkArticleName.Value <> 0 Then
y = Split(LCase$(txtArticleName.Value), " ")
'strValue = vbn
'For i = LBound(y) To UBound(y)
' https://stackoverflow.com/questions/19780016/vba-write-all-possible-combinations-of-4-columns-of-data
' strValue = strValue & y(i)
'Next i
Select Case UBound(y)
Case 1
strValue1 = LCase$(Replace$(txtArticleName.Value, " ", "*"))
strValue2 = y(1) & "*" & y(0)
strFilter = strFilter & "AND (ARTICLE_NAME LIKE '*" & strValue1 & "*' OR ARTICLE_PATH LIKE '*" & strValue1 & _
"*' OR ARTICLE_NAME LIKE '*" & strValue2 & "*' OR ARTICLE_PATH LIKE '*" & strValue2 & "*')"
Case 2
strValue1 = LCase$(Replace$(txtArticleName.Value, " ", "*"))
strValue2 = y(2) & "*" & y(1) & "*" & y(0)
strValue3 = y(2) & "*" & y(0) & "*" & y(1)
strValue4 = y(1) & "*" & y(2) & "*" & y(0)
strValue5 = y(1) & "*" & y(0) & "*" & y(2)
strValue6 = y(0) & "*" & y(2) & "*" & y(1)
strFilter = strFilter & "AND (ARTICLE_NAME LIKE '*" & strValue1 & "*' OR ARTICLE_PATH LIKE '*" & strValue1 & _
"*' OR ARTICLE_NAME LIKE '*" & strValue2 & "*' OR ARTICLE_PATH LIKE '*" & strValue2 & _
"*' OR ARTICLE_NAME LIKE '*" & strValue3 & "*' OR ARTICLE_PATH LIKE '*" & strValue3 & _
"*' OR ARTICLE_NAME LIKE '*" & strValue4 & "*' OR ARTICLE_PATH LIKE '*" & strValue4 & _
"*' OR ARTICLE_NAME LIKE '*" & strValue5 & "*' OR ARTICLE_PATH LIKE '*" & strValue5 & _
"*' OR ARTICLE_NAME LIKE '*" & strValue6 & "*' OR ARTICLE_PATH LIKE '*" & strValue6 & _
"*')"
Case Else
strFilter = strFilter & "AND (ARTICLE_NAME LIKE '*" & LCase$(Replace$(txtArticleName.Value, " ", "*")) & _
"*' OR ARTICLE_PATH LIKE '*" & LCase$(Replace$(txtArticleName.Value, " ", "*")) & "*')"
End Select
blnFilter = True
End If
If chkBib.Value = 0 Then
strFilter = strFilter & " AND ARTICLE_PATH NOT LIKE 'BIB (*'"
blnFilter = True
End If
If chkBibGem.Value = 0 Then
strFilter = strFilter & " AND ARTICLE_PATH NOT LIKE 'GEM (*'"
blnFilter = True
End If
Set rstX = CurrentDb.OpenRecordset("SELECT FOLDER_PATH FROM ADMIN_FOLDERS WHERE FILTER_CHECK2 = FALSE")
With rstX
While Not .EOF
If .Fields("FOLDER_PATH") = getParam("FTN_ROOT_DIR") Then
strFilter = strFilter & " AND ARTICLE_PATH NOT LIKE '*" & .Fields("FOLDER_PATH") & "*' AND ARTICLE_PATH NOT LIKE 'MAG (*' AND ARTICLE_PATH NOT LIKE '*PUBLICATIES_SCANS*'"
Else
strFilter = strFilter & " AND ARTICLE_PATH NOT LIKE '*" & .Fields("FOLDER_PATH") & "*'"
End If
blnFilter = True
.MoveNext
Wend
.Close
End With
If blnFilter Then
Me.FRM_ARTICLES_2.Form.Filter = strFilter
gstrFilter = Me.FRM_ARTICLES_2.Form.Filter
Me.FRM_ARTICLES_2.Form.FilterOn = True
Else
Me.FRM_ARTICLES_2.Form.FilterOn = False
End If
Exit_Knop12_Click:
Exit Sub
Err_Knop12_Click:
MsgBox Err.Description
Resume Exit_Knop12_Click
End Sub
The code that is in bold is the code that represent when certain text has been searched however this is the code I actually want to expand.
Want I want to do is making the searching more specific based on what file extension is in the text.
So this code parameter:
Code:
If LenB(txtArticleName.Value) > 0 And chkArticleName.Value <> 0 Then
Should be:
Code:
(txtbestandextensie.Value) > 0 and chkbestandextensie.Value <> 0 Then
Now this choice list has a querry as a basis for the selection and what has to happen is the following:
Code:
Search like "File Extension choice"
so for example when I select the .pdf extension what actually has to happen is: Search like ".pdf".
Code:
ARTICLE_NAME LIKE ".pdf"
Here is the Querry and how it looks like in the form.
Querry:
Form:
Also small side note: For some unknown reason the top row also gets deleted "sometimes" when I boot up the database: why this happens I have no clue: So if someone can explain and point out why this is. Let me know.
So what I know so far is that the I need to use the strFilter but so far I do not know how to write this based on a list.