Listbox to control listbox

[NDClark]

I have a list box with all phone numbers in it. I would like to be able to
filter the listbox phone numbers by category.

In other programs, I have seen two listboxes side by side and one be
clickable to filter the results of the other listbox. How can I accomplish
this result?

Any suggestions on this would be helpful.

 

[NDClark]

Ok, I have set up the Category Table with PC ID as the primary key.
Also as a side note. I have created a relationship between both tables
linking the PC ID fields.

The List box name for the Category is ListCategory
The List box name for the Phone Numbers is ListNumbers

Private Sub ListCategory_AfterUpdate()
Me!cbo_ListNumbers.RowSource = "SELECT Home number FROM "
Phone numbers WHERE PC ID= " & Me!cbo_Phone Category
End Sub

I am getting a Syntax error on the WHERE

 

[Rob Parker]

If you really have a space in the field names (not good practice), you must
enclose the field names in square brackets. The same applies for the table
and control names. You'll need:
Me!cbo_ListNumbers.RowSource = "SELECT [Home number] FROM [Phone numbers]
WHERE [PC ID] = " & Me![cbo_Phone Category]

HTH,

Rob

 

[John W. Vinson]

Ok, I have set up the Category Table with PC ID as the primary key.
Also as a side note. I have created a relationship between both tables
linking the PC ID fields.

The List box name for the Category is ListCategory
The List box name for the Phone Numbers is ListNumbers

Private Sub ListCategory_AfterUpdate()
Me!cbo_ListNumbers.RowSource = "SELECT Home number FROM "
Phone numbers WHERE PC ID= " & Me!cbo_Phone Category
End Sub

I am getting a Syntax error on the WHERE

As Rob said, you need brackets - blanks are meaningful, and should not be in
fieldnames; in addition, if [PC ID] is a Text field rather than a Number you
need quotemarks around the criterion:

Me!cbo_ListNumbers.RowSource = "SELECT [Home number] FROM " _
& "[Phone numbers] WHERE [PC ID]= '" _
& Me!cbo_Phone Category & "'"

This will set the rowsource to a string like

SELECT [Home number] FROM [Phone numbers] WHERE [PC ID] = 'Cell'

If the ID is a number field ignore this suggestion (until you need to use a
text field in some other context).

 

[NDClark]

Ok. I am no longer getting a syntax error. Thanks for that.

The two listboxes are still not doing what I need them to do. I want to be
able to click the Category listbox and filter out the category I have for
that particular phone number. The two listboxes are side by side on the same
form.

PC ID is an autonumber ID field of my Phone Category table.

Phone numbers is the name of the table with the phone numbers
Phone Category is the name of the phone categories, such as 'family,
friends, associates etc.'

ListNumbers is the name of the Listbox listing all the phone numbers and
names.
List Category is the name of the Listbox listing just the categories.

I have both tables joined by the PC ID field.

I have the following code in the AfterUpdate of the ListNumbers Listbox:

Private Sub ListNumbers_AfterUpdate()
Me!cbo_ListNumbers.RowSource = "SELECT [PC ID] FROM [Phone numbers] WHERE
[PC ID]= " & Me![cbo_ListCategory]
End Sub

This is the Row Source for the ListCategory Listbox
SELECT [Phone Category].[PC ID], [Phone Category].[Category] FROM [Phone
Category] WHERE [PC ID];

This is the Row Source for the ListNumbers listbox
SELECT [Phone numbers].[Phone numbers ID], [Phone numbers].[First name],
[Phone numbers].[Last name], [Phone numbers].[Home number], [Phone
numbers].[2nd number] FROM [Phone numbers];

There is a combo box to select on the form where I edit the phone numbers.
The combo box has options for the PC ID 'Category such as Family, Friends,
etc'. These categories are saving to the Phone number table for each phone
number.

Ok, I have set up the Category Table with PC ID as the primary key.
Also as a side note. I have created a relationship between both tables
linking the PC ID fields.

The List box name for the Category is ListCategory
The List box name for the Phone Numbers is ListNumbers

Private Sub ListCategory_AfterUpdate()
Me!cbo_ListNumbers.RowSource = "SELECT Home number FROM "
Phone numbers WHERE PC ID= " & Me!cbo_Phone Category
End Sub

I am getting a Syntax error on the WHERE

As Rob said, you need brackets - blanks are meaningful, and should not be in
fieldnames; in addition, if [PC ID] is a Text field rather than a Number you
need quotemarks around the criterion:

Me!cbo_ListNumbers.RowSource = "SELECT [Home number] FROM " _
& "[Phone numbers] WHERE [PC ID]= '" _
& Me!cbo_Phone Category & "'"

This will set the rowsource to a string like

SELECT [Home number] FROM [Phone numbers] WHERE [PC ID] = 'Cell'

If the ID is a number field ignore this suggestion (until you need to use a
text field in some other context).

 

[NDClark]

Sorry I did not make this clear.

I want to choose a category from the category box. Then see the phone
numbers box
display the numbers which are marked to be in the category choosen
in the category box.

Ok I will delete the Row Source of the Phone Number box.

Ok. I am no longer getting a syntax error. Thanks for that.

The two listboxes are still not doing what I need them to do.

Then you need to explain that to us somewhat more clearly

What the now _should_ do is:

if you choose a category from the category-box, the phone-numbers box
should only list numbers which are marked to be in the category choosen
in box 1.

I have both tables joined by the PC ID field.

I have the following code in the AfterUpdate of the ListNumbers Listbox:

Private Sub ListNumbers_AfterUpdate()
Me!cbo_ListNumbers.RowSource = "SELECT [PC ID] FROM [Phone numbers] WHERE
[PC ID]= " & Me![cbo_ListCategory]
End Sub

This seems to me as a circular dependency: The list shown in the list
bos depends on what the user selects from the same list.

The code you show above ist code vor the categorie's list "AfterUpdate"

This is the Row Source for the ListCategory Listbox
SELECT [Phone Category].[PC ID], [Phone Category].[Category] FROM [Phone
Category] WHERE [PC ID];

This is the Row Source for the ListNumbers listbox
SELECT [Phone numbers].[Phone numbers ID], [Phone numbers].[First name],
[Phone numbers].[Last name], [Phone numbers].[Home number], [Phone
numbers].[2nd number] FROM [Phone numbers];

You cannot have two Rowsources for the listboxes.

so as I said at the top of this post: please describe somwhat more
clearly what you expect the boxes to do.

Volker

 

[NDClark]

Volker, I am totally lost now. I am sorry I am a newbie to Access

I have one list box with Categories -ListCategory
I have another list box with Phone numbers - ListNumbers

You said I do not need two RowSources. How can I display the Phone numbers
in the second box if there is no RowSource? I am missing something here.

If I click on ListCategory and select say 'Family' I want the numbers in the
ListNumber box to display only the phone numbers I have categorized as
'Family'

How can I accomplish this?
I have put the AfterUpdate code in the ListCategory listbox:

Private Sub ListCategory_AfterUpdate()
Me!cbo_ListNumbers.RowSource = "SELECT [Home number]FROM [Phone numbers]"
WHERE [PC ID] = " & Me![cbo_ListCategory]"
End Sub

On the SELECT [Home number] I dont think I am calling the right thing. The
home number refers to a field on the Phone numbers table. Should I have
something different in that?

Ok. I am no longer getting a syntax error. Thanks for that.

The two listboxes are still not doing what I need them to do.

Then you need to explain that to us somewhat more clearly

What the now _should_ do is:

if you choose a category from the category-box, the phone-numbers box
should only list numbers which are marked to be in the category choosen
in box 1.

I have both tables joined by the PC ID field.

I have the following code in the AfterUpdate of the ListNumbers Listbox:

Private Sub ListNumbers_AfterUpdate()
Me!cbo_ListNumbers.RowSource = "SELECT [PC ID] FROM [Phone numbers] WHERE
[PC ID]= " & Me![cbo_ListCategory]
End Sub

This seems to me as a circular dependency: The list shown in the list
bos depends on what the user selects from the same list.

The code you show above ist code vor the categorie's list "AfterUpdate"

This is the Row Source for the ListCategory Listbox
SELECT [Phone Category].[PC ID], [Phone Category].[Category] FROM [Phone
Category] WHERE [PC ID];

This is the Row Source for the ListNumbers listbox
SELECT [Phone numbers].[Phone numbers ID], [Phone numbers].[First name],
[Phone numbers].[Last name], [Phone numbers].[Home number], [Phone
numbers].[2nd number] FROM [Phone numbers];

You cannot have two Rowsources for the listboxes.

so as I said at the top of this post: please describe somwhat more
clearly what you expect the boxes to do.

Volker

 

[NDClark]

Ok. I see some of the numbers in the lstPhoneNumbers and I see all the
Categories in the lstCategories boxes.

Still unable to get the lstPhoneNumbers to filter out when I select the
lstCategories.

I do have the following code in the AfterUpdate of the lstCategories listbox:
Private Sub lstCategories_AfterUpdate()
Me.lstPhoneNumbers.Requery
End Sub

Now, if I select a Category and click the Refresh All button on the Ribbon
it will show only the numbers for that category.

 

[Nathan D Clark]

The Row Source is
SELECT Contacts.ContactID, (FirstName+" ") & LastName, PhoneNumber, Category
FROM Contacts INNER JOIN ContactPhones ON
Contacts.ContactID=ContactPhones.ContactID WHERE
PhoneCategory=Form!lstCategories Or Form!lstCategories Is Null ORDER BY
LastName, FirstName;

This sort of thing is pretty routine, so I can't see why it doesn't work in
your case. What's the RowSource property of the lstPhoneNumbers control?

Ken Sheridan
Stafford, England

Ok. I see some of the numbers in the lstPhoneNumbers and I see all the
Categories in the lstCategories boxes.

Still unable to get the lstPhoneNumbers to filter out when I select the
lstCategories.

I do have the following code in the AfterUpdate of the lstCategories listbox:
Private Sub lstCategories_AfterUpdate()
Me.lstPhoneNumbers.Requery
End Sub

Now, if I select a Category and click the Refresh All button on the Ribbon
it will show only the numbers for that category.

Correction: The second list box, lstPhoneNumbers, should have a RowSource
property of:

[quoted text clipped - 11 lines]

Ken Sheridan
Stafford, England

 

[Nathan D Clark]

It is working now. My mistake Ken. I added another field on the
phonenumbers table and linked it to PhoneCategories table. That was my
problem. Thank you for everything and your patience with me.

Peace.

This sort of thing is pretty routine, so I can't see why it doesn't work in
your case. What's the RowSource property of the lstPhoneNumbers control?

Ken Sheridan
Stafford, England

Ok. I see some of the numbers in the lstPhoneNumbers and I see all the
Categories in the lstCategories boxes.

Still unable to get the lstPhoneNumbers to filter out when I select the
lstCategories.

I do have the following code in the AfterUpdate of the lstCategories listbox:
Private Sub lstCategories_AfterUpdate()
Me.lstPhoneNumbers.Requery
End Sub

Now, if I select a Category and click the Refresh All button on the Ribbon
it will show only the numbers for that category.

Correction: The second list box, lstPhoneNumbers, should have a RowSource
property of:

[quoted text clipped - 11 lines]

Ken Sheridan
Stafford, England