Filter using a input box 2

[jacko999]

I've looked all over for this but heres my problem:
I have a form called frmLearner and At the moment i have a Feild names surname. Rather than when opened on edit mode having to scroll through all fields i want to add a extra input box which when a surname is entered it pulls out the specified surnames so you can just scroll through the people with those surnames

Chris

 

[TheAceMan1]

How are ya jacko999 . . .

Setup a [blue]combobox with your surname[/blue] field and in the [blue]AfterUpdate[/blue] event of the combo set the forms [blue]Filter[/blue] property . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

You can filter your form in the After Update event of the combobox. You will need a means to show 'All', so set the RowSource to include 'All', for example:

[tt]SELECT "All" From Table1 UNION SELECT Surname FROM Table1[/tt]

Some sample code:

Private Sub cboSurname_AfterUpdate()

If Me.cboSurname <> "All" Then
    Me.Filter = "Surname='" & Me.cboSurname & "'"
    Me.FilterOn = True
'Set default value to show filter:
    Me.cboSurname.DefaultValue = "=" & Chr(34) & Me.cboSurname & Chr(34)
Else
    Me.Filter = ""
    Me.FilterOn = False
    Me.cboSurname.DefaultValue = Chr(34) & Me.cboSurname & Chr(34)
End If
End Sub

 

[jacko999]

thanks alot guys

Chris

 

[jacko999]

is there any way i could create a button to click which will allow me to re search the form rather than having to exit the form to search again?

Chris

 

[Remou]

You should not have to exit to search again. What code are you using and what is the rowsource of your combo?

 

[jacko999]

well i changed my Surname actual field to the combo box so when i search it the second time i input it changes. I need this to happen but i wish to be able to search again

Chris

 

[Remou]

Do you mean that when you add names, you cannot find them? Also, you seem to have missed my questions; what code are you using and what is the rowsource of your combo?

 

[jacko999]

no once ive searched once if i try to search again it saves the new surname as if the surname has changed which i do need to do my code i used is that above and row source is blank

Chris

 

[Remou]

You need to set the row source to something and you need to remove the control source. This is a combobox (isn't it) for searching, not a control for updating.

I gave you an example of a rowsource:

[tt]SELECT "All" From Table1 UNION SELECT Surname FROM Table1[/tt]

 

[jacko999]

ok so now my control source is empty and my rowsource is SELECT [tblLearner].[Surname] FROM tblLearner; according to the properties is this correct?

Chris

 

[Remou]

It should be. If you re-read my posts, you will notice that I recommend using 'All', but it is not essential.

 

[jacko999]

SELECT "All" From [tblLearner].[Surname] FROM tblLearner; is what u reccommend to use then

Chris

 

[Remou]

I think you have misread my posts, what I recommended, twice, was:

[tt]SELECT "All" From Table1 UNION SELECT Surname FROM Table1[/tt]

It should be easy enough to edit this to suit your application.

 

[jacko999]

Ok i get you thanks for all your help and quick responce much appreciated



Chris