CODEWITHSUNDEEP
sqlms-accessms-access-2013
J.Olufsen
J.Olufsen

Reputation: 13915

Invalid control source in MS Access while linking data by foreign keys

I need to create form which holds data Country.country_name for current Supplier.supplier_id of current Product record. A product has FK supplier_id. Table Supplier has FK country_id. Table Country has two fields: country_id and country_name. Tried to put SQL query to get country_name of current product but got an error "Invalid control source". How fix it?

SQL query: 

SELECT Country.country_name FROM 
Country INNER JOIN 
(Supplier INNER JOIN Product ON Product.supplier_id=Supplier.supplier_id) 
ON Supplier.country_id=Country.country_id;

enter image description here

Solved:

Main Form has to have following query SELECT * FROM (Country INNER JOIN Supplier ON Country.country_id = Supplier.country_id) INNER JOIN Product ON Supplier.supplier_id = Product.supplier_id; in Property sheet > Data > Record source.

Upvotes: 2

Views: 3999

Answers (2)

HansUp
HansUp

Reputation: 97101

The combo's Control Source property is the form's record source field to which you want to bind the combo. So it sounds like the form's record source does not include a field named country_name. (It doesn't matter here that the combo's Row Source does include that field --- you won't be binding the combo to it.)

On the combo's property sheet, click the drop-down arrow on the right side of the Control Source box. It should display the names of all the fields it recognizes as included in the form's record source.

Upvotes: 3

mwolfe02
mwolfe02

Reputation: 24227

The resulting field name in that query will be country_name (i.e., without the Country. prefix). Just use country_name as your control source.

Upvotes: 2

Related Questions