Subtable search (1 Viewer)

nick5196 · Feb 28, 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

John Big Booty · Feb 28, 2011

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.

nick5196 · Feb 28, 2011

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.
Nick

John Big Booty · Feb 28, 2011

Perhaps this article will give you some pointers.

nick5196 · Mar 2, 2011

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
Nick

John Big Booty · Mar 2, 2011

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.

John Big Booty · Mar 3, 2011

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;

Code:

    Dim strFilter As String
    
    Me.Refresh
    
    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;

Code:

Forms!MainFormName!SubFormName.Form.FilterOn = False

John Big Booty · Mar 3, 2011

Here's a practical example. You will also need to deal with Nulls, in your filtering code.

hunterfan48 · Apr 3, 2011

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

hunterfan48 said:
Code:

Private Sub Text0_Change() Dim strFilter As String Me.Refresh 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 me...it 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!!

hunterfan48 · Apr 5, 2011

anybody????

Subtable search (1 Viewer)

nick5196

Registered User.

Attachments

John Big Booty

AWF VIP

nick5196

Registered User.

John Big Booty

AWF VIP

nick5196

Registered User.

John Big Booty

AWF VIP

John Big Booty

AWF VIP

John Big Booty

AWF VIP

Attachments

hunterfan48

Registered User.

hunterfan48

Registered User.

Similar threads

Users who are viewing this thread