Advanced lookup fields

G

Guest

Hello people,

I hope I can explain clearly what I want :) I have these three tables:

Table1
TypeID Desc
====== -------
1 Type1
2 Type2
3 Type3
4 Type4
99 Unknow


Table2
SubID TypeID* SubTipo
===== --------- ---------
1 Type1 SubType1a
2 Type1 SubType1b
3 Type1 SubType1c
4 Type2 SubType2a
5 Type2 SubType2b
6 Type2 SubType2c
7 Type3 SubType3a
8 Type3 SubType3b
9 Type3 SubType3c
10 Type4 SubType4a
11 Type4 SubType4b
12 Type4 SubType4c



Table3
ID TypeId* SubType Field1 Field2
== --------- --------- -------- -----------
1 Type1 SubType1c xxx yyy
2 Type3 SubType3b xxx yyy


* Numeric fields created with lookup wizard to show the Desc from Table1.

The TypeID from Table1 has a relashionship of 1-to-many to TypeID in
table2 as well as 1-to-many to TypeId of Table3. I'm missing something at this
point... When the user selects the TypeID in the lookup of Table3, the SubType
lookup should change to reflect only the subtypes for the users' choice,
i.e. User selects Type2 in the TypeID and the SubType should only list
SubType2a, SubType2b and SubType2c.
I've tried the following:

- Display Control: ComboBox
- Row Source Type: Table/Query
- Row Source: SELECT [Table2].[SubTipo] FROM Table2 WHERE
[Table2].[TypeID]=[Table3].[TypeID];

Bu it didn't work. I select the Table3 TypeId and when I click on the Table3
SubType combobox Access ask me for the ID, it doesn't read the value
from TypeID :(

Thanks for all,
rbasniak
 
D

Dirk Goldgar

rbasniak said:
Hello people,

I hope I can explain clearly what I want :) I have these three tables:

Table1
TypeID Desc
====== -------
1 Type1
2 Type2
3 Type3
4 Type4
99 Unknow


Table2
SubID TypeID* SubTipo
===== --------- ---------
1 Type1 SubType1a
2 Type1 SubType1b
3 Type1 SubType1c
4 Type2 SubType2a
5 Type2 SubType2b
6 Type2 SubType2c
7 Type3 SubType3a
8 Type3 SubType3b
9 Type3 SubType3c
10 Type4 SubType4a
11 Type4 SubType4b
12 Type4 SubType4c



Table3
ID TypeId* SubType Field1 Field2
== --------- --------- -------- -----------
1 Type1 SubType1c xxx yyy
2 Type3 SubType3b xxx yyy


* Numeric fields created with lookup wizard to show the Desc from
Table1.

The TypeID from Table1 has a relashionship of 1-to-many to TypeID in
table2 as well as 1-to-many to TypeId of Table3. I'm missing
something at this point... When the user selects the TypeID in the
lookup of Table3, the SubType lookup should change to reflect only
the subtypes for the users' choice,
i.e. User selects Type2 in the TypeID and the SubType should only list
SubType2a, SubType2b and SubType2c.
I've tried the following:

- Display Control: ComboBox
- Row Source Type: Table/Query
- Row Source: SELECT [Table2].[SubTipo] FROM Table2 WHERE
[Table2].[TypeID]=[Table3].[TypeID];

Bu it didn't work. I select the Table3 TypeId and when I click on the
Table3 SubType combobox Access ask me for the ID, it doesn't read the
value
from TypeID :(

Thanks for all,
rbasniak
Click to expand...

You can't do this in a table, but you can do it on a form. See the two
techniques demonstrated at

http://www.mvps.org/access/forms/frm0028.htm
 
V

Van T. Dinh

BTW, you store redundant data in Table3. There is no need for the Field
TypeID in Table3 since with the assigned SubType value, you can derive the
Type / TypeID.

HTH
Van T. Dinh
MVP (Access)
 
G

Guest

Thank you so much, it's working now...

Best regards,
rbasniak

Dirk Goldgar said:
rbasniak said:
Hello people,

I hope I can explain clearly what I want :) I have these three tables:

Table1
TypeID Desc
====== -------
1 Type1
2 Type2
3 Type3
4 Type4
99 Unknow


Table2
SubID TypeID* SubTipo
===== --------- ---------
1 Type1 SubType1a
2 Type1 SubType1b
3 Type1 SubType1c
4 Type2 SubType2a
5 Type2 SubType2b
6 Type2 SubType2c
7 Type3 SubType3a
8 Type3 SubType3b
9 Type3 SubType3c
10 Type4 SubType4a
11 Type4 SubType4b
12 Type4 SubType4c



Table3
ID TypeId* SubType Field1 Field2
== --------- --------- -------- -----------
1 Type1 SubType1c xxx yyy
2 Type3 SubType3b xxx yyy


* Numeric fields created with lookup wizard to show the Desc from
Table1.

The TypeID from Table1 has a relashionship of 1-to-many to TypeID in
table2 as well as 1-to-many to TypeId of Table3. I'm missing
something at this point... When the user selects the TypeID in the
lookup of Table3, the SubType lookup should change to reflect only
the subtypes for the users' choice,
i.e. User selects Type2 in the TypeID and the SubType should only list
SubType2a, SubType2b and SubType2c.
I've tried the following:

- Display Control: ComboBox
- Row Source Type: Table/Query
- Row Source: SELECT [Table2].[SubTipo] FROM Table2 WHERE
[Table2].[TypeID]=[Table3].[TypeID];

Bu it didn't work. I select the Table3 TypeId and when I click on the
Table3 SubType combobox Access ask me for the ID, it doesn't read the
value
from TypeID :(

Thanks for all,
rbasniak
Click to expand...

You can't do this in a table, but you can do it on a form. See the two
techniques demonstrated at

http://www.mvps.org/access/forms/frm0028.htm

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
G

Guest

Thanks for the tip Van...

Regards,
rbasniak

Van T. Dinh said:
BTW, you store redundant data in Table3. There is no need for the Field
TypeID in Table3 since with the assigned SubType value, you can derive the
Type / TypeID.

HTH
Van T. Dinh
MVP (Access)


rbasniak said:
Hello people,

I hope I can explain clearly what I want :) I have these three tables:

Table1
TypeID Desc
====== -------
1 Type1
2 Type2
3 Type3
4 Type4
99 Unknow


Table2
SubID TypeID* SubTipo
===== --------- ---------
1 Type1 SubType1a
2 Type1 SubType1b
3 Type1 SubType1c
4 Type2 SubType2a
5 Type2 SubType2b
6 Type2 SubType2c
7 Type3 SubType3a
8 Type3 SubType3b
9 Type3 SubType3c
10 Type4 SubType4a
11 Type4 SubType4b
12 Type4 SubType4c



Table3
ID TypeId* SubType Field1 Field2
== --------- --------- -------- -----------
1 Type1 SubType1c xxx yyy
2 Type3 SubType3b xxx yyy


* Numeric fields created with lookup wizard to show the Desc from Table1.

The TypeID from Table1 has a relashionship of 1-to-many to TypeID in
table2 as well as 1-to-many to TypeId of Table3. I'm missing something at this
point... When the user selects the TypeID in the lookup of Table3, the SubType
lookup should change to reflect only the subtypes for the users' choice,
i.e. User selects Type2 in the TypeID and the SubType should only list
SubType2a, SubType2b and SubType2c.
I've tried the following:

- Display Control: ComboBox
- Row Source Type: Table/Query
- Row Source: SELECT [Table2].[SubTipo] FROM Table2 WHERE
[Table2].[TypeID]=[Table3].[TypeID];

Bu it didn't work. I select the Table3 TypeId and when I click on the Table3
SubType combobox Access ask me for the ID, it doesn't read the value
from TypeID :(

Thanks for all,
rbasniak
Click to expand...
Click to expand...
 
Top