none
'Cannot add record(s); join key of table 'Current Rentals' not in recordset. RRS feed

  • Question

  • Hi everyone, I'm really new to Access (been following some tutorials today) but I've ran into a problem when trying to add a new record via my form as I get the error message in the title. Can anyone explain what I have to do because I've searched the problem up and seen the replies but don't really understand what I need to do to fix mine.

    In my form I've got the following fields Customer ID, first name, last name, street, city, postcode, contact number, rental id, book id, title, genre, rental price, checkout date, return date, card number and card expiry.

    All of the fields work except for rental id, book id, checkout date and return date. All these belong to my Current rentals table.

    Appreciate any help as really struggling thanks

    Monday, August 10, 2015 8:46 PM

Answers

  • So your tables should look like the following:

    Customer

    CustomerID

    NameOf

    Phone

    Address

    AsOf

    1

    John Doe

    1234567890

    123 Anywhere St

    8/10/2015

    2

    Jane Smith

    1234561234

    124 Anywhere St

    8/10/2015

    Book

    BookID

    Title

    Author

    Published

    1

    Moby Dick

    Herman Melville

    1/1/1851

    2

    Adventures of Tom Sawyer

    Mark Twain

    1/1/1876

    Rental

    RentalID

    CustomerID

    BookID

    DateRented

    DateReturned

    1

    1

    1

    8/10/2015

    2

    1

    2

    8/10/2015

    Per the Rental table, John Doe has rented two books, "Moby Dick," and "Adventures of Tom Sawyer."

    Again, the Rental record can't exists without a customer (John Doe - ID 1)  and a book (Moby Dick - ID 1).

    Does this help you understand what your problem is?


    • Edited by RunningManHD Monday, August 10, 2015 9:41 PM
    • Marked as answer by JamesM94 Monday, August 10, 2015 10:13 PM
    Monday, August 10, 2015 9:30 PM

All replies

  • Your Current Rentals table is joined to at least one other table where a related value should exist. 
    This is called referential integrity.  So the other table would be the parent table with a primary key and Current Rentals is related using a foreign key field.

    From what I can tell, you Current Rentals table requires a CustomerID to exists in what must be the Customers table.  It must also have a BookID that exists in what must be the Books table.

    Monday, August 10, 2015 8:51 PM
  • Your Current Rentals table is joined to at least one other table where a related value should exist. 

    yeah its connected to my customers table and books table :/

    not really sure what related value to add

    Monday, August 10, 2015 8:56 PM
  • So first you must have at least one or more customers in your Customers table.

    Next, you must have some books available in your Books table to rent.

    Now you can create a record in the Rentals table using only CustomerIDs that exists in the Customers table and BookIDs that exist in the Books table.  Both are required information in your Rentals table.

    You can't have a rental transaction without a customer, and you can't rent something if it doesn't exist.  Make sense?







    Monday, August 10, 2015 9:00 PM
  • So first you must have at least one or more customers in your Customers table.

    Next, you must have some books available in your Books table to rent.

    So, then you want to create a rental record in the Rentals table, so now you can only create a record using only CustomerIDs that exists in the Customers table and BookIDs that exist in the Books table.


    yeah I've currently got 6 customers in my customers table and 5 books in books table. 

    then in my 'Current Rentals' table I've got RentailID, BookID, CustomerID and CheckoutDate and ReturnDate

    Monday, August 10, 2015 9:04 PM
  • So your tables should look like the following:

    Customer

    CustomerID

    NameOf

    Phone

    Address

    AsOf

    1

    John Doe

    1234567890

    123 Anywhere St

    8/10/2015

    2

    Jane Smith

    1234561234

    124 Anywhere St

    8/10/2015

    Book

    BookID

    Title

    Author

    Published

    1

    Moby Dick

    Herman Melville

    1/1/1851

    2

    Adventures of Tom Sawyer

    Mark Twain

    1/1/1876

    Rental

    RentalID

    CustomerID

    BookID

    DateRented

    DateReturned

    1

    1

    1

    8/10/2015

    2

    1

    2

    8/10/2015

    Per the Rental table, John Doe has rented two books, "Moby Dick," and "Adventures of Tom Sawyer."

    Again, the Rental record can't exists without a customer (John Doe - ID 1)  and a book (Moby Dick - ID 1).

    Does this help you understand what your problem is?


    • Edited by RunningManHD Monday, August 10, 2015 9:41 PM
    • Marked as answer by JamesM94 Monday, August 10, 2015 10:13 PM
    Monday, August 10, 2015 9:30 PM
  • If you are creating an input form, you would have a main form and a sub form.  The main form would be your customer record, and the sub form would be your rental record(s).

    The key sub form properties should be as follows:

    • Link Master Fields: CustomerID
    • Link Child Fields: CustomerID


    Monday, August 10, 2015 9:55 PM
  • Monday, August 10, 2015 10:11 PM
  • So your tables should look like the following:


    Customer

    CustomerID

    NameOf

    Phone

    Address

    AsOf

    1

    John Doe

    1234567890

    123 Anywhere St

    8/10/2015

    2

    Jane Smith

    1234561234

    124 Anywhere St

    8/10/2015

    Book

    BookID

    Title

    Author

    Published

    1

    Moby Dick

    Herman Melville

    1/1/1851

    2

    Adventures of Tom Sawyer

    Mark Twain

    1/1/1876

    Rental

    RentalID

    CustomerID

    BookID

    DateRented

    DateReturned

    1

    1

    1

    8/10/2015

    2

    1

    2

    8/10/2015

    Per the Rental table, John Doe has rented two books, "Moby Dick," and "Adventures of Tom Sawyer."

    Again, the Rental record can't exists without a customer (John Doe - ID 1)  and a book (Moby Dick - ID 1).

    Does this help you understand what your problem is?


    yes thank you very much, didn't realise how stupid my tables were. Appreciate all the help :)
    Monday, August 10, 2015 10:13 PM
  • No problem, glad to help. 

    Now to throw another log on the fire here, the field Author in the Book table is currently a text field.  This was for example only.  However, proper design calls for a table of Authors, and the Book table would store the Author id rather than the Author's name.  Make sense?


    Monday, August 10, 2015 10:23 PM
  • No problem, glad to help. 

    Now to throw another log on the fire here, the field Author in the Book table is currently a text field.  This was for example only.  However, proper design calls for a table of Authors, and the Book table would store the Author id rather than the Author's name.  Make sense?


    yeah got it thanks :)

    just one more quick question, in your database when creating a new record how did you get the little arrow drop down to pick out of books etc.?

    Monday, August 10, 2015 10:55 PM
  • To see what is known as a combo box (text box with a drop down list) in your table, you can define the field property "Display Control," under the Lookup tab.

    To see the same in a form, you would use a combo box control.  To get more information about combo boxes, try referencing Access help.  If you need further assistance, feel free to post back.

    Monday, August 10, 2015 11:15 PM
  • Is that the ONLY way to set up the data entry form? Could you have a combo box with a label for the BookID that writes the chosen title (or BookID) to the RentalID table?
    Tuesday, August 14, 2018 7:56 PM