Subtable search (1 Viewer)


Registered User.
Feb 17, 2011
Hi, I wish to create a search box which will filter through the names in my sub-table list on the switchboard. I would like it to be similar to the itunes search function or the one which JBB created: it shortens the list after the entry of each character.

The reason for not creating a listbox and searching that is because I wish to maintain the open and edit student functions of the sub-table.

Thanks a lot for any help, I have uploaded a copy of the DB, the form in question opens on start-up: "switchboard". Sorry there is so much on the DB/it is so large.

Thanks again


To replicate the features of my sample you will need to dynamically filter the Record Source of your subform.

Alternately you can allow records to be edit by double clicking on the desired record in the list box, you could use the same pop up form to add records to the underlying tables.
I have already downloaded and implemented your dynamic search function, to some success. Could you tell me how you would, specifically, filter the subform?
Therefore creating the same search function, but to filter by elimination through the sub-table.

Thank you, I'm very great full for your help.
This was an interesting read however it did not really help. I really like the "listbox" system and just need to manipulate it to work with a sub-table. I have tried removing the listbox and naming and subtable/query the same. The debugger then picks up on the .requery command.

Any ideas of how I could make this conversion from listbox to subtable?

Thanks again
I'm thinking that what you are trying to do will require a partial re-working of the search form and the logic behind it. This is currently not an area that I'm overly interested in exploring.

My suggestion is to read up on form filters, have a shot at implementing them and post back with any specific question that might arise.
OK I was working on another problem and the solution came to me.

Use the following code, in the On Change event of your text box;
    Dim strFilter As String
    strFilter = "[FieldNameToSearch] like '*" & Me.SrchBoxName & "*'"

    Forms!MainFormName!SubFormName.Form.Filter = strFilter
    Forms!MainFormName!SubFormName.Form.FilterOn = True
    Me.SrchBoxName.SelStart = Len(Me.SrchBoxName)

Now this code currently only works on one field you will need to expand it to work on multiple fields. additionally you will need to add a protocol for dealing with trailing spaces in your search string.

To return to your start posture with no filters use;
Forms!MainFormName!SubFormName.Form.FilterOn = False
Last edited:
Here's a practical example. You will also need to deal with Nulls, in your filtering code.


Here's my latest attempt to put your sample to work for me!

Private Sub Text0_Change()
    Dim strFilter As String
    strFilter = "sport like '*" & Me.Text0 & "*'"
    strFilter = "ID like '*" & Me.Text0 & "*'"
    strFilter = "year like '*" & Me.Text0 & "*'"
    strFilter = "manufacturer like '*" & Me.Text0 & "*'"
    strFilter = "set like '*" & Me.Text0 & "*'"
    strFilter = "player name like '*" & Me.Text0 & "*'"
    strFilter = "card type like '*" & Me.Text0 & "*'"
    strFilter = "card number like '*" & Me.Text0 & "*'"
    strFilter = "condition like '*" & Me.Text0 & "*'"
    strFilter = "team like '*" & Me.Text0 & "*'"
    Forms!Form4!frmSubform.Form.Filter = strFilter
    Forms!Form4!frmSubform.Form.FilterOn = True
    Me.Text0.SelStart = Len(Me.Text0)
End Sub

Ok, so I got it to work partially. LOL Like you mentioned in your sample John, it was just for one field. Well I would like it to search more than one, but it's causing me a few problems. What and where would I put the extra code to be able to do that?

As of right now, when I type in the search box it narrows it down to nothing. As well, when I try using a space in my text box (i.e., Joe Montana) it won't let keeps on writing as such (JoeMontana)

And last, I need to add the code to deal with nulls. Where can I find that?

Thanks for all the help guys!!

Users who are viewing this thread

Top Bottom