Tricky one

[Fredrik]

Hello!

I have three tables:

SUPPLIERS

Supplier 1
Supplier 2
Supplier 3

LOADINGPLACES

Loadingplace 1 Supplier 1
Loadingplace 1 Supplier 2
Loadingplace 2 Supplier 3
Loadingplace 3 Supplier 3

And ORDERS

Ordernumber Supplier Loadingplace

When i'm in the ordertable i put the order number. Then i've made a "list"
for Supplier with a link to Table SUPPLIERS so that i can choose which
supplier with the mouse.

Now to the tricky part. For Loadingplaces I only want to see the
loadingplaces available for the selected supplier

In this example:

2345689 Supplier 3 | |
-Loadingplace 2
-Loadingplace 3

How do I do this? I'm quite new at Access so a little more info than a
SQL-string would be appreciated.
Hope that someone can help me with this. (Our two experts in the company
doesn't have a clue).

Regards
Fredrik

 

[Beetle]

For starters, it sounds like you are working directly in the tables and using
lookup fields. If that's the case, then that is the first thing you should
correct.
See this link for reasons why;

http://www.mvps.org/access/lookupfields.htm

Next, I can't tell from your post, but your tables/fields should look
something
like the following;

tblSuppliers
*********
SupplierID (Primary Key)
SupplierName
Address
City
State
other fields related specifically to a supplier

tblLoadingPlaces
************
LPID (PK)
Description
other fields related specifically to a loading place

tblOrders
*******
OrderID (PK)
SupplierID (Foreign Key to tblSuppliers)
LPID (FK to tblLoadingPlaces)
other data related to an order

Only the ID numbers for Suppliers and Loading Places should appear
in the Orders table, not the actual names. You also have a Supplier field
in your Loading Places table, which may or may not be correct depending
on the relationships in your app (I don't know). But if a supplier field does
belong in that table, again, it should only be the SupplierID.

Now for your original question, what you want to do is called
cascading combo boxes, but it must be done using combo boxes in a
form, not lookup fields in a table. If my previous assumptions were wrong
and you are already using a form with combo boxes, then you will find
plenty of info on how to set them up if you search for "cascading combo
boxes".

Post back if you have questions.