Results 1 to 6 of 6
  1. #1
    sidewayzalex is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    51

    "Cannot add record(s); Join key of table 'tblPartsPerOrder' not in recordset.


    Alright. So after starting a new database from scratch, I have been able to successfully auto-fill a row in a form.

    The dilema, though, is that I cannot add another row in the form without manually going to tblPartsPerOrder and starting a new row by inputting the JCSnumber. How do I make it so that this is automatic?

    The error message is: "Cannot add record(s); Join key of table 'tblPartsPerOrder' not in recordset.

    I have attached a copy of the database I am testing this on.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Unfortunately, Windows compression is not recognizing that zip file and won't extract.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You are going to encounter a lot of problems with this database as time goes on. But the fix right now is to change the RECORD SOURCE of your subform to this SQL statement:

    Code:
    SELECT tblPartsPerOrder.JCSnumber, tblPartsPerOrder.Quantity, tblPartsPerOrder.VenderID, tblPartsPerOrder.PartID, tblPartsPerOrder.Description, tblPartsPerOrder.PartCost, [Quantity]*[PartCost] AS ExtendedCost, [Quantity]*[PartCost]/0.6 AS ExtendedSell
    FROM tblPartsPerOrder;

  4. #4
    sidewayzalex is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    51
    rpeare, how will I encounter lots of problems with this database?

    Is there a particular thing I should do or change before going further?
    also, thanks for the code, I will try it when I get a chance on Monday.

  5. #5
    sidewayzalex is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    51
    jPeare,
    I have just created a subform, titled sfrmPartsPerOrder. I attempted using the SQL code you gave me to no avail. Now the system doesnt auto-fill, but I CAN create multiple rows.

    Attached is the updated database.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I was able to download and unzip the second attachment.

    When I view tblOrders two records appear to have same JSCnumber - 0 - but when I view the records in form with the textbox set to show multiline, I see two 0's in the second record. There must be a CR between the 0s. I positioned cursor between them and hit delete now the two zeros show on one line as 00.

    Change the subform container SourceObject to sfrmPartsPerOrder or change the RecordSource of [tblOrders Query subform]
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Join Key of table in recordset
    By Rohit0012 in forum Forms
    Replies: 14
    Last Post: 09-24-2011, 09:04 PM
  2. Replies: 8
    Last Post: 05-12-2011, 06:11 PM
  3. join key not in recordset - what does it mean?
    By geophilus in forum Queries
    Replies: 1
    Last Post: 08-22-2010, 09:32 AM
  4. Join A Recordset to the current db Table
    By mjellis in forum Programming
    Replies: 0
    Last Post: 08-10-2010, 02:44 PM
  5. Creating "Edit Record" link in table column
    By joshearl in forum Forms
    Replies: 1
    Last Post: 12-25-2009, 11:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums