Prev: Access 2003/Access 2007
Next: Convenient search feature for Access
From: Dragon on 7 Jul 2010 01:40
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.


From: Jörn Bosse on 7 Jul 2010 05:27
Hello,

this might help you:

Dim strSQL As String
Dim strSearch As String
strSearch = Me.txtSearch.Text 'use your textfieldname instead
of txtSearch

strSQL = "SELECT * " & _
"FROM Yourtable " & _
"WHERE YourFieldinTable like '*" & strSearch & "*'"
Me.YourSubform.Form.RecordSource = strSQL

From: Salad on 7 Jul 2010 10:03
Dragon wrote:
> 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.
>

In the AfterUpdate event you can set a filter. Something like
If Not IsNull(Me.TextBoxName) then
strSearch = "FullName Like *" & Me.TextBoxName & "*"
Else
strSearch = "" 'will remove flter if blank
Endif

'set the filter
Forms!MainFormName!SubFormName.Form.Filter = strSearch
'turn the filter on or off
Forms!MainFormName!SubFormName.Form.FilterOn = (strSearch > "")
 | 
Pages: 1
Prev: Access 2003/Access 2007
Next: Convenient search feature for Access