Combo Box, Rowsource problem

  • Thread starter Thread starter Meilu
  • Start date Start date
M

Meilu

Hello,

I posted a similiar question on the Forms Newsgroup but
received no reply there. So I'm going to try my luck
here :-)

Basically I have
Table Z : Client, Color, BLAH
Table A : ClientName, ID , Color, BLAH......
Table B : ID, Color, BLAH.....

Table A's Color field is a combo box. With the row
source:
SELECT Z.zColor FROM Z WHERE Z.Client=ClientName;
This successfully pulls all the Color values that the
given Client has.

I want Table B's Color field, which is also a combo box,
to pull it's values from Table A. The two tables are
linked by their ID field.
So I set the row source for B.Color as this:
SELECT A.Color FROM A WHERE A.ID=ID:

This DOES NOT work successfully. Instead of ONLY pulling
the colors with the same ID as the current record ... it
takes colors from ALL matching records.

For example:

This is what happens ---------->
record 1 A: 1, red .......
1, blue ............
1, white .........
B.Color = red blue white

record 2 A: 2, pink
2, magenta
B.Color = red blue white pink magenta

This is what I WANT to happen ---------->
record 1 A: 1, red .......
1, blue ............
1, white .........
B.Color = red blue white

record 2 A: 2, pink
2, magenta
B.Color = pink magenta


I don't understand why it's not working. It seems like
the same situation as pulling A.Color from Z ... but it
is not working the same way. Any ideas?

Thanks for ANYTHING!
Meilu
 
I think the problem is here

SELECT A.Color FROM A WHERE A.ID=ID:

Somehow you need to specify the second ID is from the form and not from A.
For example
SELECT A.Color FROM A WHERE A.ID=forms!MyForm!ID
 
Are there other records with the same ID? Your exmaple has A:2, Color, ...
Is there a B:2, Color, ...? Other than that, I do not see why other values
would be pulled in to your row source.

Kelvin Lu
 
Back
Top