insert rows into subform problem, help!

[rsbutterfly16]

hi i have a main form called "frmMain" that i use for users to add new studentsNumbers txtStudentsNumbers.bound to tblStudents also this form has a combo box called cmbDepartments coming from tblDepartments.

Then a subform which right now it has a combobox called cmbSubDepartments (coming from tblSubDepartments) , this combobox has a rowsource to update everytime someone selects a dept in the main form so it shows the subdepartments belonging to the departments.
Each department has a a set of Subdepartments, also has two texboxes called txtTuitionA, and txtTuitionB.
so in total i have three tables each with the pk id.



what i need is that when a user enters a student number in the main form, select the department where they want to add the student to, then the subform gets automotically populated with all the subdepartments so then the user is forced to enter TuitionA and tuition B to each subdept for that particular students

i tried this...

(in the main form)

Private Sub cmbDepartment_AfterUpdate()
Dim intQty As Integer
Dim i As Integer


If MsgBox("Are you sure you want to add a new tuition plans for this student?", vbYesNo) = vbYes Then
Me.txtStudentID = DMax("StudentID", "tblStudents") + 1 'get New student number 'Add the new row into Main Table tblStudents

intQty= me.cmbSubDepartments.itemdata -1

For i = 1 To intQty - 1
strSQL = "INSERT INTO tblStudent (StudentID, SubDepartmentID) SELECT StudentID, SubDepartmentID FROM tblSubDepartments WHERE SubDepartmentID = " & Forms!frmStudentsTuitions_subform!cmbSubDepartments & ";"
CurrentDb.Execute strSQL


Next i

Me.frmStudentsTuitions_subform.Requery
Me.frmStudentsTuitions_subform.SetFocus



Else
DoCmd.CancelEvent 'if user choose no in question then undo
DoCmd.Close acForm, Me.Name 'close current form

End If


End Sub


but my subform does not get updated... :-( .

 

[rsbutterfly16]

Thank you Pat! so i would put this in my main form?

Private Sub cmbDepartment_AfterUpdate()
Dim intQty As Integer
Dim i As Integer


If MsgBox("Are you sure you want to add a new tuition plans for this student?", vbYesNo) = vbYes Then
Me.txtStudentID = DMax("StudentID", "tblStudents") + 1 'get New student number 'Add the new row into Main Table tblStudents
Here.....

 

[rsbutterfly16]

studentID is coming from the DMax("StudentID", "tblStudents") + 1 ..............

yes the subform has an autonumberID called SubDepartmentID , this form comes from the tblSubDeparments. the foreignkey is FKStudentID.

There is a one to many relationship between tbl Students and tblSubdeparments from StudentID and FKStudentID


this is what i tried but it says too few parameters....

Dim lngID As Long

If MsgBox("Are you sure you want to add a new tuition for this student?", vbYesNo) = vbYes Then
Me.txtStudentID = DMax("StudentID", "tblStudents") + 1 'creates newStudentId

lngID = Me.StudentID



strSQL = "INSERT INTO tblSubDepartments (FkStudentID, SubDepartmentNumber)
"SELECT " & lngID & ", SubDepartmentNumber , SubDepartmentNumber from tblSubDepartmentInformation ;"


Me.sfrm.Requery

DBEngine(0)(0).Execute strSQL, dbFailOnError


any help would be greatly apreciated!!!