How can I extract the value of other fields of table 1 using its PK in table 2

[wasseypurian]

I have two tables which are as follows

1. Table Project now referred as tblProj, whose columns are
ID | ProjCode | ProjName | Country |.. | CountryName
2. Table Country as tblCountry, whose columns are
ID(PK) | Country


In tblProj I have stored input from the Form but the value stored in the country column Country of tblProj is the primary key value of the Country(in tblCountry)

Now I am trying to generate a code (ProjCode) which uses the actual requires the text value of "Country" in tblCountry, but when I refer to [Country] in tblProj I get the numeric value(its PK value).

I have tried to add a new column in tblProj as "CountryNam e" using the query

SELECT list_country.ID, list_country.Country
FROM tbl_project INNER JOIN list_country ON tbl_project.Country = list_country.ID
WHERE (((list_country.ID)=[tbl_project].[Country]));

but this lists all the county name instead of just the particular country with the ID equal to the value stored in that particular row in tblProj

I have even tried to set "CountryNam e" using Dlookup in the form in the Private Sub_Country_Aft erUpdate() as

Me.CountryName = Dlookup("[Country]","[tblCountry]","[tbl_Country].[ID] = [Forms]![Project Form]![Country]")

but this one does not seem to work.

I have an idea to modify the primary key of tblCountry which include the first character of each country but I just found out that calculated field values cannot be set as the primary key.
Thanks in advance :)

[wasseypurian]

I tried using Ubound box in the form that displays the desired code but unable to store that particular value in the table.

[twinnyfo]

wasseypurian,

Your question is very confusing, but I think it is because of unclear English, more so than you question.

In addition, you say:

1. Table Project now referred as tblProj, whose columns are
ID | ProjCode | ProjName | Country |.. | CountryName
2. Table Country as tblCountry, whose columns are
ID(PK) | Country

Your code says:

SELECT list_country.ID, list_country.Country 
FROM tbl_project INNER JOIN list_country ON tbl_project.Country = list_country.ID 
WHERE (((list_country.ID)=[tbl_project].[Country]));

What are the names of your tables????

Second, Your query is doing exactly what you want it to do. You are asking the query to generat a list of countries, which have an entry in your Projects Table.

Third, You have your Tables (if your descsribing them accurately) set up correctly with a PK for each record, and it seems your projects table properly looks at the index, and not the actual tet ofr the country. This is great! Why would you now want to mess with your PKs???? Don't break a good thing!

Fourth, what, exactly, is your question? What ar eyou trying to do? Everything points to the index of your Countries Table, which is how it ought to be, so I am confused about trying to assign the text value somewhere.

Please provide a little better description. Thanks!