Save subform record to a table

[robin a]

Hi,
I have a main form which contains an employee ID text box the user enters. Then from a combo box CboDept the user chooses dept which populates another combo box where the user chooses job title. CboJob_Title There is a subform (Class_Catalog subform)(set as continuouse) that is linked to the main form (Emp_New) by job title. From the job title the subform populates Class_ID. There may be anywhere from 0 to 15 lines populated based on selection in main form.

My dilemma is getting the subform info to save to a table named Classes_taken. Each record is comprised of Emp_ID and Class_ID. At the same time I need the main form to save its record to Emp table.

I have an INSERT INTO SQL statement but its not working:

Private Sub CmdSave_Click()

     Dim mySQL As String
        
     mySQL = "INSERT INTO Classes_taken([Emp_ID],[Class_ID])VALUES(" & Forms![Class_Catalog subform]![TxtEmp] & "," & Forms![Class_Catalog subform]![TxtCID] & ");"
     
    Debug.Print mySQL
    DoCmd.RunSQL mySQL
     

End Sub

[Rabbit]

There is no need to use an INSERT statement if your form is bound to the tables.

[Jerry Maiapu]

If you set up your table relationship properly you do not need to insert into table again.

My advise: Since you want to see data on the subform that depends on the values filtered from the a main form, you should have two tables. Currently I guess you have one table which seems to be the source data for the subform.

Create another table (tbldepart), remove Department and the job title columns from the existing table and have it in tbldepart table with a new ID field as AUTONUMBER (SO 3 COLUMNS IN THIS TABLE.)

Now craete a foreign key in your existing table and have a many to one realtionship with tbldepart being the one side and your existing table being the many side of the realtionship.

Use form wizard to create a form by selecting the two tables-a main from and a subform will be created automatically.

Create 2 combos on the main form(one for department and another for job title) using wizard. Follow the wizard and select the option to filter the subform based on the values selected in the main form.

Cheers!

JM

[robin a]

Thank you for your responses. This is how mine is set up. My main form is named Emp_New and is based on the Emp table and saves to that table when i click save. I have two subforms, but I'll just worry about one for now. It is called Class_Catalog_s ubform. The link is parent: CboJOb_Title and Chid: Job_title. Based on the job title selection in the main form, the subform populates Class_Name and Class_ID which it gets from the Class_Catalog table. The Class_ID record sources is Class_ID from the Class Catalog table. The problem is, I want to save Class_ID to a table named Classes_taken. The subform also has a text box with =[Forms]![Emp_New]![txtEmp_ID] as the record source that simply lists the employee ID typed into txtEmpID on the main form for each class that is listed. I need this to save with each Class_ID listed to Classes_taken table. I'm pretty sure I need code to save the subform records where I need to.

I've attached my database, I've run out of ideas. all help is appreciated.

[ariful alam]

In your database, U used a Main form that has two sub forms. 1st sub form has a link field but 2nd sub form has no field linked to the main form. you used save button for all the forms to save. but whenever you use the sub form in a main form, i think you do not need to use save button. whenever you save the main form data the linked sub form data will also save the specific table of the sub forms.

how many sub forms in a main form you have it doesn't matter. the matter is all the sub forms should linked to the main form using a specific field that is common in main form and sub form.

[twinnyfo]

robin a,

Based on looking at your database, I'm not exactly sure what you are trying to do with your Class_Catolog subform. Are you trying to be able to select a particular class from the subform and then declare that it has been taken by the employee on a particular date? If so, there are no controls on your subform that would indicate that is what you are trying to do.

If this is the case, you should have a command button in the detail section of the form next to the course, so that when you click that button, the appropriate data is transferred to the Classes_taken table. This is very easily done, but I would use VBA instead of a macro, as you currently have a macro that simply goes to a new record.

Right now, form the database you provided, it's just not clear exactly what you are trying to do, so based on your question, this is the answer I provide.

Your Append Query also was not working properly, so I recommend the folowing to fix it. You could run this query from VBA or macro, but I recommend VBA to get accustomed to the better functionality of the code.

INSERT INTO Classes_taken ( Emp_ID, Class_ID, Date_Taken )
SELECT Emp.Emp_ID, Class_Catalog.Class_ID, Date() AS Date_Taken
FROM Emp, Class_Catalog
WHERE (((Emp.Emp_ID)=[Forms]![Emp_New]![txtEmp_ID]) AND ((Class_Catalog.Class_ID)=[Forms]![Emp_New]![Class_Catalog subform].[Form]![TxtCID]));

Hope this helps out a bit for you....

[robin a]

Thank you everybody. I am very close! Twinnyfo, i used your code and left off the Date_Taken field because I don't need that in this instance. The code I'm left with for the append query is:

INSERT INTO Classes_taken ( Emp_ID, Class_ID )
SELECT Emp.Emp_ID, Class_Catalog.Class_ID
FROM Emp, Class_Catalog
WHERE (((Emp.Emp_ID)=[Forms]![Emp_New]![txtEmp_ID]) AND ((Class_Catalog.Class_ID)=[Forms]![Emp_New]![Class_Catalog subform].[Form]![TxtCID]));

When I run this on the click event of the save button on the main form it says that 0 records were appended. My question about the query is the SELECT statement, should it say the Emp_ID is coming from the Emp table when the Emp_ID is a new one that is being input on the Emp_new main form? I don't know if it makes a difference that it doesn't already exist in the Emp table. Other than that, I think the code should work. What do you think? I really appreciate this.

[twinnyfo]

Robin,

Perhaps this might work, as I've taken out the WHERE clause completely. Instead, this INSERT statement merely checks to see what the forms have as current data, then inserts into the table. Again, hope this helps......

INSERT INTO Classes_taken ( Emp_ID, Class_ID )
SELECT [Forms]![Emp_New]![txtEmp_ID] AS Emp_ID, [Forms]![Emp_New]![Class_Catalog subform].[Form]![TxtCID] AS Class_ID;

Cheers!

[robin a]

YOU ARE A GENIUS! THAT DID IT! Thanks to you I started a SQL training course. I am so happy I am beside myself! I now have to figure out how to save the All_classes subform emp_ID and class_ID to the Classes_taken form but now I think maybe a second append query may do it. I'll work on it. I seriously can't thank you enough!

Robin