Hi.
I would like my form to stop me so that I don't overload a
classroom.
You're in luck. Someone asked a similar question yesterday. One method of
preventing the user from entering too many records is to limit the number of
new records that can be entered on the subform. Set the subform's Cycle
Property to "All Records." Use the following code in the subform's
OnCurrent( ) event:
' * * * * Start Code * * * *
Private Sub Form_Current()
On Error GoTo ErrHandler
Dim recSet As DAO.Recordset
Dim fOpenedRecSet As Boolean
Set recSet = Me.RecordsetClone
fOpenedRecSet = True
If (Me.NewRecord And _
(recSet.RecordCount >= Nz(Me.Parent!txtCapacity.Value, 0))) Then
recSet.MoveLast
Me.Bookmark = recSet.Bookmark
Else
Me!txtStuff.SetFocus ' Set focus to first editable control.
End If
CleanUp:
If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If
Set recSet = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in Form_Current( ) in " & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
' * * * * End Code * * * *
.... where txtStuff is a text box in the subform that may be edited by the
user, and txtCapacity is a text box on the main form that holds a number
indicating the maximum capacity.
A new record will not be created, so if the primary key is an AutoNumber, it
won't increment when a new record is attempted and aborted. Therefore, it
has the effect of cycling up to the maximum number of records allowed, so
that tabbing after the last control will return focus to the first control on
that last record (which is then moved to focus on the first editable control
in the example above).
This method doesn't warn the user that the maximum number of records have
already been entered into the subform, but when the Cycle Property is set to
"Current Record," the user is never warned that only one record may be
entered, either. And since you have the calculation located in the footer of
the subform to count the number of students, you may want to put an alert
there (red font, perhaps?) that the maximum capacity has been reached for
this class.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
