update Form and its subForm via two textboxes

  • Thread starter Thread starter Lorenzo
  • Start date Start date
L

Lorenzo

Hello and thanks in advance for your nice precious help.
I have a Form with just one subForm with a classic one to many relationship,
I am trying to filter the data of the whole Form from two text boxes. I
have seen ways of filtering via combobox but that is not what I want. I
would like to be able to filter the data with text as the search parameter.
The Form I open contains contacts that I would like to be able to filter
typing the initials in the mentioned textboxes for example typing "s" in
txtFirstName would then load the first record of the table starting with
FirstName with "s"

Sorry if I have probably repeated myself but I did it only to clarify that
I am not looking to filter the results in a listbox but on the records of
the main form itself.

Thank you,
Lorenzo
 
Hello and thanks in advance for your nice precious help.
I have a Form with just one subForm with a classic one to many relationship,
I am trying to filter the data of the whole Form from two text boxes. I
have seen ways of filtering via combobox but that is not what I want. I
would like to be able to filter the data with text as the search parameter.
The Form I open contains contacts that I would like to be able to filter
typing the initials in the mentioned textboxes for example typing "s" in
txtFirstName would then load the first record of the table starting with
FirstName with "s"

Sorry if I have probably repeated myself but I did it only to clarify that
I am not looking to filter the results in a listbox but on the records of
the main form itself.

Thank you,
Lorenzo
Click to expand...

If you want to use Access' builtin feature for this use the Menu:
select Records... Filter... Filter by Form, and choose the option
"start of text".

If you want to roll your own, you will need *two textboxes* - one
unbound, to specify the filter criteria, and a second bound to the
FirstName field to display the name. In the AfterUpdate event of the
unbound textbox you could use code like

Private Sub txtFindFirstName_AfterUpdate()
If Me.txtFindFirstName & "" = "" Then
' user left the search blank, remove the filter
Me.Filter = ""
Me.FilterOn = False
Else
' user entered one or more letters
Me.Filter = "LIKE '" & Me.txtFindFirstName & "*'"
Me.FilterOn = True
End If
End Sub

John W. Vinson[MVP]
 
John thanks for your help, I was most interested in rolling my own but I get
an error with the vba code provided. It says that it can't assign the value
to the object. This should be a simple task what am I doing wrong? I have
tried as you suggested just with one field name and the unbound textbox but
I get the same error message. The debugger goes right on the line

Me.Filter = "LIKE '" & Me.txtFindFirstName & "*'"

Any further help greatly appreciated
Lorenzo
 
I think John made a slight error in what he typed. When you create a filter,
you need to tell Access the field to which the filter is intended to apply.

Assuming your table contains a field FirstName on which you want to filter,
you'd want something like

Me.Filter = "[FirstName] LIKE '" & Me.txtFindFirstName & "*'"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Lorenzo"
 
I think John made a slight error in what he typed. When you create a filter,
you need to tell Access the field to which the filter is intended to apply.

Assuming your table contains a field FirstName on which you want to filter,
you'd want something like

Me.Filter = "[FirstName] LIKE '" & Me.txtFindFirstName & "*'"
Click to expand...

Thanks, Douglas, and apologies, Lorenzo!

John W. Vinson[MVP]
 
Back
Top