New to Access

[mikke3141]

Hi,
I'm trying to update a Access database and now the update is updating too much. I need to get the country information from a linked country table so that the correct value (countryID) is maintained in the employee table. Now the first example changes the country table what is should not. How do I get so that the access update expression picks the correct key from the country table based on the country name? The first picture updates the country table, but I just want the matching number to employee table. The second picture shows what I would like to code to do, but it does not work

[June7]

You aren't saving country name in EmployeeTbl. This UPDATE makes no sense.

You select CountryID from combobox during data entry to EmployeeTbl.

[mikke3141]

Yes I know that the first box makes no sense, but it was just to show the issue in hand. I know that I can select the CountyID from the combobox to maintain the data to to EmployeeTbl, but the combobox is not an option as I need to copy the generated SQL statement to an excel ADO SQL-string. I use the access query just to generate the code itself. The second picture shows what I am trying to do.

[orange]

Welcome Mikke3141,

A few things before your specific issue.
Name is a reserved word in Access. Here is a link to more info on reserved words.

Choose a naming convention for field and object names that does not allow embedded spaces. You will avoid many syntax issues when using this approach.

When you post, it helps readers if you tell us about your application in overview using simple. plain English.
For example, we are a small importing business. We import products from suppliers in Italy, Germany and Greece.....

There are database principles that, if followed, will simplify usage of the database and streamline some processes. Key to these is normalization which is not dependent upon MS Access, but is a technique for the efficient design of relational databases. Here is a link to Normalization info, but there are several and researching a few will help you with design.

I suggest you build a model of your tables and relationships - can be done with pencil and paper before getting too committed to a physical database that is difficult to modify.

The Database Planning and Design link in my signature has many articles in different formats that should be useful to you.

You may also review this material on Join Types by member isladogs.

Good luck with your project.

Crossposted @ https://access-programmers.co.uk/for...d.php?t=307533

Mikke3141 -- see this re Cross posting
When you submit the same or similar post to multiple forums, always supply the link(s) to the other posts.

[ArviLaanemets]

You must have your form based ONLY on EmployeeTbl;
On form you'll have a combo box with control source linked to EmployeeTbl.CountryID;
Combo box row source must be a query from CountryTbl (like 'SELECT CountryID, Country FROM CountryTbl ORDER BY 2'). Linked column must be 1, column count must be 2, and column widths must be like '0,2.5'.

[mikke3141]

Thank you, but as I stated earlier Combo box is not an option as I update the database through Excel using ADO and and a SQL-string. Access Forms are not part of the solution.

[orange]

Perhaps you could post the SQL-string along with a description of what you want to accomplish.
Is this a 1 record issue, or multiple records, multiple conditions...?

[June7]

What you want is not possible with datasets provided. Country name is not in EmployeesTbl, therefore it is not possible to link to another table on country name so as to retrieve country ID. Again, your request makes no sense.

[alansidman]

crossposted: https://www.ozgrid.com/forum/forum/h...ccess-database

Please read: https://www.excelguru.ca/content.php?184