Relationships -- am I this dumb?
I had a previous posting where some folks tried to help me sort out some relationship issues, which I thought I had done, but then somehow lost it.
Maybe if I explain in detail somebody can point out what I'm doing wrong. I have used Access quite a bit, but primarily for a huge flat-field database. I'm trialing FM 10 to use for this relational database I'm trying to develop as a number of people recommended it as being much more user friendly. It also has user access controls built in, which I need and is now gone from Access 2007.
I have three tables that have data that will not change much, and that data would ONLY be changed by the administrator. I've called these static tables. Users will be able to view/print these tables to look up information, but not change them.
There are six tables that are designed for user input to create and update records for one type of unique activity each, which I've called user tables. Each of these tables are independent of the others; I can see no cross-reference needs between them. In those six user tables I want to have fields that use drop-down lists to select one, and only one, option from fields within the three static tables. This is so that subsequent searches and reports can be done consistently right now. In the future, I have an idea of pulling information from both static and user tables into reports, but haven't gotten that far yet. Right now I can't even create a record!
In those six user tables I have primary keys as well as foreign keys for the three tables with static data. I have linked the primary keys from the three static tables to their corresponding foreign key value in the six user tables. From all the information I've found, it seems that's the appropriate way as the static tables have a one to many relationship with the user tables.
When I go to the field in one of the six user tables needing a look-up (this word seems to confuse things) from one of the static tables, I can see the option in Field/Control Setup to display the related tables. I select the field that I want and in the layout it shows :: and the name of the appropriate field. In Layout view, that field is displayed in the box with the :: and the name of the appropriate field. However, when I try and enter a new record in the user table in Browse mode, tabbing through the entries just skips that field entirely.
Then I went back to Field/Control Setup in the box "Display values from," then to "Manage Value Lists," created a new value list with an appropriate name, selected the field from the same static table, and all seems fine as I OK out of it. BUT, when I go back to enter a new record, the same thing happens. In Browse mode it acts as if the field isn't there at all -- it just bypasses it. I cannot click into it or tab into it.
I went into Edit Relationships, which shows the foreign key in the user table linked to the primary key in the static table, and added a link from the specific field in the user table to the specific field in the static table. As they used to say when I lived in London, no joy.
I've also tried reversing the primary-foreign key relationships -- no change.
I've re-read the stuff from The Missing Manual and the tuturials on Lynda.com, but they always seem to be linking multiple tables together. I think I'm doing what they say, but nothing seems to work.
Somehow I think it has to do with the relationship link, but I've tried almost all possible permutations and nothing changes.
Anybody have an idea of where I'm going wrong?
Thanks for listening/reading all this.