Filter a subform from the main form

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

d10u4vAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

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
 
MikeTooleConnect With a Mentor Commented:
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
 
MikeTooleCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
d10u4vAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
d10u4vAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
one at a time...
0
 
d10u4vAuthor Commented:
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
 
d10u4vAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
are you using the filter and the recordsource simultaneously?
0
 
d10u4vAuthor Commented:
No,  I have been trying either or.
0
 
Rey Obrero (Capricorn1)Commented:


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
 
d10u4vAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
d10u4vAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
strSQL = "INSERT INTO tbl_search_results SELECT *  From tbl_Contacts WHERE [company_name] Like '*" & str & "*'"
 
db.Execute strSQL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.