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.
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?
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).