Maximum count

  • Thread starter Thread starter andre
  • Start date Start date
A

andre

Hallo

I have two related tables (students and courses)In the
course tabel there is also a field MAX_COUNT
As soon as more students sign in for a specific course I
need an alert in a message box.
What 's the easiest way to solve this problem

with kind regards andre
 
Hallo

I have two related tables (students and courses)In the
course tabel there is also a field MAX_COUNT
As soon as more students sign in for a specific course I
need an alert in a message box.
What 's the easiest way to solve this problem
Click to expand...

<chuckle> A client just asked me to add this feature to his
class-signup form! So my answer is fresh off the presses.

You'll need a bit of VBA code in the Form's BeforeInsert event. Here's
what I used:

Private Sub Form_BeforeInsert(cancel As Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Dim iSize As Integer

On Error GoTo PROC_ERR

' check to see if class is full
Set rs = Me.RecordsetClone
' Check on the parent form to see if the user has entered a max
iSize = Nz(Forms!frmEnrollment!txtClassSize, 10000)
If rs.RecordCount >= iSize Then
iAns = MsgBox("Class size limited to " & iSize & "; add
anyway?", vbYesNo)
If iAns = vbNo Then cancel = True
Me.Undo
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & _
" in Form_BeforeInsert in Form_sbfrmEnrollment:" & vbCrLf &
Err.Description
Resume PROC_EXIT

End Sub
 
'read max for specific course
max_for_course = DLookup("MAX_COUNT", "Courses", "((COURSE_ID) = " &
course_id & ")")

'if number of students is greater then max
If Me.TextCountOfStudents > max_for_course Then
Msgbox "Max number is " & max_for_course, vbExclamation, "Error"
Me.TextCountOfStudents = max_for_course
end if
 
Back
Top