Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filter a subform from the main form

Posted on 2008-10-12
16
Medium Priority
?
592 Views
Last Modified: 2013-11-28
Hi,

I have form with a few search text boxes on.  Also on the form is a sub form (frm_Contacts_Sub) which is a contiuous form with tbl_contacts as it record source.  I have used a continuous form instead of a list box as i need to allow the user to select a contact using a tick box on each record.

My question is how do i filter the sub form from my text boxes from the main form.  I have some code which i am using however, when i begin typing in the search text box is always returns the same one result regardless what i type.

Tom
Private Sub SearchText_Change()
Dim SQL As String
 
SQL = " SELECT * FROM tbl_Contacts Where [company_name] Like '*" & Me.SearchText.Text & "*'"
Me![frm_contacts_sub].Form.RecordSource = SQL
End Sub

Open in new window

0
Comment
Question by:d10u4v
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
16 Comments
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 800 total points
ID: 22697090
There's no need to replace the SQL, just set a filter.
This certainly works for me:

Private Sub Text0_Change()
    With Me![Customers subform].Form
        .Filter = "[Job Title] Like '*" & Me.Text0.Text & "*'"
        .FilterOn = True
    End With
End Sub
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 22697103
Having said that that, the Recordsource method works too - at the cost of a requery for every character you type. The filter will probably perform better.
Private Sub Text0_Change()
    With Me![Customers subform].Form
'        .Filter = "[Job Title] Like '*" & Me.Text0.Text & "*'"
'        .FilterOn = True
        .RecordSource = "select * from customers where [Job Title] Like '*" & Me.Text0.Text & "*'"
    End With
End Sub
0
 

Author Comment

by:d10u4v
ID: 22697206
Thanks,

I can't seem to get the RecordSeouce method to work, but the filter is working great.  I'm currently testing different seach methods in Access and have a few ideas which i'm not sure are ok.

Could i use the same filter method, but time it would filter the table directly and copy the returned data to another table which would them be used to populate a list box or continuous form.  The reason being the user could then build up a table of search results which they could use to run a mail merge or print of a report.  Once the search form is closed the tble would be cleared ready for a new search.

Tom
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22698115
try

Private Sub Text0_Change()
dim str
str=str & me.text0.text
    With Me![Customers subform].Form
'        .Filter = "[Job Title] Like '*" & str & "*'"
'        .FilterOn = True
        .RecordSource = "select * from customers where [Job Title] Like '*" & str & "*'"
    End With
End Sub
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1200 total points
ID: 22698124

Private Sub Text0_Change()
dim str
str=str & me.text0.text
    With Me![Customers subform].Form
'        .Filter = "[Job Title] Like '*" & str & "*'"
'        .FilterOn = True
        .RecordSource = "select * from customers where [Job Title] Like '*" & str & "*'"
        .requery
    End With
End Sub
0
 

Author Comment

by:d10u4v
ID: 22698136
Hi,  Thanks for that Capricorn1, i'll have a look at that.

How would i also send the 'filtered' data to a new table (tbl_search_results)?  I need to have the table already setup and just append data to it, not have the table created each time.

Tom
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22698162
one at a time...
0
 

Author Comment

by:d10u4v
ID: 22698305
I had this, but it would either create a new table each time or error out Runtime Error 3010 because the table already existed.

Tom
strSQL = "SELECT * INSERT INTO tbl_search_results From tbl_Contacts WHERE [company_name] Like '*" & Me.SearchText.Text & "*'"
 
db.Execute strSQL

Open in new window

0
 

Author Comment

by:d10u4v
ID: 22698328
Hi

I have just tried your example using [.RecordSource = "select * from customers where [Job Title] Like '*" & str & "*'"] and i get the same out come as before.

When i enter a seach criteria it returns only one result regardless what i enter.  The filter works fine. but using the recordsource option doesn't.

Tom
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22698383
are you using the filter and the recordsource simultaneously?
0
 

Author Comment

by:d10u4v
ID: 22698393
No,  I have been trying either or.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22698469


Private Sub Text0_Change()
dim str
str=str & me.text0.text
    With Me![Customers subform].Form
        ' clear the filter
        .filter=""

        .RecordSource = "select * from customers where [Job Title] Like '*" & str & "*'"
        .requery
    End With
End Sub
0
 

Author Comment

by:d10u4v
ID: 22698511
Still the same thing is happening.  Regardless what i type in the search i get the same result.  It is always "Archive Test Company" Even if i just type a 'B' in the search field i get that result.

Tom
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22698533
do a compact and repair

do a DEBUG>Compile
correct any error that will be raised.

....

try decompiling your db

http://www.granite.ab.ca/access/decompile.htm

if problem persists
your form could be corrupted..


create a new form and subform

and use the codes above
0
 

Author Comment

by:d10u4v
ID: 22698692
It must be becuase its a Sunday and i said that i wanted to get this done by tomorrow...

For some reason my 'unbound' form was bound to the tbl_contacts table!  That is what was causing the strange search results.  It is working ok now.

I now need to look at copying the returned results into another table.

Tom
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22698752
strSQL = "INSERT INTO tbl_search_results SELECT *  From tbl_Contacts WHERE [company_name] Like '*" & str & "*'"
 
db.Execute strSQL
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question