List box

[kettch]

I need help on making a list box do a specific action.

Say i had a list box A with options "foo" and "bewm"
and another list box B with option "bar" "monkey" "spank" "luser" and "phear"

My question is what would I have to do in order to make it so when someone clicks on the option "foo" in list box A that when they look at the contents of B that they would be limited to only "bar" and "phear"
and if they clicked option "bewm" in list box A that in list box B the only available options would be "monkey" "spank" and "luser"

so basically what i need to know is how to limit the contents of one list box based on what was selected in another.

if you need more details let me know.

Thanks!

 

[dschomburg]

Check out thread 181-59670 "Can anyone help with Dlookup"
I think it might be the same thing you're looking for.
It should be around the 5 group of threads (if you're set to display 10 per page)
Dave

 

[kettch]

This works ok for 1 value for 1 item, but i may need access to know several values that correspond to a selection in list box one.

example:

if i have computer parts and i choose "monitor" i want the other list box to only let me select from certain manufacturers, and not all manufacturers that are available.

 

[dschomburg]

Send me your email adrs and I'll send an example that someone else sent to me. It will help. Full of great examples.
My email = dave_r_schomburg@bankone.com

 

[RickSpr]

You should have a table that contains all the valid combinations of values from both combo boxes.

Set the RowSource of the second combo box to a SQL statement that selects values based on the first combo box's value. For instance:
SELECT Manufacturer FROM PartsTable WHERE Manufacturer = Forms!ThisForm!ComboBox1.Value" Rick Sprague

 

[Nebuchednezzar]

put an after update event on list box a.
You can have the source for list box 2 be dependent on a query that uses the value selected in list box a as a variable in the query.

You would need to have a way in a table to identify which values in list box b correspond to the values in list box a.
(like a table such as...
MANUFACTURER SUPPLIES
------------ --------
FOO MONITORS
FOO-YOO MICE

Then, query such as select * from mfg_supplies where supplies = "MONITORS";
(pass MONITORS in as a variable).

 

[ghubbell]

You have to create a relationship between the two by some means: You may as well do it the right way regardless of the quantity of records you have...
We'll do it the proper way (not really very hard):

Table1 with an autonumber "ID#" and a text field. You enter in "foo" and "bewm" as records. Your ID Number Indexed property must be set to Yes (No duplicates).

Table2 with a long integer field and a text field. This long integer field Indexed property must be set to No or Yes(duplicatesOK). You enter in "bar" "monkey" "spank" "luser"... as the text records and enter in the ID# that has been created for you in table1 that matches table2:

Say "foo" ends up being ID# 1. In table 2 "bar" and "phear" get a 1 as their connecting ID. If your with me so far...This is relational database design. Table1 can have Many records (Table2). In order to formalize this relationship you have to go to the database window, right click and select relationships. Click show all tables and atleast your two tables should appear. Now you have to drag the ID# from Table1 to the ID# of Table2. A box will pop up and ask you the type of relationship. It should have already determined that this is a one to many relationship, and you can choose if you want to automatically update and or delete the records in the related table (table2). Choose what you think is suitable for your needs. Were done here so lets go to the lists!

Use the wizard and drop a list box on to your form. Its data should come from table1 and you should hide the first (ID#) column. Do the same with the second listbox however its data comes from table2. When it is on the form select its property sheet find the Row Source and click on the ellipse "..." button to the right. This will bring up a query that the list box has built. In criteria under the ID# field add =[EnterTheNameOfTheFirstListBoxHere]...not literally. Close the query, move back to the first list box.
Double click on it to see its properties. Select the Event tab and select after update 'till it reads "event procedure". Again, click on the ellipse "..." button beside. This will open the Visual Basic environment and automatically label up the event for you. In between the lines you'll have to add this:
Me![EnterTheNameOfTheSecondListBox].Requery
Close the VB environment, close and save your form. Open it up and if all is well it should fly!




Gord
ghubbell@total.net

 

[7ony]

The folloging is placed on the GotFocus Event of a Combo Named SubClass. Is simply changes its own Rowsource based on the value of another Combo Box named Class. The table Fassets is the table attached to the form, and the values are a limited set coming from the same table.
---
Private Sub SubClass_GotFocus()
If Len([Class]) Then
SubClass.RowSource = "SELECT SubClass FROM Fassets WHERE Class=" & [Class] & " GROUP BY SubClass ORDER BY SubClass"
SubClass.Requery
Else
SubClass.RowSource = ""
SubClass.Requery
End If
End Sub
---
Hope it helps...
7ony