Filter Records within a List Box?

[Bkper087]

I just posted a similar question, but after some research I've decided I need to ask it a different way.



My landing form when you open the database I am working on is a navigation style form. Within the navigation form I have a "primary function" form and a few others that allow you to make edits to drop down menus, etc.

The real question - is there a way to have a list of records filtered within a form and and be able to select the desired record based on that filter. I know it is possible to select records from a list box, which is what i currently have on the form, but I cannot find a way to filter the list box based on a search tool. Any ideas?

[orange]

Not sure I'm understanding, but here's a link where a list box is filtered.
If that isn't what you are looking for, then maybe a jpg or 2 of your situation would help.

[Bkper087]

Orange, that is exactly what I am looking for; however, I can't seem to make it work. I have almost no experience with VBA although I'm not afraid to try it. I downloaded the sample database and it functions exactly how I would like it to in my own database; however, I keep getting an error when I attempt to recreate it in my own database. I followed the steps given, copied and pasted the code, but I am getting the error "Invalid Outside Procedure" and it is highlighting the code below in RED:

'Create a string (text) variable
Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = SearchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
SrchText.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
Me.SearchResults.Requery


'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
Exit Sub
End If

'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
Me.SearchFor.SetFocus

If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If

[orange]

Did you go to the form; design view; click on the SearchFor box; get the Properties; Event Procedure
as in the attached jpg?
Click on the 3 ... at the end of the Event Procedure line
That's where the code goes..

Check it out and let us know if it's working.

[ssanfu]

I would check spelling first.

Step #2 is: "On that form create two unbound text boxes, call the first one SearchFor".....
Did you create the unbound text box and name it "SearchFor"?

Personally, I would use

vSearchString = Me.SearchFor.Text

(The "Me." tells me that the object (SearchFor) is a control on the form, not a variable in the code.)
Usually, the highlight means Access doesn't know what the object is or can't find it.

[Bkper087]

Yes I entered the code under the OnChange section. Also I tried adding the "Me." notation in front of the text, but I still get the same error.

[orange]

This error

"Invalid Outside Procedure" and it is highlighting the code below in RED:

indicates, to me anyway, that you have a line that should be within some processing code, and it is not within a sub or function.

Can you zip and post a copy of your database that has the problem?

[Bkper087]

I have attached a copy of my database. I went ahead and attempted to build the tool within my own database since I am uploading the file for trouble shooting anyway. I am no longer getting the same error, but it still is not working. The only thing I have changed from the instructions given in the example is the name FRM_SearchMult. In my existing form the name of this form is ProfileBuilderF.

The relevant queries and forms are listed in the custom group "ITEMS RELATED TO VBA SEARCH QUESTION"

Thanks for any help in advance. It would be a huge time-saver if I can get this figured out.

[orange]

Can you give a brief overview of what this database is about--say 4 -5 lines plain English?

I see lots of tables in the relationships. That's good compared to many we see.
I'm just curious about the underlying "business".

Your code should look like this:

Private Sub SearchFor_Change()
'Create a string (text) variable
    Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = SearchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.SearchResults.Requery


'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        Exit Sub
    End If

'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
    Me.SearchFor.SetFocus

    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If

End Sub

If you look at your code, it is sitting above this line

Private Sub SearchFor_Change() and is outside the procedure.

Try changing as above and let us know.

[Bkper087]

I work for a non profit organization and the overall idea is to build a profile for prospective donors or volunteers, the organizations they are involved in, their professions, etc

[orange]

Did you see the code? and try it?

Basically I just moved the

Private Sub SearchFor_Change()

to the line above your code and removed the extra End Sub statement.