SQL or Query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

VB/Access Newbie - Not sure how I would go about performing the following:

I have a combo box which displays several items from a table. There may be 3
associated rows for item1 but I used the DISTINCT keyword to limit the
display of item1. Upon selection of item1, I want to populate a text or list
box with the appropriate values(qty and color) from the item selected from
the combo box.

Sample data base:

Item Qty Color
A 3 Red
A 6 Blue
A 3 White
B 2 White
B 3 Blue
C 5 Red

Thanks in advance!
 
In the combo RowSource include all the fields

Select Item ,Qty, Color From TableName

Make sure that the combo ColumnCount include 3 columns
You can set the column width not to display a record

In the text boxes contrl source you can write
Qty text box:
=[ComboName].Column(1)

Color text box:
=[ComboName].Column(2)

===============================
Or, you can use the after update event of the combo to set both text boxes

Me.[QtyTextBoxName]=Me.[ComboName].Column(1)
Me.[ColorTextBoxName]=Me.[ComboName].Column(2)

Note: the column count of the combo start with 0
 
Thanks Ofer. I originally had it set up that way but that would not produce
the desired effect.

For the combobox, I have it set to Select Distinct so that it would only
display a single instance of Item A,B,C. Once one of those values are
selected, for example "A", I want to poplate QtyTxt with values 3,6,3 and
ColorTxt with Red, Blue, White.

I've been reading other threads and it seems like the approach I would use
would be to use an SQL statement with a variable based on the combobox value?
The samples I saw didn't seem to apply to my needs but I tried a couple with
no success.

Ofer Cohen said:
In the combo RowSource include all the fields

Select Item ,Qty, Color From TableName

Make sure that the combo ColumnCount include 3 columns
You can set the column width not to display a record

In the text boxes contrl source you can write
Qty text box:
=[ComboName].Column(1)

Color text box:
=[ComboName].Column(2)

===============================
Or, you can use the after update event of the combo to set both text boxes

Me.[QtyTextBoxName]=Me.[ComboName].Column(1)
Me.[ColorTextBoxName]=Me.[ComboName].Column(2)

Note: the column count of the combo start with 0

--
Good Luck
BS"D


BillyMo68 said:
VB/Access Newbie - Not sure how I would go about performing the following:

I have a combo box which displays several items from a table. There may be 3
associated rows for item1 but I used the DISTINCT keyword to limit the
display of item1. Upon selection of item1, I want to populate a text or list
box with the appropriate values(qty and color) from the item selected from
the combo box.

Sample data base:

Item Qty Color
A 3 Red
A 6 Blue
A 3 White
B 2 White
B 3 Blue
C 5 Red

Thanks in advance!
Click to expand...
Click to expand...
 
Back
Top