How to populate combo box with values in MS Access 2007

Question

I have a database with 3 tables.

Manufact: ID, Name, Decription

Model: ID, Manufact_ID, Name

Order: ID, Manufact_ID, Model_ID, Quantity, Date,

In Access I would like to have the normal table/datasheet view for Order which gives the following output

ID Manufact.Name Model_ID(combo box) Quantity Date  
1  LG            W3000H-BN            10       02-01-2009
2  SAMSUNG       SyncMaster 205BW      5       02-20-2009
3  SAMSUNG       SyncMaster 206BW      2       03-18-2009
4  Iiyama        ProLite E1902S-B1     1       05-13-2009

My problem is the combo box. I want to have only the list of the Models of the curent Manufacturer selectable in the combo box. e.g: only the SAMSUNG models if SAMSUNG is selected. I tried to set the Lookup row source for Model_ID column as

SELECT Model.ID,Model.Manufact_ID,Model.Name,Order.Manufact_ID \ 
FROM Model, Order WHERE Order.Manufact_ID = Model.Manufact_ID

This is actually an Model INNER JOIN Order ON ... giving the wrong result. How can I refer to the actual value of Order.Manufact_ID in the actual row's Lookup SELECT command? It's a variable value I cannot stringify.

Thanks!

strauss

Answer 1

Unfortunately this isn't possible. A combo box on a form can only have 1 source, it cannot have different sources for each record on a datasheet or multi-record form.

Answer 2

You may wish to read this tread which seems to be the same as yours. It provides a solution:

http://stackoverflow.com/questions/927256/is-there-a-simple-way-of-populating-dropdown-in-this-access-database-schema/927588#927588