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