Use TextBox on the main form to limit SubForm

[Dragon]

Hi,

I have a form with a text box and a subform. Main form is unbound. SubForm
is displaying records from a query. I would like to the user to be able to
enter text in the text box and subform to display records containing this
text with a field.

For Example:

SubForm Fields:
ID, [Full Name], City, PostCode

Date:
1, John Doe, London, as12as
2, John Tee, NY, 12345
3, Jane Doe, London, as12as

If user enters Doe into the text field, it should show records 1 and 3 in
the subform.

I have created an on-update event and tried to create a filter for the
subform but it does not seem to be working. The field I am trying to match
has a space in it ([Full Name]) and for some reason I can't seem to be able
to create an appropriate filter. Also, I want a 'Like' filter and not
'equal' filter.

Thank you for your help in advance.

 

[Jörn Bosse]

Hello,

this might help you:

Dim strSQL As String
Dim strSearch As Strin
strSearch = Me.txtSearch.Text 'use your textfieldname instead
'of txtSearch
strSQL = "SELECT * " & _
"FROM Yourtable " & _
"WHERE YourFieldinTable like '*" & strSearch & "*'"
Me.YourSubform.Form.RecordSource = strSQL

 

[John Spencer]

One thing you could do is base the subform on a query like the following

SELECT *
FROM SomeTable
WHERE [Full Name] Like "*" & [Forms]![Main Form Name]![Text Control Name] & "*"

And then use code to force a requery of the subform's form whenever the value
in the control changes. Perhaps the lost focus event of the control or the a
button on the form to trigger the requery.

Private Sub <<Your event>> ()
Me.NameOfSubFormControl.Form.Requery
End Sub

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County