Code Issue

[Eric]

What is the BeforeUpdate code to instruct a listbox to
search for information to display from a table based upon
an item entered into another box on a form? Example...

Table 1
A B
1 Red Tomato
2 Red Pepper
3 Yellow Banana
4 Orange Orange

Form 1
ComboBox1= Red ListBox1=Tomato,Pepper(2 to Choose From
ComboBox1= Yellow ListBox1=Banana
ComboBox1= Orange ListBox1=Orange

I can get the ComboBox to Display the 3 choices from
column A, although it displays "Red" 2x. But I only want
the choices in ListBox1 to show up for the corresponding
colors in ComboBox1. I've been wrestling with this for
about 2 weeks and I just can't seem to figure it out. Any
help would be greatly appreciated. Thanks in advance!
Eric

 

[Glen Appleton]

Hi Eric,

First, in combo1 (the colors), use the DISTINCT parameter in the rowsource
SQL statement to avoid duplicate listings:

SELECT DISTINCT [A] FROM [Table1];

Next, use the value in conbo1 to filter the items in combo 2:

Private Sub combo1_Click()

combo2.RowSource = "SELECT DISTINCT FROM [Table1] WHERE ([A] = '" &
combo1.Value & "');"
combo2.Requery

End Sub

This should be enough to put you in the right direction. :~)-

Hope this helps,
- Glen