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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
one at a time...
ASKER
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
Tom
strSQL = "SELECT * INSERT INTO tbl_search_results From tbl_Contacts WHERE [company_name] Like '*" & Me.SearchText.Text & "*'"
db.Execute strSQL
ASKER
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
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
are you using the filter and the recordsource simultaneously?
ASKER
No, I have been trying either or.
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
ASKER
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
Tom
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
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
ASKER
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
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
strSQL = "INSERT INTO tbl_search_results SELECT * From tbl_Contacts WHERE [company_name] Like '*" & str & "*'"
db.Execute strSQL
db.Execute strSQL
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