Results 1 to 5 of 5
  1. #1
    DecaturLady is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26

    Question Appended records Not writing to Form

    My db contains names, contact info, etc., for my high school class. It has six tables. [Names, Phone numbers, Email addresses, Physical address, Mailing address, and finally photos. [Names is my primary table, Relationship to other five tables are all directly from the Names Primary Key ID to the Primary Key ID of the other five tables.]



    There are 500+ records. The original "official" list was painfully converted to the db and data checking began. I created a query to combine the data from all the tables (except photos). Then I created a form ("Kitchen Sink") based upon that query. So far so good and everything was going GREAT!

    Trouble began with error checking and we found about 16 classmates were missing from the original list. I used an Append Query to update the names table. NOW, the BIG PROBLEM!!! When I go to my Kitchen Sink form, the new records are not there! I'm stumped!!!

    Do I need to delete the form Kitchen Sink's source query and create a new one?
    Do I also need to create a new Kitchen Sink II form based upon my new query?

    I need HELP in a BIG way. Fiftieth Reunion is ~ten months away. Data is very stale and I need to get this fixed so I can contact classmates to obtain corrected information.

    Can anyone help me? Please!!!

    pam

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    we cant guess at this.
    Did you check your append query to see if all records transfered?

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The problem is your query. I assume that in the query you create joins to link all the other tables to your main table [Names]. However, if you just use normal joins when linking the tables, if any one or more of the 5 linked tables does not contain a record corresponding to the PK in the [Names] table, then there will be no record for that Name in the query results, and hence no information in the form.

    What you need to do is convert the 5 joins in the query to outer joins (sometimes called left joins), so that a missing record in one of the 5 linked tables does not prevent the entire record from being displayed in the query results.

    In the query design -
    - Right-click the join line between the [names] table any one of the linked tables
    - click "Join Properties"
    - You will see a dialogue showing you the linked tables and the columns used to join them.
    - selection Option 2 or 3 below, whichever one starts with "Include ALL records from [Names]...."
    - Click OK to close the dialog, save and run the query.

    The data from the new records you added to [Names] should now be shown, but with no data from the other tables (because there isn't any yet).

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Post a jpg of your tables and relationships, or just post a copy of your database.

  5. #5
    DecaturLady is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26
    Hey John_G --- THANK YOU, THANK YOU, THANK YOU!!!

    After a few wrong turns, I finally "got it" and my Append Query worked like a charm.
    You're the best!

    DecaturLady

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

Similar Threads

  1. Auto-populate fields in appended records
    By FinChase in forum Access
    Replies: 2
    Last Post: 06-17-2014, 10:23 AM
  2. Opening form to Appended Record
    By PPat in forum Forms
    Replies: 8
    Last Post: 04-12-2013, 03:49 PM
  3. CRLF automatically appended in string concat?
    By tnt in forum Programming
    Replies: 2
    Last Post: 10-04-2011, 09:36 PM
  4. writing control names on a form to a table
    By focosi in forum Access
    Replies: 1
    Last Post: 09-21-2011, 08:47 AM
  5. Writing between criteria in a form
    By Suzied in forum Forms
    Replies: 7
    Last Post: 03-26-2010, 12:41 PM

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