How to test Multiple Conditions before a record is allowed in a Table? Ask

[hardsoft]

Hello everyone!

I'm new to Access and I've started developing a Student Enrollment Database for my school using online resources.

I am trying to Enroll Students in some subjects (Minumum of 8 and Maximum of 9).

FOLLOWING ARE OBTAINABLE:
1. There is 1 Session in a Year
2. There are 3 Terms in a Session
3. Student will spend 3 years in the school.
3. A student can only enroll up to 9 different subjects in 1 Term through year 1 to 3.
4. Student will be Promoted to another Class in another Session and offer same subjects. (Require another enrollment, I think)

WHAT I ALREADY HAVE
1. I have a Table called tblEnrolled in which enrollment is recorded.
2. I have a form for the enrollment

I WANT THE FOLLOWING CONDITIONS TO ME MET BEFORE A RECORD IS ALLOWED IN THE TABLE:
1. No student can enroll a perticular Subject more than 1 in a Term (Want to check if the selected Student has already enrolled in the selected Subject for the selected Term and for the selected Session)
2. No student can enroll more than nine (9) different Subjects in a Term.

WHAT I REALLY WANT?

I'm using a form to input data into the table.

I want the On_Click Event of the SaveButton (VBA code) to check any of the above criteria is not violated.

Thank you for your help.

The code i'm currently using to post to this table is as follows:

Dim NewSubjectCode As String
Dim NewSubject As String
Dim stLinkCriteria As String
Dim strCriteria As String
Dim strMainCriteria As String
On Error GoTo Err

If IsNull(cboSession) Then
    MsgBox "Please select SESSION to proceed.", vbInformation, "Required"
    Me.cboSession.SetFocus
    Exit Sub
End If
If IsNull(cboTerm) Then
    MsgBox "Please select TERM to proceed.", vbInformation, "Required"
    Me.cboTerm.SetFocus
    Exit Sub
End If
If IsNull(cboSelectClass) Then
    MsgBox "Please select CLASS to proceed.", vbInformation, "Required"
    Me.cboSelectClass.SetFocus
    Exit Sub
End If
If IsNull(cboName) Then
    MsgBox "Please select STUDENT to proceed.", vbInformation, "Required"
    Me.cboName.SetFocus
    Exit Sub
End If
If IsNull(cboCode) Then
    MsgBox "Please select SUBJECT to proceed.", vbInformation, "Required"
    Me.cboCode.SetFocus
    Exit Sub
End If

NewSubject = Me.txtSubjects.Value
NewSubjectCode = Me.cboCode.Column(0)
NewStudentID = Me.txtStudentID.Value
stLinkCriteria = "[SubjectCode] = " & "'" & NewSubjectCode & "'"
strCriteria = "[StudentID] = " & "'" & NewStudentID & "'"
strMainCriteria = stLinkCriteria & "And" & strCriteria

If Me.cboCode.Column(0) = DLookup("[SubjectCode]", "QueryEnrollmentDetails", strMainCriteria) Then
    MsgBox "" & NewSubject & " is already selected for this student.", vbCritical, "Duplicate Subject"
    Me.Undo
    Me.cboCode.SetFocus
    Me.txtStudentID = Me.txtID
    Me.txtStudentName = Me.cboName
    Me.txtStudentClass = Me.txtClass
    Me.txtSession = Me.cboSession
    Me.txtTerm = Me.cboTerm
Exit Sub
Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRec
    Me.[SubformSubjects].Requery
    Me.cboCode.SetFocus
    Me.txtStudentID = Me.txtID
    Me.txtStudentName = Me.cboName
    Me.txtStudentClass = Me.txtClass
    Me.txtSession = Me.cboSession
    Me.txtTerm = Me.cboTerm
End If
Err:
Exit Sub
End Sub

 

[Ranman256]

You want a form NOT connected to the data table. Rather a temp table.
User enters the data,then when SAVE is clicked,
Run queries,counts, vb code to verify all the entries.
If All are correct,run append queries to add to main data table.

 

[hardsoft]

I appreciate your help, @Ranman256.

I get your idea but don't know how to go about it.