Subtable search

N

nick5196

Registered User.
Local time
Yesterday, 22:18
Joined
Feb 17, 2011
Messages
43
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
 

Attachments

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.
Nick
 
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
 
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;
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
 
Last edited:
Here's a practical example. You will also need to deal with Nulls, in your filtering code.
 

Attachments

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!!
Click to expand...
 
You must log in or register to reply here.

Similar threads

F
VBA Webpage search
Replies
1
Views
108
theDBguy
theDBguy
D
Solved Search pdf for specific string
Replies
14
Views
485
JimChristmas
J
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
426
CJ_London
C
Uncle Gizmo
How to Simplify MS Access Form Searches Using Class Modules and Collections to Pair Text Boxes and Option Groups for Efficient SQL Query Generation
Replies
14
Views
538
MarkK
MarkK
E
Emmanuel Katto Dubai : Suggestions Needed for Creating a Search Text Box
Replies
2
Views
140
Edgar_
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom