Finding Record with one word in field name

[Gnerks]

I have a table in which the records are listed in the following format:

1MRS/Civil Engineering/Training Manager
2MRS/Operations/Advanced Training and Learning Center

I have a form with a list box that lists all of the Project Names from by
Access data table. We are using Access 2007.

I have a text box to filter through the names in the list box and want to
use it for user input to select only those records that CONTAIN the word
training (or whatever the user enters).

My code for the text box is

Private Sub txtLookup_Change()
'Used to quickly lookup a study

Dim strCriteria As String
Dim sSQL As String

strCriteria = Nz(Me.txtLookup.Text, "") & "*"
sSQL = "SELECT tblREPORTfields.*" _
& " from tblREPORTfields" _
& " WHERE (((tblREPORTfields.SELECTED)=False)" _
& " And ((tblREPORTfields.[PROJECT TITLE]) Like " & Chr(34) &
strCriteria & Chr(34) & "));"
Me.lstProjects.RowSource = sSQL
Me.lstProjects.Requery

End Sub

How do i change this code in order to select only those studies that have
the word the user entered in them?

 

[Klatuu]

I would not suggest using the Change event. The Change event fires after
each keystroke. In this case I don't think that is what you really want.
Now, your question was how to select all items that have a specific value in
a text box. Here is how you can do that using the text box After Update
event:

Private Sub Text2_AfterUpdate()
Dim lngX As Long
With Me.List0
For lngX = 0 To .ListCount - 1
If InStr(.ItemData(lngX), Me.Text2) Then
.Selected(lngX) = True
End If
Next lngX
End With
End Sub

 

[Gnerks]

This is helping - now I can find some of the records that contain the keyword
(i.e., training), however, it will just bring up the first record - not
filtered to show all records with the keyword. Is there a way to add a
filter to the code you provided within the listbox to show only those records
containing the keyword?

 

[Klatuu]

I think I msread the question. Here is a function you can use to filter your
SQL statment based on what is selected in a list box:

Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

--
Dave Hargis, Microsoft Access MVP

This is helping - now I can find some of the records that contain the keyword
(i.e., training), however, it will just bring up the first record - not
filtered to show all records with the keyword. Is there a way to add a
filter to the code you provided within the listbox to show only those records
containing the keyword?

I have a table in which the records are listed in the following format:

1MRS/Civil Engineering/Training Manager
2MRS/Operations/Advanced Training and Learning Center

I have a form with a list box that lists all of the Project Names from by
Access data table. We are using Access 2007.

I have a text box to filter through the names in the list box and want to
use it for user input to select only those records that CONTAIN the word
training (or whatever the user enters).

My code for the text box is

Private Sub txtLookup_Change()
'Used to quickly lookup a study

Dim strCriteria As String
Dim sSQL As String

strCriteria = Nz(Me.txtLookup.Text, "") & "*"
sSQL = "SELECT tblREPORTfields.*" _
& " from tblREPORTfields" _
& " WHERE (((tblREPORTfields.SELECTED)=False)" _
& " And ((tblREPORTfields.[PROJECT TITLE]) Like " & Chr(34) &
strCriteria & Chr(34) & "));"
Me.lstProjects.RowSource = sSQL
Me.lstProjects.Requery

End Sub

How do i change this code in order to select only those studies that have
the word the user entered in them?