Limiting Record Number in Subform

[Murphette]

I have a main form (F-BMSClasses) with a subform (F-BMSAttendance). The main form displays one of the possible classes students can be registered for, and the subform displays who is registered or allows more to be registered. In the subform Current Event, I placed this code to limit the number of records:

Private Sub Form-Current()
‘Code to limit the class size to 14.

Dim cnt As Long
cnt = Me!RecordsetClone.RecordCount

If cnt<>0 and cnt >13 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True

End If

With this code, I seem to be able to add records to classes that have no one in them, and I can add students to those classes that have less than 14. If I reach capacity in any one class the code successfully triggers the AllowAdditions = False statement for that class. However, when I move to another class (the next record of the main form) from that point on the AllowAdditions = False statement seems to persist, despite the class being empty or under capacity. In previous postings I have read that the Me!RecordsetClone.movelast method may need to be applied. I have tried using it in various positions within the above code and have had no success. Can someone suggest a correction or a whole different direction to control the number of subform records? (I have toyed with a control on the main form [txtCountStaff] with its data source the dCount function. I was able to correctly display in that control the number of records in the subform. I even used it to hide or unhide the subform depending on the count, but does not prevent someone from overbooking the class once they are in the subform.) Please share your collective wisdom…Thanks!

 

[Remou]

I think a count in a textbox in the subform footer might be the most suitable. You can refer to this in the main form or check it in the subform. Use:

[tt]=IIf(IsNumeric(InsertCorrectRef),InsertCorrectRef,0)[/tt]

For references in the main form to avoid #Error when there is no recordset.

 

[MajP]

Your code works fine, but you have 2 typos

Private Sub Form_Current()
cnt = Me.RecordsetClone.RecordCount

Me!Recordsetclone should have thrown an error. It is neither a field name or a member of a collection

 

[TheAceMan1]

How are ya Murphette . . .

With a few changes I have your code working fine! Change to:

[blue]   If Me.Recordset.RecordCount > 13 Then
      Me.AllowAdditions = False
   Else
      Me.AllowAdditions = True
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[PHV]

In the CurrentEvent procedure of the subform:
Me.AllowAdditions = (Me.Recordset.RecordCount < 14)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Murphette]

Hi All,
I used your suggestion, TheAceMan1, in the Current Event of the subform, but the basic issue persisted. When I fill one class to the max, and then move to another record in the main form (i.e. another class) the subform shows only the field headings, and there is no opportunity to insert a new record...the subform is still under the AllowAdditions.False state. I needed to find something outside of the subform to counteract that state. I managed a work-around solution that seems effective. I kept my main form control txtCountStaff. I then put the follwing code into the Current Event of the main form:
txtCountStaff.Requery
If txtCountStaff.Value = 0 Then
Me![F-BMSAttendance].Form.AllowAdditions = True
End If

I appreciate everybody's input. The end result is success. Thanks!