lookup field in table

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hello everyone,



I want to make a lookup field in a table, in which the possibilities shown,
depend on what is entered into the previous field in the same table.



These are the relevant tables and their fields:

T00 Clients has these fields:

(BuildingNumber), ClientName, AreaNumber, AreaManager



T05 Suppliers has these fields:

(SupplierNumber), SupplierName



T10 Articles has these fields:

(SupplierNumber, ArticleNumber), ArticleDescription, Price



T20 Clients And Articles has these fields:

(BuildingNumber, SupplierNumber, ArticleNumber), DiscountPerc



The fields between parentheses are the primary keys of that table.

The fields BuildingNumber and SupplierNumber used in the tables T10 and/or
T20 have relationships with referential integrity refering to tables T00
and/or T05.

The combination {SupplierNumber_ArticleNumber} in table T20 is linked using
ref.int. to table T10 in which this combo is the primary key.




This is what I want to accomplish: When entering BuildingNumber,
SupplierNumber and ArticleNumber into table T20, the ArticleNumber field in
that table is to be a lookup field that mentions only the articles that have
been linked to that particular supplier in table T10.



I know that building a more sophisticated query in the Qeury section that
does work for that query, can supply a correct SQL instruction that can then
be copied to the Row source section in the table design. Getting that SQL
statement that works when put into that specific part however, seems not
possible, so far.



Can anyone help?



Greetings,

Peter
 
In your field options in design mode click on the Lookup tab and indicate
the table you wish to use as a lookup from there. You can also use a query
for lookup.
 
Hello everyone,



I want to make a lookup field in a table,
Click to expand...

Well... maybe you don't want to do so. Read
http://www.mvps.org/access/lookupfields.htm
for a critique of what many of us consider a misfeature.
in which the possibilities shown,
depend on what is entered into the previous field in the same table.
Click to expand...

This is difficult in a Table - very easy on a Form; this is but one of
many reasons that table datasheets should not be used for data entry,
period. Users shouldn't even SEE them, instead interacting with Forms
for all data manipulations.
This is what I want to accomplish: When entering BuildingNumber,
SupplierNumber and ArticleNumber into table T20, the ArticleNumber field in
that table is to be a lookup field that mentions only the articles that have
been linked to that particular supplier in table T10.
Click to expand...

If you're using a Form, you can set the RowSource property of the
ArticleNumber combo box to the value selected in the form's Supplier
combo box. This change can be made in the AfterUpdate event of the
Supplier combo box, or (a trick I've read but haven't yet tried) in
the GotFocus event of the ArticleNumber combo box itself.
 
Back
Top