Searching a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to take the words typed into a field on a form, search for all of them
in a field on a table then return a list of records (where any of the words
match) as a list so that the required record can be selected (like a combo
box).
Can this be done? If so, can anyone help?
 
You could filter a form so that it showed only the records that contain at
least one of the words.

This example assumes your form has an unbound text box named txtKeywords.
The user types the words, with a space between them. When they move
somewhere else, the form is filtered so that the only records shown are
those that have one of these words in a field named Notes.

It saves any edits before applying the filter. If the user clears the box
(or types nothing but spaces), the form shows all records again.

The code works by splitting the words into a variant array, and then looping
through the array elements to build up the Where string, adding OR to each
one. It then chops off the trailing OR, and applies it to the form's filter.
The current version of Access copes with up to 99 ANDs in the WHERE clause,
so the code checks the user did not type more than 99 words.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & _
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
 
Thank you for trying to help.
Is it possible to use some form of this filter directly with my combo box?
Currently I simply have a combo that allows me to select a record from a
table then displays certain fields on my form for the single record - that
works fine. This though is not good enough as many variations of the same
words may be used to identify a single record so what I want to do is take
the words typed in to the text box and use those as the search criteria
(filter?) for my combo so I just get the matching records to select from in
my drop down list, not all the records on the table.
Is this possible?

Allen Browne said:
You could filter a form so that it showed only the records that contain at
least one of the words.

This example assumes your form has an unbound text box named txtKeywords.
The user types the words, with a space between them. When they move
somewhere else, the form is filtered so that the only records shown are
those that have one of these words in a field named Notes.

It saves any edits before applying the filter. If the user clears the box
(or types nothing but spaces), the form shows all records again.

The code works by splitting the words into a variant array, and then looping
through the array elements to build up the Where string, adding OR to each
one. It then chops off the trailing OR, and applies it to the form's filter.
The current version of Access copes with up to 99 ANDs in the WHERE clause,
so the code checks the user did not type more than 99 words.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & _
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

kbrad said:
I want to take the words typed into a field on a form, search for all of
them
in a field on a table then return a list of records (where any of the
words
match) as a list so that the required record can be selected (like a combo
box).
Can this be done? If so, can anyone help?
Click to expand...
Click to expand...
 
So you have a combo for navigating to a record, and you also want to filter
the combo in the same way as the form?

Once you get the filter for the form working, you can also use the WHERE
string you created in the RowSource of your combo. For example, if Combo1
currently has Table1 as its RowSource, and Table1 also has the keywords,
your code would use:
Me.Combo1.RowSource = "SELECT DISTINCT Table1.ID FROM Table1 WHERE " &
Left(strWhere, lngLen) & ";"

If you are not sure how to generate this SQL statement, or if it needs
multiple tables, you could mock up a query and then switch it to SQL View to
see an example of what you need in your string.

Don't forget the DISTINCT: you can use that to de-dupe the combo's values
for the cases where a record contains more than one of the keywords.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

kbrad said:
Thank you for trying to help.
Is it possible to use some form of this filter directly with my combo box?
Currently I simply have a combo that allows me to select a record from a
table then displays certain fields on my form for the single record - that
works fine. This though is not good enough as many variations of the same
words may be used to identify a single record so what I want to do is take
the words typed in to the text box and use those as the search criteria
(filter?) for my combo so I just get the matching records to select from
in
my drop down list, not all the records on the table.
Is this possible?

Allen Browne said:
You could filter a form so that it showed only the records that contain
at
least one of the words.

This example assumes your form has an unbound text box named txtKeywords.
The user types the words, with a space between them. When they move
somewhere else, the form is filtered so that the only records shown are
those that have one of these words in a field named Notes.

It saves any edits before applying the filter. If the user clears the box
(or types nothing but spaces), the form shows all records again.

The code works by splitting the words into a variant array, and then
looping
through the array elements to build up the Where string, adding OR to
each
one. It then chops off the trailing OR, and applies it to the form's
filter.
The current version of Access copes with up to 99 ANDs in the WHERE
clause,
so the code checks the user did not type more than 99 words.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & _
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


kbrad said:
I want to take the words typed into a field on a form, search for all of
them
in a field on a table then return a list of records (where any of the
words
match) as a list so that the required record can be selected (like a
combo
box).
Can this be done? If so, can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
I think you're going to have to spell this out for me! I don't get it.
At the moment there is no Record Source at form level the fields on my form
are all unbound and are populated by selecting from the combo box list. So I
need to apply what you're trying to explain to me solely to the record source
of the combo field.

I've tried to do it but am getting an enter Parameter Value error on StrWhere.

Ideally, I'd like to be able to type into the combo box and to use the
NotInList event to search for the words entered when what is typed is not
found. For example, if "machine type A" is selectable via the combo as those
words are in the field on the table, as soon as you type "mac" it and others
will appear so I can select the right one. But if it is typed as "Type A
machine" the NotInList event will be triggered. This is when I want it to
search for each word separately and for the combo to give me just a list of
records with these words in them like "machine A", "engine type A" and the
one I want "machine type A" so I can again select the one I want.
Maybe this is asking too much and I need to use the separate keywords input
field to set up the combo? But I don't know how to that either.

Are you able to help further as I'm getting in a right mess with this!!
Thanks

Allen Browne said:
So you have a combo for navigating to a record, and you also want to filter
the combo in the same way as the form?

Once you get the filter for the form working, you can also use the WHERE
string you created in the RowSource of your combo. For example, if Combo1
currently has Table1 as its RowSource, and Table1 also has the keywords,
your code would use:
Me.Combo1.RowSource = "SELECT DISTINCT Table1.ID FROM Table1 WHERE " &
Left(strWhere, lngLen) & ";"

If you are not sure how to generate this SQL statement, or if it needs
multiple tables, you could mock up a query and then switch it to SQL View to
see an example of what you need in your string.

Don't forget the DISTINCT: you can use that to de-dupe the combo's values
for the cases where a record contains more than one of the keywords.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

kbrad said:
Thank you for trying to help.
Is it possible to use some form of this filter directly with my combo box?
Currently I simply have a combo that allows me to select a record from a
table then displays certain fields on my form for the single record - that
works fine. This though is not good enough as many variations of the same
words may be used to identify a single record so what I want to do is take
the words typed in to the text box and use those as the search criteria
(filter?) for my combo so I just get the matching records to select from
in
my drop down list, not all the records on the table.
Is this possible?

Allen Browne said:
You could filter a form so that it showed only the records that contain
at
least one of the words.

This example assumes your form has an unbound text box named txtKeywords.
The user types the words, with a space between them. When they move
somewhere else, the form is filtered so that the only records shown are
those that have one of these words in a field named Notes.

It saves any edits before applying the filter. If the user clears the box
(or types nothing but spaces), the form shows all records again.

The code works by splitting the words into a variant array, and then
looping
through the array elements to build up the Where string, adding OR to
each
one. It then chops off the trailing OR, and applies it to the form's
filter.
The current version of Access copes with up to 99 ANDs in the WHERE
clause,
so the code checks the user did not type more than 99 words.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & _
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


I want to take the words typed into a field on a form, search for all of
them
in a field on a table then return a list of records (where any of the
words
match) as a list so that the required record can be selected (like a
combo
box).
Can this be done? If so, can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Your original post explained that you wanted to:
return a list of records
in response to some search criteria.

I suggest you use a bound form in continuous view as a way to return a list
of records. You can set the Filter property of the form, so it only matches
the records you want. You will need to get this part working before you move
on to the question of how to select one of the returned records.

If you know nothing about Access or VBA, you might use the Tabular Form
Wizard to create a form that shows all the records from your table. Then
check help on Filter By Form to see how you can enter criteria to filter the
form to just those matching the word(s) you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

kbrad said:
I think you're going to have to spell this out for me! I don't get it.
At the moment there is no Record Source at form level the fields on my
form
are all unbound and are populated by selecting from the combo box list. So
I
need to apply what you're trying to explain to me solely to the record
source
of the combo field.

I've tried to do it but am getting an enter Parameter Value error on
StrWhere.

Ideally, I'd like to be able to type into the combo box and to use the
NotInList event to search for the words entered when what is typed is not
found. For example, if "machine type A" is selectable via the combo as
those
words are in the field on the table, as soon as you type "mac" it and
others
will appear so I can select the right one. But if it is typed as "Type A
machine" the NotInList event will be triggered. This is when I want it to
search for each word separately and for the combo to give me just a list
of
records with these words in them like "machine A", "engine type A" and the
one I want "machine type A" so I can again select the one I want.
Maybe this is asking too much and I need to use the separate keywords
input
field to set up the combo? But I don't know how to that either.

Are you able to help further as I'm getting in a right mess with this!!
Thanks

Allen Browne said:
So you have a combo for navigating to a record, and you also want to
filter
the combo in the same way as the form?

Once you get the filter for the form working, you can also use the WHERE
string you created in the RowSource of your combo. For example, if Combo1
currently has Table1 as its RowSource, and Table1 also has the keywords,
your code would use:
Me.Combo1.RowSource = "SELECT DISTINCT Table1.ID FROM Table1 WHERE "
&
Left(strWhere, lngLen) & ";"

If you are not sure how to generate this SQL statement, or if it needs
multiple tables, you could mock up a query and then switch it to SQL View
to
see an example of what you need in your string.

Don't forget the DISTINCT: you can use that to de-dupe the combo's values
for the cases where a record contains more than one of the keywords.

kbrad said:
Thank you for trying to help.
Is it possible to use some form of this filter directly with my combo
box?
Currently I simply have a combo that allows me to select a record from
a
table then displays certain fields on my form for the single record -
that
works fine. This though is not good enough as many variations of the
same
words may be used to identify a single record so what I want to do is
take
the words typed in to the text box and use those as the search criteria
(filter?) for my combo so I just get the matching records to select
from
in
my drop down list, not all the records on the table.
Is this possible?

:

You could filter a form so that it showed only the records that
contain
at
least one of the words.

This example assumes your form has an unbound text box named
txtKeywords.
The user types the words, with a space between them. When they move
somewhere else, the form is filtered so that the only records shown
are
those that have one of these words in a field named Notes.

It saves any edits before applying the filter. If the user clears the
box
(or types nothing but spaces), the form shows all records again.

The code works by splitting the words into a variant array, and then
looping
through the array elements to build up the Where string, adding OR to
each
one. It then chops off the trailing OR, and applies it to the form's
filter.
The current version of Access copes with up to 99 ANDs in the WHERE
clause,
so the code checks the user did not type more than 99 words.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & _
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


I want to take the words typed into a field on a form, search for all
of
them
in a field on a table then return a list of records (where any of
the
words
match) as a list so that the required record can be selected (like a
combo
box).
Can this be done? If so, can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
I have rewritten my form so the fields are bound and disposed of the combo
box altogether. The filter works and does exactly what I want but I would
like the form not to display any records when it opens - it currently
displays all - and when I save the record I select, I want to blank all the
fields out. Using what I had before to clear everything gives me an error -
Recordset not updatable - presumably because the form is now bound not
unbound.
Nearly there now, just this little bit to sort - thanks for help so far.


Allen Browne said:
Your original post explained that you wanted to:
return a list of records
in response to some search criteria.

I suggest you use a bound form in continuous view as a way to return a list
of records. You can set the Filter property of the form, so it only matches
the records you want. You will need to get this part working before you move
on to the question of how to select one of the returned records.

If you know nothing about Access or VBA, you might use the Tabular Form
Wizard to create a form that shows all the records from your table. Then
check help on Filter By Form to see how you can enter criteria to filter the
form to just those matching the word(s) you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

kbrad said:
I think you're going to have to spell this out for me! I don't get it.
At the moment there is no Record Source at form level the fields on my
form
are all unbound and are populated by selecting from the combo box list. So
I
need to apply what you're trying to explain to me solely to the record
source
of the combo field.

I've tried to do it but am getting an enter Parameter Value error on
StrWhere.

Ideally, I'd like to be able to type into the combo box and to use the
NotInList event to search for the words entered when what is typed is not
found. For example, if "machine type A" is selectable via the combo as
those
words are in the field on the table, as soon as you type "mac" it and
others
will appear so I can select the right one. But if it is typed as "Type A
machine" the NotInList event will be triggered. This is when I want it to
search for each word separately and for the combo to give me just a list
of
records with these words in them like "machine A", "engine type A" and the
one I want "machine type A" so I can again select the one I want.
Maybe this is asking too much and I need to use the separate keywords
input
field to set up the combo? But I don't know how to that either.

Are you able to help further as I'm getting in a right mess with this!!
Thanks

Allen Browne said:
So you have a combo for navigating to a record, and you also want to
filter
the combo in the same way as the form?

Once you get the filter for the form working, you can also use the WHERE
string you created in the RowSource of your combo. For example, if Combo1
currently has Table1 as its RowSource, and Table1 also has the keywords,
your code would use:
Me.Combo1.RowSource = "SELECT DISTINCT Table1.ID FROM Table1 WHERE "
&
Left(strWhere, lngLen) & ";"

If you are not sure how to generate this SQL statement, or if it needs
multiple tables, you could mock up a query and then switch it to SQL View
to
see an example of what you need in your string.

Don't forget the DISTINCT: you can use that to de-dupe the combo's values
for the cases where a record contains more than one of the keywords.

Thank you for trying to help.
Is it possible to use some form of this filter directly with my combo
box?
Currently I simply have a combo that allows me to select a record from
a
table then displays certain fields on my form for the single record -
that
works fine. This though is not good enough as many variations of the
same
words may be used to identify a single record so what I want to do is
take
the words typed in to the text box and use those as the search criteria
(filter?) for my combo so I just get the matching records to select
from
in
my drop down list, not all the records on the table.
Is this possible?

:

You could filter a form so that it showed only the records that
contain
at
least one of the words.

This example assumes your form has an unbound text box named
txtKeywords.
The user types the words, with a space between them. When they move
somewhere else, the form is filtered so that the only records shown
are
those that have one of these words in a field named Notes.

It saves any edits before applying the filter. If the user clears the
box
(or types nothing but spaces), the form shows all records again.

The code works by splitting the words into a variant array, and then
looping
through the array elements to build up the Where string, adding OR to
each
one. It then chops off the trailing OR, and applies it to the form's
filter.
The current version of Access copes with up to 99 ANDs in the WHERE
clause,
so the code checks the user did not type more than 99 words.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & _
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


I want to take the words typed into a field on a form, search for all
of
them
in a field on a table then return a list of records (where any of
the
words
match) as a list so that the required record can be selected (like a
combo
box).
Can this be done? If so, can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
kbrad said:
I have rewritten my form so the fields are bound and disposed of the combo
box altogether. The filter works and does exactly what I want but I would
like the form not to display any records when it opens - it currently
displays all - and when I save the record I select, I want to blank all the
fields out. Using what I had before to clear everything gives me an error -
Recordset not updatable - presumably because the form is now bound not
unbound.
Nearly there now, just this little bit to sort - thanks for help so far.
Click to expand...

Open the form with...
DoCmd.OpenForm "FormName",,,,acFormAdd

In the AfterUpdate event of the form have code...

Me.DataEntry = True
 
I don't think this will do the job as I don't want to edit the data displayed
that is on the table, I just want to clear what I see on the form.
In the filter code previously provided there is a bit that shows all records
when the input field is blank. I suppose what I want to do is turn that round
so I see nothing when the input is blank?
 
If you want to suppress all records in the form:
Me.Filter = "(False)"
Me.FilterOn = True
 
Back
Top