I belong to a music club and we have a database of all the music.
The relevant table is Music, with MusicID as the primary key and Title (other fields are irrelevant).
We have to put together a themed music program, and next months theme is royalty. So I want to search for words like "Emperor", "Royal", "King", "Elizabethan", "Tsar". anywhere in the title. This needs to be done in one pass rather than searching for each word separat
My mind is a comparative blank on where to start. My thoughts are to create a temporary table using all the words in all the titles (no duplicates) and use that a s source for a very very long multi-select list box.
Then where?
Any thoughts gratefully received.
Phil
This looked fun, I came up with this. I think it's close to what you are looking for: - Private Sub txtKeywords_Change()
-
-
Dim sKeywords As String
-
Dim sKeyword As Variant
-
Dim lCurrentSelStart As Long
-
Dim sSQL As String
-
-
lCurrentSelStart = Me.txtKeywords.SelStart
-
sKeywords = Nz(Me.txtKeywords.Text, "")
-
-
' Get Filter
-
For Each sKeyword In Split(sKeywords, " ")
-
If Len(Trim(sKeyword)) > 0 Then sSQL = sSQL & "Title LIKE '*" & sKeyword & "*' OR "
-
Next sKeyword
-
If Len(sSQL) > 0 Then sSQL = Left(sSQL, Len(sSQL) - 4)
-
-
' Apply Filter
-
If Len(sSQL) > 0 Then
-
Me.Filter = sSQL
-
Me.FilterOn = True
-
Else
-
Me.FilterOn = False
-
End If
-
-
' reset txtKeywords back to where it was
-
Me.txtKeywords.SetFocus
-
Me.txtKeywords.Value = sKeywords
-
Me.txtKeywords.SelStart = lCurrentSelStart
-
-
End Sub
In the above, the keywords are split on a space, which you could replace the space in the For Each with a ~ (Tilde) and it would do what you are talking about. Personally, I like the space to split as it's more intuitive, but using a ~ would be more powerful.
10 2341 NeoPa 32,470
Expert Mod 16PB
Start with a table of words you want found - [tblWord].[Word]. - SELECT DISTINCT
-
[Music].*
-
FROM [Music]
-
, [tblWord]
-
WHERE ([Music].[Title] Like '*' & [tblWord].[Word] '*')
NeoPa 32,470
Expert Mod 16PB
On a separate point, please check your PMs and let me have a reply when you can Phil.
I'm curious why you think you need to use a table other than the Music Table?
You could create a continuous Form based on the Music Table. Then drop on a textbox where the user types in a space separated list of keywords. Then either on a button press or losing focus on the Keyword textbox, you could split the keywords and build a Where Clause and apply it to the Forms Filter.
Basically the user would enter: - Emperor Royal King Elizabethan Tsar
and your code would create this: - Title LIKE '*Emperor*' OR Title LIKE '*Royal*' OR Title LIKE '*King*' OR Title LIKE '*Elizabethan*' OR Title LIKE '*Tsar*'
You're absolutely right, I don't need that table.
At the moment, I have a routine which filters as you type each letter -
Private Sub TxtFilter_Change()
-
-
'Title like "*roy*"OR Title Like "*bl*"
-
-
Dim Fltr As String
-
-
' If we return no records, tempotarily remove the filter
-
If Me.RecordsetClone.RecordCount = 0 Then
-
Me.FilterOn = False
-
End If
-
-
TxtFilter.SetFocus
-
-
If Nz(Me.TxtFilter.Text) = "" Then
-
Me.Form.Filter = ""
-
Me.FilterOn = False
-
' If a partial value is typed, filter for a partial company name match.
-
Else
-
If Right(TxtFilter.Text, 1) <> "~" Then ' Last letter typed not a tilde
-
' FilterStg = filterst & Right(TxtFilter, 1)
-
Fltr = Fltr & "Title Like '*" & Replace(Me.TxtFilter.Text, "'", "''") & "*'"
-
End If
-
-
Me.Form.Filter = Fltr
-
Me.FilterOn = True
-
End If
-
-
Me.TxtFilter.SetFocus
-
-
' Move the cursor to the end of the combo box.
-
If Me.RecordsetClone.RecordCount = 0 Then
-
Exit Sub
-
End If
-
-
Me.TxtFilter.SelStart = Len(Me.TxtFilter.Text)
-
-
End Sub
This works perfectly.
What I want to do now is having typed in the first few letters of the first word I am looking for, type a tilde (~) to start adding a further word. This has got to create an additional
OR "Title Like '*" & Next Word, "'", "''") & "*'"
Haven't really had time to have a go at this, but it seems tricky.
Phil
This looked fun, I came up with this. I think it's close to what you are looking for: - Private Sub txtKeywords_Change()
-
-
Dim sKeywords As String
-
Dim sKeyword As Variant
-
Dim lCurrentSelStart As Long
-
Dim sSQL As String
-
-
lCurrentSelStart = Me.txtKeywords.SelStart
-
sKeywords = Nz(Me.txtKeywords.Text, "")
-
-
' Get Filter
-
For Each sKeyword In Split(sKeywords, " ")
-
If Len(Trim(sKeyword)) > 0 Then sSQL = sSQL & "Title LIKE '*" & sKeyword & "*' OR "
-
Next sKeyword
-
If Len(sSQL) > 0 Then sSQL = Left(sSQL, Len(sSQL) - 4)
-
-
' Apply Filter
-
If Len(sSQL) > 0 Then
-
Me.Filter = sSQL
-
Me.FilterOn = True
-
Else
-
Me.FilterOn = False
-
End If
-
-
' reset txtKeywords back to where it was
-
Me.txtKeywords.SetFocus
-
Me.txtKeywords.Value = sKeywords
-
Me.txtKeywords.SelStart = lCurrentSelStart
-
-
End Sub
In the above, the keywords are split on a space, which you could replace the space in the For Each with a ~ (Tilde) and it would do what you are talking about. Personally, I like the space to split as it's more intuitive, but using a ~ would be more powerful.
Thanks for that, that should work, but that is working on complete words. The code that I outlined is rather fun because, as you type each letter in the TxtFilter search box, a finer & finer filter is applied.
So with the TxtFilter blank there are 2639 record.
On typing "r", not surprisingly it drops to 2130 records (Lots of music titles have an "r" in them. "ro" pulls it down to 266 and "roy" down to 2 copies of Handel's Music for the Royal Fireworks and Berlioz Royal Hunt & Storm (Trojans).
What I expect to happen is that I then enter a tilde in the TxtFilter and all 2639 records show. as I then type in a "k", the three records already mentions + any titles with a "k" in the title should show. By the time I have typed in "rot~ki" I would expect to see only records with "royal" and "king" it the title.
Phil
NeoPa 32,470
Expert Mod 16PB
I suspect you may have misunderstood the post a bit there Phil.
As JForbes explains below the code, his approach uses a space rather than a tilde, but that's easily changed by changing line #12 to use "~" instead of " " . Otherwise it seems to do just what you've specified.
I would suggest that much of the code is unnecessary. The .Value needn't be changed/set - which is never a good idea within a Control_Change() procedure anyway. Essentially, the code shouldn't change anything about the control just by setting the form's filter, so the code to set it back is not required.
I'm afraid your requirement to handle this within a form and that the words be entered on the form, wasn't clear originally so my suggestion wasn't based on that. However, JForbes' code seems to do the job pretty well I suspect.
Thanks for all your help. My original idea uf searching for words from a combo boc showing a list of all the words in the titles was useless as there were over 4000 words.
Problem is now solved and in addition there is an extra filter for Media Type (Disc, Vinyl, Tape 78s - I go back a long way).
So on changing the TxtFilter or Updating the SelectMediaType Combo I run -
Sub FilterForm()
-
-
'MediaTypeID = 1 AND (Title Like '*roy*' OR Title Like '*kin*')
-
-
Dim Fltr As String
-
-
If SelectMediaType > 0 Then ' Media Type not all
-
Fltr = "MediaTypeID = " & SelectMediaType
-
End If
-
-
' If we return no records, tempotarily remove the filter
-
If Me.RecordsetClone.RecordCount = 0 Then
-
Me.FilterOn = False
-
End If
-
-
TxtFilter.SetFocus
-
-
If Left(TxtFilter.Text, 1) = "~" Then ' Don't allow a tilde as first letter
-
TxtFilter.Text = ""
-
End If
-
-
If Fltr > "" Then ' Media Type selected
-
Fltr = Fltr & " AND "
-
End If
-
Fltr = Fltr & "(Title Like '*" & Replace(Me.TxtFilter.Text, "'", "''") & "*'"
-
Fltr = Replace(Fltr, "~", "*' OR Title Like '*")
-
Fltr = Fltr & ")"
-
Me.Form.Filter = Fltr
-
Me.FilterOn = True
-
-
-
Me.TxtFilter.SetFocus
-
-
' Move the cursor to the end of the input text box.
-
If Me.RecordsetClone.RecordCount = 0 Then
-
Exit Sub
-
End If
-
-
Me.TxtFilter.SelStart = Len(Me.TxtFilter.Text)
-
-
End Sub
-
Works perfectly and surprisingly fast.
Phil
Glad you got it working Phil. Keep posting these fun challenges. =)
NeoPa,
I didn't expect it, but changing the Filter of the Form while inside the OnChange Event causes the search TextBox to fire off it's Before and AfterUpdate events and the entirety of the text of the TextBox becomes selected. I'm not sure, but I think the Form is firing a Refresh. So that last bit of code to SetFocus and update the selected text seems necessary. I didn't like it, but Access would act all crazy without it.
NeoPa 32,470
Expert Mod 16PB
I suppose I should have guessed you knew better than to include it for no reason. I stand corrected :-)
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
6 posts
views
Thread by Keith |
last post: by
|
3 posts
views
Thread by Trevor |
last post: by
|
4 posts
views
Thread by Axel |
last post: by
|
1 post
views
Thread by Brian |
last post: by
| |
1 post
views
Thread by HYRY |
last post: by
|
35 posts
views
Thread by shapper |
last post: by
| | | | | | | | | | | | | |