Filtering a List Box

[Andrew C]

Hi

I have a client form that has a list box down the side. When you select a
name in the list box it updates the fields to show that clients details.

What i want to do is add a text box above the list box. So that if you
enter anything in the text box it filters the list box and shows any results
that contains the text entered. (Whether is be at the start in the middle or
at the end or their surname. If the text box is left empty id shows all
fields.

The List box current has 3 fields (Clinet No, Client Surname, Client First
Name)

I have looked through the newsgroup but cant find exactly what im after.

Thanks

 

[Jeff Boyce]

Andrew

It sounds like you are trying to modify the contents of the listbox, based
on what is entered in a textbox. One approach might be to (re-)set the
listbox's RowSource property after each keystroke in the textbox.

You might use the KeyPress event and dynamically build a SQL string that
you'd then assign to the listbox's RowSource property. Requery the listbox,
if necessary.

The modification you are doing to the SQL string would involve the "WHERE"
clause -- for example, if you had entered "Joh" into the textbox, your
(dynamic) SQL statement would include something like (untested aircode --
use your own fields/tables):

"SELECT {whatever} FROM {whereever} WHERE [SomeField]='Joh';"

 

[Andrew C]

I do want to modify the contents on the list box but it does not hae to be
after every key stroke. It can be after you leave the text box or hit enter
to move on.

Is there another way to do it. Something a bit simpler. I only have medium
knowledge of access. but its getting better with the help of this newsgroup.

Thanks

Andrew

It sounds like you are trying to modify the contents of the listbox, based
on what is entered in a textbox. One approach might be to (re-)set the
listbox's RowSource property after each keystroke in the textbox.

You might use the KeyPress event and dynamically build a SQL string that
you'd then assign to the listbox's RowSource property. Requery the listbox,
if necessary.

The modification you are doing to the SQL string would involve the "WHERE"
clause -- for example, if you had entered "Joh" into the textbox, your
(dynamic) SQL statement would include something like (untested aircode --
use your own fields/tables):

"SELECT {whatever} FROM {whereever} WHERE [SomeField]='Joh';"

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi

I have a client form that has a list box down the side. When you select a
name in the list box it updates the fields to show that clients details.

What i want to do is add a text box above the list box. So that if you
enter anything in the text box it filters the list box and shows any results
that contains the text entered. (Whether is be at the start in the middle or
at the end or their surname. If the text box is left empty id shows all
fields.

The List box current has 3 fields (Clinet No, Client Surname, Client First
Name)

I have looked through the newsgroup but cant find exactly what im after.

Thanks

 

[Ofer]

Create a Text Box with a button next to it, after the use key in the text
he/she press the button to get the new list, if noting was entered, it will
display the full list, so if the user want to display the all list again all
he had to do is delete the content of the text box and pree the button

Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 Like '*" & Me.TextBoxName & "*'"

--
\\// Live Long and Prosper \\//
BS"D

I do want to modify the contents on the list box but it does not hae to be
after every key stroke. It can be after you leave the text box or hit enter
to move on.

Is there another way to do it. Something a bit simpler. I only have medium
knowledge of access. but its getting better with the help of this newsgroup.

Thanks

Andrew

It sounds like you are trying to modify the contents of the listbox, based
on what is entered in a textbox. One approach might be to (re-)set the
listbox's RowSource property after each keystroke in the textbox.

You might use the KeyPress event and dynamically build a SQL string that
you'd then assign to the listbox's RowSource property. Requery the listbox,
if necessary.

The modification you are doing to the SQL string would involve the "WHERE"
clause -- for example, if you had entered "Joh" into the textbox, your
(dynamic) SQL statement would include something like (untested aircode --
use your own fields/tables):

"SELECT {whatever} FROM {whereever} WHERE [SomeField]='Joh';"

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi

I have a client form that has a list box down the side. When you select a
name in the list box it updates the fields to show that clients details.

What i want to do is add a text box above the list box. So that if you
enter anything in the text box it filters the list box and shows any results
that contains the text entered. (Whether is be at the start in the middle or
at the end or their surname. If the text box is left empty id shows all
fields.

The List box current has 3 fields (Clinet No, Client Surname, Client First
Name)

I have looked through the newsgroup but cant find exactly what im after.

Thanks

 

[Andrew C]

Im having problems with the code you told me.

I keep getting a compile error. And not sure why. Im not famailar with VB
code only know very little.

this is the current info i have in the Rowsource of the List box (not sure
if this is casuing a problem)

SELECT CLIENt.[Client Id], CLIENt.Surname, CLIENt.[First Name] FROM CLIENt
WHERE (((CLIENt.Active)="Y")) ORDER BY CLIENt.Surname;

cause the list box also need to filter out active and inactive members as
well, Has a field which has a "Y" or "N" for active or inactive.

any more info you need just ask

Thanks

Create a Text Box with a button next to it, after the use key in the text
he/she press the button to get the new list, if noting was entered, it will
display the full list, so if the user want to display the all list again all
he had to do is delete the content of the text box and pree the button

Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 Like '*" & Me.TextBoxName & "*'"

--
\\// Live Long and Prosper \\//
BS"D

I do want to modify the contents on the list box but it does not hae to be
after every key stroke. It can be after you leave the text box or hit enter
to move on.

Is there another way to do it. Something a bit simpler. I only have medium
knowledge of access. but its getting better with the help of this newsgroup.

Thanks

Andrew

It sounds like you are trying to modify the contents of the listbox, based
on what is entered in a textbox. One approach might be to (re-)set the
listbox's RowSource property after each keystroke in the textbox.

You might use the KeyPress event and dynamically build a SQL string that
you'd then assign to the listbox's RowSource property. Requery the listbox,
if necessary.

The modification you are doing to the SQL string would involve the "WHERE"
clause -- for example, if you had entered "Joh" into the textbox, your
(dynamic) SQL statement would include something like (untested aircode --
use your own fields/tables):

"SELECT {whatever} FROM {whereever} WHERE [SomeField]='Joh';"

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi

I have a client form that has a list box down the side. When you select a
name in the list box it updates the fields to show that clients details.

What i want to do is add a text box above the list box. So that if you
enter anything in the text box it filters the list box and shows any
results
that contains the text entered. (Whether is be at the start in the middle
or
at the end or their surname. If the text box is left empty id shows all
fields.

The List box current has 3 fields (Clinet No, Client Surname, Client First
Name)

I have looked through the newsgroup but cant find exactly what im after.

Thanks

 

[Ofer]

When you assign the RowSource, change the double quote to single quote for
the Y

SELECT CLIENt.[Client Id], CLIENt.Surname, CLIENt.[First Name] FROM CLIENt
WHERE (((CLIENt.Active)='Y')) ORDER BY CLIENt.Surname

--
\\// Live Long and Prosper \\//
BS"D

Im having problems with the code you told me.

I keep getting a compile error. And not sure why. Im not famailar with VB
code only know very little.

this is the current info i have in the Rowsource of the List box (not sure
if this is casuing a problem)

SELECT CLIENt.[Client Id], CLIENt.Surname, CLIENt.[First Name] FROM CLIENt
WHERE (((CLIENt.Active)="Y")) ORDER BY CLIENt.Surname;

cause the list box also need to filter out active and inactive members as
well, Has a field which has a "Y" or "N" for active or inactive.

any more info you need just ask

Thanks

Create a Text Box with a button next to it, after the use key in the text
he/she press the button to get the new list, if noting was entered, it will
display the full list, so if the user want to display the all list again all
he had to do is delete the content of the text box and pree the button

Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 Like '*" & Me.TextBoxName & "*'"

--
\\// Live Long and Prosper \\//
BS"D

I do want to modify the contents on the list box but it does not hae to be
after every key stroke. It can be after you leave the text box or hit enter
to move on.

Is there another way to do it. Something a bit simpler. I only have medium
knowledge of access. but its getting better with the help of this newsgroup.

Thanks

:

Andrew

It sounds like you are trying to modify the contents of the listbox, based
on what is entered in a textbox. One approach might be to (re-)set the
listbox's RowSource property after each keystroke in the textbox.

You might use the KeyPress event and dynamically build a SQL string that
you'd then assign to the listbox's RowSource property. Requery the listbox,
if necessary.

The modification you are doing to the SQL string would involve the "WHERE"
clause -- for example, if you had entered "Joh" into the textbox, your
(dynamic) SQL statement would include something like (untested aircode --
use your own fields/tables):

"SELECT {whatever} FROM {whereever} WHERE [SomeField]='Joh';"

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi

I have a client form that has a list box down the side. When you select a
name in the list box it updates the fields to show that clients details.

What i want to do is add a text box above the list box. So that if you
enter anything in the text box it filters the list box and shows any
results
that contains the text entered. (Whether is be at the start in the middle
or
at the end or their surname. If the text box is left empty id shows all
fields.

The List box current has 3 fields (Clinet No, Client Surname, Client First
Name)

I have looked through the newsgroup but cant find exactly what im after.

Thanks

 

[Andrew C]

Thanks Got it working OK with one little problem. It know wont sort the
list by surname. below is what i have got to work but i need to sort by
surname.

Have triend to add 'ORDER BY client.surname' on the end but keep getting a
compile error. what am i doing wrong

Private Sub Command48_Click()
Me.List36.RowSource = "SELECT CLIENt.[Client Id], CLIENt.Surname,
CLIENt.[First Name] FROM CLIENt Where (((CLIENt.Active)='Y')) and
client.surname Like '*" & Me.Search & "*'"

End Sub

Thanks

When you assign the RowSource, change the double quote to single quote for
the Y

SELECT CLIENt.[Client Id], CLIENt.Surname, CLIENt.[First Name] FROM CLIENt
WHERE (((CLIENt.Active)='Y')) ORDER BY CLIENt.Surname

--
\\// Live Long and Prosper \\//
BS"D

Im having problems with the code you told me.

I keep getting a compile error. And not sure why. Im not famailar with VB
code only know very little.

this is the current info i have in the Rowsource of the List box (not sure
if this is casuing a problem)

SELECT CLIENt.[Client Id], CLIENt.Surname, CLIENt.[First Name] FROM CLIENt
WHERE (((CLIENt.Active)="Y")) ORDER BY CLIENt.Surname;

cause the list box also need to filter out active and inactive members as
well, Has a field which has a "Y" or "N" for active or inactive.

any more info you need just ask

Thanks

Create a Text Box with a button next to it, after the use key in the text
he/she press the button to get the new list, if noting was entered, it will
display the full list, so if the user want to display the all list again all
he had to do is delete the content of the text box and pree the button

Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 Like '*" & Me.TextBoxName & "*'"

--
\\// Live Long and Prosper \\//
BS"D


:

I do want to modify the contents on the list box but it does not hae to be
after every key stroke. It can be after you leave the text box or hit enter
to move on.

Is there another way to do it. Something a bit simpler. I only have medium
knowledge of access. but its getting better with the help of this newsgroup.

Thanks

:

Andrew

It sounds like you are trying to modify the contents of the listbox, based
on what is entered in a textbox. One approach might be to (re-)set the
listbox's RowSource property after each keystroke in the textbox.

You might use the KeyPress event and dynamically build a SQL string that
you'd then assign to the listbox's RowSource property. Requery the listbox,
if necessary.

The modification you are doing to the SQL string would involve the "WHERE"
clause -- for example, if you had entered "Joh" into the textbox, your
(dynamic) SQL statement would include something like (untested aircode --
use your own fields/tables):

"SELECT {whatever} FROM {whereever} WHERE [SomeField]='Joh';"

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi

I have a client form that has a list box down the side. When you select a
name in the list box it updates the fields to show that clients details.

What i want to do is add a text box above the list box. So that if you
enter anything in the text box it filters the list box and shows any
results
that contains the text entered. (Whether is be at the start in the middle
or
at the end or their surname. If the text box is left empty id shows all
fields.

The List box current has 3 fields (Clinet No, Client Surname, Client First
Name)

I have looked through the newsgroup but cant find exactly what im after.

Thanks

 

[Ofer]

Try

Me.List36.RowSource = "SELECT CLIENt.[Client Id], CLIENt.Surname,
CLIENt.[First Name] FROM CLIENt Where (((CLIENt.Active)='Y')) and
client.surname Like '*" & Me.Search & "*' Order By Surname"

--
\\// Live Long and Prosper \\//
BS"D

Thanks Got it working OK with one little problem. It know wont sort the
list by surname. below is what i have got to work but i need to sort by
surname.

Have triend to add 'ORDER BY client.surname' on the end but keep getting a
compile error. what am i doing wrong

Private Sub Command48_Click()
Me.List36.RowSource = "SELECT CLIENt.[Client Id], CLIENt.Surname,
CLIENt.[First Name] FROM CLIENt Where (((CLIENt.Active)='Y')) and
client.surname Like '*" & Me.Search & "*'"

End Sub

Thanks

When you assign the RowSource, change the double quote to single quote for
the Y

SELECT CLIENt.[Client Id], CLIENt.Surname, CLIENt.[First Name] FROM CLIENt
WHERE (((CLIENt.Active)='Y')) ORDER BY CLIENt.Surname

--
\\// Live Long and Prosper \\//
BS"D

Im having problems with the code you told me.

I keep getting a compile error. And not sure why. Im not famailar with VB
code only know very little.

this is the current info i have in the Rowsource of the List box (not sure
if this is casuing a problem)

SELECT CLIENt.[Client Id], CLIENt.Surname, CLIENt.[First Name] FROM CLIENt
WHERE (((CLIENt.Active)="Y")) ORDER BY CLIENt.Surname;

cause the list box also need to filter out active and inactive members as
well, Has a field which has a "Y" or "N" for active or inactive.

any more info you need just ask

Thanks

:

Create a Text Box with a button next to it, after the use key in the text
he/she press the button to get the new list, if noting was entered, it will
display the full list, so if the user want to display the all list again all
he had to do is delete the content of the text box and pree the button

Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 Like '*" & Me.TextBoxName & "*'"

--
\\// Live Long and Prosper \\//
BS"D


:

I do want to modify the contents on the list box but it does not hae to be
after every key stroke. It can be after you leave the text box or hit enter
to move on.

Is there another way to do it. Something a bit simpler. I only have medium
knowledge of access. but its getting better with the help of this newsgroup.

Thanks

:

Andrew

It sounds like you are trying to modify the contents of the listbox, based
on what is entered in a textbox. One approach might be to (re-)set the
listbox's RowSource property after each keystroke in the textbox.

You might use the KeyPress event and dynamically build a SQL string that
you'd then assign to the listbox's RowSource property. Requery the listbox,
if necessary.

The modification you are doing to the SQL string would involve the "WHERE"
clause -- for example, if you had entered "Joh" into the textbox, your
(dynamic) SQL statement would include something like (untested aircode --
use your own fields/tables):

"SELECT {whatever} FROM {whereever} WHERE [SomeField]='Joh';"

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi

I have a client form that has a list box down the side. When you select a
name in the list box it updates the fields to show that clients details.

What i want to do is add a text box above the list box. So that if you
enter anything in the text box it filters the list box and shows any
results
that contains the text entered. (Whether is be at the start in the middle
or
at the end or their surname. If the text box is left empty id shows all
fields.

The List box current has 3 fields (Clinet No, Client Surname, Client First
Name)

I have looked through the newsgroup but cant find exactly what im after.

Thanks

 

[Andrew C]

Thanks for help works brilliantly

Andrew

Try

Me.List36.RowSource = "SELECT CLIENt.[Client Id], CLIENt.Surname,
CLIENt.[First Name] FROM CLIENt Where (((CLIENt.Active)='Y')) and
client.surname Like '*" & Me.Search & "*' Order By Surname"

--
\\// Live Long and Prosper \\//
BS"D

Thanks Got it working OK with one little problem. It know wont sort the
list by surname. below is what i have got to work but i need to sort by
surname.

Have triend to add 'ORDER BY client.surname' on the end but keep getting a
compile error. what am i doing wrong

Private Sub Command48_Click()
Me.List36.RowSource = "SELECT CLIENt.[Client Id], CLIENt.Surname,
CLIENt.[First Name] FROM CLIENt Where (((CLIENt.Active)='Y')) and
client.surname Like '*" & Me.Search & "*'"

End Sub

Thanks

When you assign the RowSource, change the double quote to single quote for
the Y

SELECT CLIENt.[Client Id], CLIENt.Surname, CLIENt.[First Name] FROM CLIENt
WHERE (((CLIENt.Active)='Y')) ORDER BY CLIENt.Surname

--
\\// Live Long and Prosper \\//
BS"D


:

Im having problems with the code you told me.

I keep getting a compile error. And not sure why. Im not famailar with VB
code only know very little.

this is the current info i have in the Rowsource of the List box (not sure
if this is casuing a problem)

SELECT CLIENt.[Client Id], CLIENt.Surname, CLIENt.[First Name] FROM CLIENt
WHERE (((CLIENt.Active)="Y")) ORDER BY CLIENt.Surname;

cause the list box also need to filter out active and inactive members as
well, Has a field which has a "Y" or "N" for active or inactive.

any more info you need just ask

Thanks

:

Create a Text Box with a button next to it, after the use key in the text
he/she press the button to get the new list, if noting was entered, it will
display the full list, so if the user want to display the all list again all
he had to do is delete the content of the text box and pree the button

Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 Like '*" & Me.TextBoxName & "*'"

--
\\// Live Long and Prosper \\//
BS"D


:

I do want to modify the contents on the list box but it does not hae to be
after every key stroke. It can be after you leave the text box or hit enter
to move on.

Is there another way to do it. Something a bit simpler. I only have medium
knowledge of access. but its getting better with the help of this newsgroup.

Thanks

:

Andrew

It sounds like you are trying to modify the contents of the listbox, based
on what is entered in a textbox. One approach might be to (re-)set the
listbox's RowSource property after each keystroke in the textbox.

You might use the KeyPress event and dynamically build a SQL string that
you'd then assign to the listbox's RowSource property. Requery the listbox,
if necessary.

The modification you are doing to the SQL string would involve the "WHERE"
clause -- for example, if you had entered "Joh" into the textbox, your
(dynamic) SQL statement would include something like (untested aircode --
use your own fields/tables):

"SELECT {whatever} FROM {whereever} WHERE [SomeField]='Joh';"

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi

I have a client form that has a list box down the side. When you select a
name in the list box it updates the fields to show that clients details.

What i want to do is add a text box above the list box. So that if you
enter anything in the text box it filters the list box and shows any
results
that contains the text entered. (Whether is be at the start in the middle
or
at the end or their surname. If the text box is left empty id shows all
fields.

The List box current has 3 fields (Clinet No, Client Surname, Client First
Name)

I have looked through the newsgroup but cant find exactly what im after.

Thanks