Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    19

    Unhappy Unanswered: creating form to enter data in tbl

    hey there,

    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.

    Please help me

  2. #2
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    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.

    HTH,

    Chris

  3. #3
    Join Date
    Oct 2004
    Posts
    19
    i took out the first/surname, and video title fields from the loans table.

    i made the form taking Loans/Member/Video ID from the Loans table, First/Surname from the Members tables, and Video title from the Videos table.

    it half worked, so thank you for that.

    when i put in the video id the title doesn't come up (unlike when i put in the member id, the first/surnames came up)

    also, although the First/surnames are coming up in the form, they are not going in the Loans tables because I don't have fields for them - or am i asking something to be done that simply can't?

    thank u

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    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).

    HTH,

    Chris

  5. #5
    Join Date
    Oct 2004
    Posts
    19
    erm, i can't seem to get it to work.

    I right-clicked on the video title text box in the loans form and the list box of control source contains the fields form the Loans table.

    so it is bound to the loans table but the video title doesn't come up.

    any ideas?

    thank you

  6. #6
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    I made a quick db that does what you're asking. Let me know if you have questions.

    Chris
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2004
    Posts
    19
    thank you very much for actually creating that database, much appreciated.

    the differences between your db and mine were:

    i used 'number' rather than 'autonumber' for all of my primary keys (member id in 100s, video id in 200s and loans in 300s)

    when building the relationship, i only 'checked' the first box (enforce referential integrity)

    but then i made a new db with my primary keys still as 'numbers', and then built my relationship still only 'checking' the first box (bascially, as i did it in the first place), and it bloody worked.

    So i don't know why my first db wasn't working.

    it's a funny ol' game.

  8. #8
    Join Date
    Oct 2004
    Posts
    19
    i now know why the video title wouldn't come up in the form.

    when i built the relationship, rather than a 1-2-many relationship between the loans and video table, it created a 1-2-1 relationship.

    and i can't explain how that happened.

    so i've binned that db and made a new one.

    thanks again

  9. #9
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Quote Originally Posted by sykes
    it's a funny ol' game.
    It sure is. Well.. a game anyway.

    Good luck and post again if you run into problems.

    Chris

    BTW, the one-one relationship would happen if you tried to join 2 primary key fields (or any 2 'No Duplicates' fields)
    Last edited by cpgospi; 10-21-04 at 15:02. Reason: Added note

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •