Code Issue

  • Thread starter Thread starter Eric
  • Start date Start date
E

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
 
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
 
Back
Top