"Not Working out" is vague. I don't know HOW it's failing to work and that's an important clue to leave out.
I'm not sure that I understand your setup and what you are trying to do. I recognize a table occurrence name that matches a suggestion that I made for your database design, but what is the purpose of "Basic Garments Lines"?
There are two basic design issues with your set up if you are trying to select a value in Technical Sheet 2 and want data to appear from Basic Garment:
This cannot happen unless a record exists in Basic Garments Lines to link the current record in Technical Sheet 2 to one or more records in Basic Garment.
You show a one to many relationship from Basic Garments Lines to Basic Garments. (Judging only by the names, I'd expect to see this relationship be many to one). Thus, even if you have such a linking record in the "lines" table, any attempt to "look up" or reference data in Basic Garments may not link to the expected record as it will link to what could be the first of many related records in Basic Garments.
Yes, I'm trying to fix the database design as you suggested in another post. I've tried to fiddle around with it a bit more. So my relationship graph is looking slightly different. See attached...
Currently I have a field called 'Name' on a 'technical sheet 2' layout, pulling it's data from a value list that is the values from the 'name' field from 'basic garments' table. Now i just need to have the style number to change accordingly, type field, which just stay static..?
There is no "attached" in your last post.
And I will repeat my original question: what is the purpose of "Basic Garments Lines"?
I need to know that in order to better advise you.
Sorry have removed the garment lines - something i tried, in order to get it to work but have removed it again. Se attached (the picture that should have been attached above)
The link needs to run the other way. You need to link a primary key in Basic Garment to a foreign key in Technical Sheet 2 as I would assume that you have many records in Technical Sheet 2 that will link to the Same record in Basic Garment.
Then, you have two choices depending on what you need to do with the data:
1) Just put fields from Basic Garment on your Technical Sheet 2 based layout. When you select a matching GarmentID in the current record, the fields from Basic Garment will display that data. (This is an example of what I meant by "linking" data instead of copying it.)
2) Set up fields in technical sheet 2 that auto-enter (copy) data from fields in Basic Garment. This is the option to use if you want data in Basic Garment to serve as a kind of "starting point" of typical default values that then need to be edited into details specific to the record in Technical Sheet 2.
My best guess is that you need option 1 here.
Option one is the one I’m going for.
I’ve change the link around. Which as you explained make sense – going from one basic garment to many variations.
The only thing I’m a bit confused about is how to match the basic garment::ID to the records in ‘Technical sheet 2’..?
I’ve put the ‘Basic Garment::Garment name’ and ‘Basic Garment::Style no’ onto ‘Technical sheet 2’. Made drop down list on the ‘Basic Garment::Garment name’ with a value list pulling the garment names from the Basic Garment table. But when I choose a garment name the nothing happens in style no..?
xBasic GarmentID_fk would be the field for format with a value list. Since it needs to enter the ID number, the "Simple beginner method" is to set up a value list with the "use values from a field" option that shows values from Basic Garment::ID for field 1 and basic garment::Garment Name for field 2. In this value list, you can use garment name to select the correct record, but the value list enters the ID number to correctly link the technical sheet 2 record to it by ID number.
Note: there are other approaches that use scripting and/or more complex relationships to set up value lists for selecting a parent record that work better for larger value lists.
I've place the 'Basic Garment::Garment Name' and 'Basic Garment::style no' onto the 'Technical sheet 2' layout.
'Basic Garment::Garment Name' I have attached a value list. The value list uses values from field. Values from first field is: 'Basic Garment::Id' second value is 'Basic Garment:: Garment name'.
I can select the different garment names, but nothing is happening in the 'Garment::style no' field...
I guess the 2 fields needs to be linked somehow... or is it me who is completely missing the point..?
The only link that you need is the one already shown in your screen shot.
Make sure that you have set up Basic Garment::Basic GarmentID_fk as the field with the value list format and not some other field such as Basic Garment::ID.
Make sure that the expected value is being entered into this field when you select a garment by name from the value list. If this is a drop down list, you should see an ID in the field when you exit this field, not the garment name. If you are using a pop up menu, make a copy of the field and return the copy to edit box format so that you can see the actual data in the field.
Also, make sure that you do not have more than one record in Basic Garment with the same garment name if you are hiding the ID values from view in the value list settings or sorting your values on the garment name field. If Garment Name is not unique, you might be selecting and entering a different ID than you thought you were and thus matching to a different record in Basic Garment.
It didn't have the field assigned to 'Basic Garment::Basic GarmentID_fk' which was messing things up. Thank you so much