I've got a VideoShop DB with 3 tbls: Members, Videos and Loans.
Members: Member ID, First Name, Surname, DOB, Address
(I've created 15 entries)
Videos: Video ID, Video Title, Category, Certificate
(I've crated 15 entries)
Loans: Loans ID, Member ID, First Name, Surname, Video ID, Video Title, Date Rented, Date Due
(I haven't added any entries yet)
Primary keys: Member ID, Video ID and Loans ID respectively.
Member ID and Video ID are Foreign keys in the Loans table.
I've created the relationships - 1-2-many from Loans to the other two.
I want to fill the Loans table via a Loans form.
I made one using the wizard taking the Member-related and Video-related from the Members and Videos tables respectively.
It doesn't work - it won't let me fill in the form.
You have [Surname] and [First Name] fields in both Members and Loans table. Those fields should not be in the Loans table. You also have [Video Title] in both tables, should only be in Video table. That's probably where your problems lies.
Yep, you're on the right track. Make sure the textbox in the form is bound to the Loans table and not the Videos table. (Look at control source for that box)
And yep, you won't be storing names in the Loans table. You only need to store the key field, then anytime you need to display the full name, you use a query. Otherwise you'd be storing redundant data (this is badness in any db design).