Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009

    Unanswered: SQL inserting multiple records


    This is my first database and the first time I've ever dealt with Access or VBA. I've bought several books and have finished one Access book and half way through my VBA book however Reading and doing is two different things.

    Some background info on my DB:
    I'm building a training database for my company that has three main tables An employee Table that has around 700 records (which has Agent ID (s) (primary Key), names, their dept etc), a Training table (which has Class Name, Course Number(Primary Key), trainers names, locations, times, etc), and AgentRecords that ties the two tables together.

    I have a form called AddRecords which has two combo box's, a text box, and a subform(AddRecords_Subfrm). The first combo box is a Teamlead selector and the second is a Course Number selector and a text box that populates the Class Name depending on what Course Number is selected. In the subform based on what TeamLead and Course Number is selected it lists the [Agent ID], The Teamlead, Agent_Name, and Course Number.

    There are typically 15 give or take agents to a team lead and I want to save all their info I have populated in the subform along with the main form into the AgentRecords Table. So far the save button only saves the first record in the AddRecords_Subfrm along with the Class Name from the text box in the main AddRecords form. I should note that I do have my Master and Child fields linked correctly.

    here is the code which works but only adds the first recordset of info instead of all the recordsets for the Teamleader:

    DoCmd.RunSQL "INSERT INTO AgentRecords ( [Course Number], [Agent ID], [Class Name]  ) SELECT Forms!AddRecords!CENumfrm AS [Course Number], Forms!AddRecords!AddRecords_Subfrm!AGENT_IDfrm AS [Agent ID], Forms!AddRecords!ClassNamefrm AS [Class Name];"
    Well I may have given too much or too little info so please bare with me and I'll provide any additional details that may be needed and thanks!

    I should note that my employee Table is linked from an odbc and I cannot modify that list just reference it which is why I've chosen go this route.
    Last edited by jmantn; 03-16-09 at 11:58.

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    Two points:

    AgentRecords should contain [Course Number] or [Course Name], not both. This is a Second Normal Form violation.
    Secondly, you shouldn't need to manually save for a subform. Is the subform bound to the AgentRecords table?
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Thanks for the reply! I've removed the Class Name from my code.

    The subform if I'm understanding correctly, I have bound parts of it to my Employee Table (to show the TeamLead as well as Agent_ID and Agent_Name) and to my Training Table (to show the Course Number).

  4. #4
    Join Date
    Mar 2009
    I have found a solution and thanks for the help

Posting Permissions

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