Results 1 to 6 of 6
  1. #1
    bkelly is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    17

    Getting a foreign key set

    Access 2003 under Windows XP

    Question summary: How does a form fill in the value for a foreign key when creating a new record?

    Details: I am learning Access and am creating a golf score and reservation database. Right now I am working on queries and forms to load and edit golf club and course information into the database.

    Query qry2_Golf_Club gets the names of golf clubs from the table tbl2_Golf_Clubs and sorts by club name. Query qry2_Courses gets information about golf courses from table tbl2_Golf_Courses (one club may contain multiple courses) and sorts it by course name. Form fm2_Course_Edit shows the name of the golf clubs. While fm2_Course_Edit was open in design mode I dragged form fm2_Course_Data onto fm2_Course_Edit creating a form and subform.

    Displayed on this composite form is the name of the golf clubs (one at a time) and the name of the courses found at that club. Displaying records of the overall form shows the different golf clubs. Display records withing the sub form shows the courses at each club. That works.

    When I go to the sub form for courses and try to add a new course, I must edit in the value of the foreign key before Access will accept any of the Course description. How do I change the form so that when I start filling in values the form (and/or Access itself) will get the primary key from the tbl_Golf_Club and enter that value into the foreign key for the tbl_Golf_Courses.

    Gee, the basic question seems simple, but setting the stage so I can ask it is rather complex.

    Thanks for your time

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The MainForm/SubForm combination will fill in the values that are listed in the LinkChild/MasterFields properties of the SubFormControl.

  3. #3
    bkelly is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    17
    Hello RuralGuy
    The problem is that is doesn't. There is something I must have done wrong.

    This is getting frustrating so I put the database on a web page along with a screen image showing what I get. Both are in the native format, and they are in a zip file on the same page. They are located at http://www.bkelly.ws/access_test/index.htm
    I hate to ask anyone to go out of their way like this, but I don't know where to turn to resolve this problem.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    In order for the ForeignKey field to be updated in the SubForm it must be part of the RecordSource of the SubForm.

  5. #5
    bkelly is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    17
    Quote Originally Posted by RuralGuy View Post
    In order for the ForeignKey field to be updated in the SubForm it must be part of the RecordSource of the SubForm.
    But it is. That doesn't help, so I looked again and found it there. However, the foreign key in the courses table was not spelled exactly the same as the primary key in the golf club table. Hmm. Ok, I fixed that and now it works.

    Its not much consolation to say I knew I had made an error.
    Thank you for taking the time to look and to post a reply.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I always name my PrimaryKeys and their ForeignKeys the same so I don't get too confused. Glad you got it working and thanks for using the Solved thread tool.

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

Similar Threads

  1. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM
  2. Foreign Data
    By bmiller in forum Queries
    Replies: 0
    Last Post: 03-21-2006, 01:02 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