Lookup table workaround

[aarrgghh765]

Hi,

I know this is a faq but I'm struggling to find examples of exactly
what I need.

I have two tables. One for contains orders, one contains a supplier
reference (key) and a supplier name.

I want to update one of the fields in an order with the reference of
the supplier, the reference should be picked by the user going into a
populated drop-down box, clicking on the Supplier name and then that
should insert the supplier reference into the orders table.

I'm told by the ten commandments of access that I should avoid Access'
lookup tables, but I'm struggling to do this in code.

Can anyone point me to an example that I can look at please? I've
tried the northwind database but it seems to use lookup tables and it's
too big for me to understand.

Thanks.

 

[Allen Browne]

The article at:
http://www.mvps.org/access/tencommandments.htm
is not saying to avoid lookup table or combos.
Both are valid and appropriate to every database.
It is only lookup-fields in tables that are seen as evil.

Do open the Relationships window and create a relationship with referential
integrity between your Order table and your Supplier table, based on the
common SupplierID field.

Then in your Order form, do use a combo for the SupplierID.
Typically it will have properties like this:
Column Count: 2
Column Widths: 0
Row Source: Select SupplierID, SupplierName
FROM Supplier ORDER BY SupplierName;

 

[Baz]

Hi,

I know this is a faq but I'm struggling to find examples of exactly
what I need.

I have two tables. One for contains orders, one contains a supplier
reference (key) and a supplier name.

I want to update one of the fields in an order with the reference of
the supplier, the reference should be picked by the user going into a
populated drop-down box, clicking on the Supplier name and then that
should insert the supplier reference into the orders table.

I'm told by the ten commandments of access that I should avoid Access'
lookup tables, but I'm struggling to do this in code.

Can anyone point me to an example that I can look at please? I've
tried the northwind database but it seems to use lookup tables and it's
too big for me to understand.

Thanks.

I think you are slightly confused. There is absolutely nothing wrong with
using lookup tables, so long as you only define their use in the combo box
properties *on the form*. You have probably encountered people strongly,
and correctly, advising against defining lookups *on the table* (as is done
in Northwind, yuk).