Add record programmatically with values from two recordsources

[PJ Von]

My form uses three underlying tables: Classes, Students and a linking table
called StudentClassRegistrations, which stores the primary key for the
student and the class. I want to add a record to StudentClassRegistrations
based on the values selected in the form’s two combo boxes (The form’s
recordsource is set to Classes).

The first combo box allows the user to select a class (after which a subform
displays all students enrolled in that class.)

The second combo box allows the user to select the student to enroll.

I want to build an “Enroll Selected Student†command button that adds a
record to StudentClassesRegistrations, but have no idea how to accomplish
this via code. Any help will be enormously appreciated.

 

[Allen Browne]

Execute an append query to add the record to the 3rd table.

This example assumes your form has:
- a combo named cboStudentID with the StudentID number,
- a combo named cboClassID with the ClassID number,
- a command button named cmdEnrolStudent.

This is the kind of code you want to use in the Event Procedure of the
button's Click event:

Private Sub cmdEnrolStudent_Click()
Dim strSql As String
If IsNull(Me.cboStudentID) OR IsNull(Me.cboClassID) Then
MsgBox "Select a student and a class."
Else
strSql = "INSERT INTO StudentClassRegistrations " & _
"( StudentID, ClassID ) SELECT " & Me.cboStudentID & _
" AS StudentID, " & Me.cboClassID & " AS ClassID;
CurrentDb.Execute strSql, dbFailOnError
End If
End Sub

Notes:
1. You may need to requery the subform to show the record there.

2. If this kind of Execute is new, here's some background info:
http://allenbrowne.com/ser-60.html

3. An alternative would be to AddNew to the RecordsetClone of the subform.

 

[rebeccax]

Wouldn't the student have to be enrolled in the class to show up as, you
know, someone enrolled in the class?

 

[PJ Von]

Allen, Thanks so much for your help. I am a huge fan.