Add record programmatically with values from two recordsources

  • Thread starter Thread starter PJ Von
  • Start date Start date
P

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.
 
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.
 
Wouldn't the student have to be enrolled in the class to show up as, you
know, someone enrolled in the class?
 
Back
Top