I need (newbie) help, please!

[Over]

Hello everyone.



First, I'm sorry the title doesn't give any info about the help I need, but I don't know what to call it and english is not my primary language.

I want to develop a simple inventory/stock management database.

I've started by making a table called "Items", where I list all items I can order. It has the fields "Code", "Name1" and "Name2".

Then I created a table named "Stock", which will contain the same fields as the "Items" table, plus "Quantity" and "Expiry date". It's the location where the items will be stored, so when I add or subtract an item quantity, I'll know how much I have in stock. Plus, I want to know the expiry dates, so if the same items have different expiry dates, they will be saved separately.

My problem is that when I try to add a record in "Stock" table, I can get a drop down menu choosing the item's code. But I wanted to automatically appear the "Name1" and "Name2" fields as well, which are already defined in the "Items" table, although still giving the chance for the user to type a new "Name2" (for different brands of the same item).

And that's where I'm stuck. Can you help me, please?

Best regards,
Angelo

[Over]

I've tried to start over.

I've created the Products table, where each line has "id", "code", "Name1" and "Name2".

I've created the Movements table, where each line has "id", "code", "Name1", "Name2", "Quantity", "Order Date", "Received Date", "Expiry Date" and "Category Date".

My problem is that I want to link the Code, Name1 fields, so that there's can't be chosen a code with the wrong name. Example:

Table "Products"
Code | Name1 | Name2
123 | Paracetamol | BUR
123 | Paracetamol | Paracetamol
213 | Ibuprofeno | Ibuprofeno
213 | Ibuprofeno | Brufen

I don't want to allow someone to mistakenly input "123 | Ibuprofeno", for example. If I input the code "123", I want that the field "Name1" automatically fills to "Paracetamol" and that the field "Name2" only allows to choose between "BUR" and "Paracetamol".

How do I achieve this?

Please, help!

[RuralGuy]

Are you aware that you *only* need the "id" field in the Movements table and not the "Code", "Name1" and "Name2" fields?