Results 1 to 6 of 6
  1. #1
    msto is offline Novice
    Windows Vista Access 2003
    Join Date
    Apr 2019
    Posts
    4

    Related Tables, Form, Changes not successful

    I have 2 related tables, Company and employee. They have a one (company) to many (employee) relationship with a defined Company ID. I created a form I would like to use that has information from both tables. I have a combo field for company ID that when the ID is typed in auto populates the company information (on Change), with the intent of being able to add new employees easily. It works... except, I can't save anything. It errors anytime I try to add a new employee to the form. I assume it's trying to add a new record in the company table as well, which wont be allowed as the PK is that company number and can't be duplicated. But how do I fix this? I don't need to be able to add anything to the parent (company) table using this form but I can't figure out how to fix this.
    I have a different database with a one to one relationship and this problem doesn't happen.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The company ID primary key is an autonumber field? The foreign key in employee is Long Integer?

    What is the form RecordSource?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    msto is offline Novice
    Windows Vista Access 2003
    Join Date
    Apr 2019
    Posts
    4
    Primary Key is not autonumber because I read that could cause issues, it's just long integer, same for foreign key, long integer. The recordSource is a query of the 2 related tables and all their fields with inside join? (number 1) and 1-many relationship.
    I will try and get a DB copy asap.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Really don't need both tables in RecordSource. But if you do, don't use INNER JOIN. Don't allow edit in textboxes bound to company fields - set them as Locked Yes and TabStop No.
    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.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    You need to change your Form Design.

    You should have a Main Form based on the Company table and a Subform based on the Employee table.

    To select a Company you should not be typing in the Autonumber which identifies the Company.

    If you create the Combobox in the Header of the Form it should just display a List of the Company Names and the Autonumber Key should be hidden

  6. #6
    msto is offline Novice
    Windows Vista Access 2003
    Join Date
    Apr 2019
    Posts
    4
    That is exactly what I needed to do! Thank you so much, it works perfect now!

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

Similar Threads

  1. Replies: 1
    Last Post: 06-06-2016, 06:05 PM
  2. Replies: 5
    Last Post: 12-14-2012, 04:21 PM
  3. Replies: 10
    Last Post: 07-18-2012, 03:42 PM
  4. One Form to Update 2 Related Tables?
    By zannix in forum Forms
    Replies: 3
    Last Post: 01-26-2012, 08:46 AM
  5. Not able to make Form from related Tables
    By a1y1a1y1 in forum Forms
    Replies: 5
    Last Post: 01-02-2010, 12:33 PM

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