Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populate subform fields in a different table (Access 2003)

    Major dilemna.
    I have a student table, a student class schedule table, and a class table. I have a One-to-Many between the Student table and the Student Class Schedule Table so that I know what student is taking what class.
    There are countless people using this database and all seem to have trouble typing "correctly". There may be 5 Notary Public Classes listed in the Classes table and I am going to have the data entry person include a date with the class name from now on, for example Notary Public 10/2, Notary Public 10/17, Notary Public 11/2 for example so each Notary class is unique in this Classes Table.
    Now when they open the Student Form with subform of Student Schedule, I would like for them to be able to click the ClassName drop down box and choose for example Notary Public 10/2 from the list...once that is complete I want the rest of the subform to fill in with the rest of the information from the Classes Table so the data entry person will not make mistakes about when this class starts, ends, who the director is, etc... I want all the fields from the Classes table to populate the subform fields I have created for Start Date, End Date, Director Name, etc... and that info to be pulled from the Classes Table from the same line as the Notary Public class entry of Notary Public 10/2.

    Does this make sense?

    Any help would be so appreciated.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate subform fields in a different table (Access 2003)

    Create a query based on the student class schedule table and on the class table, joined on the class ID field.
    Double click the join line and select the option to return all records from the student class schedule table.
    Add all fields from the student class schedule table to the query grid, plus those fields from the class table you want to display in the subform. The class ID field from the student class schedule table should be in the query grid, but not the class ID field from the class table.
    Use this query as record source for the subform.
    Create a combo box on the subform bound to the class ID field.
    The Row Source of the combo box should be the class table (or a query based on the class table).
    When the user selects a class, the rest of the fields from the class table will be filled in automatically.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate subform fields in a different table (Access 2003)

    Thank you for the quick response Hans. I will work on this db over the weekend starting now, and keeping my fingers crossed.
    As Always....thank you so much!
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •