Using Expression Builder in Table to Limit Records (1 Viewer)

[lacey]

I have a "Course Date" field in my table. I want to limit the number of records in this field to 35 which is the maximum enrollment for that course. Is there an expression I can use to achieve this or some other way?

Thank you!

 

[John Big Booty]

You would need to do this at form level, and use the DCount() function. In fact your user should have no direct access to the table or the data held within them. All interaction with said data should take place through the "filter" of a form. In this way you can control how the data is displayed and how your user is able to interact with that data.

 

[vbaInet]

If you mean you want to limit the number of characters in a field to 35, then you can use the Input Mask property or a Validation Rule that implements the Len() function.

 

[Pat Hartman]

Use the BeforeInsert event of the subform used to add students.

If Dcount("*", "tblEnrolled", "CourseID = " & Me.CourseID) > 34 Then
    Msgbox "This class is limited to 35 and enrollment is filled.  Please choose a different section.",vbokOnly
    Cancel = True
    Me.Undo
    Exit Sub
End If

 

[lacey]

Well, the thing is, we currently aren't using a form for this table to enter data. This specific class requires that we register multiple people from each agency, so because a large handful of the students have the same agency/billing information, we enter them through the table for the sake of being able to copy/paste information into the records who have the same info.

I hope that makes sense. Is there any way to achieve the restriction of records in a field through the table...?

 

[John Big Booty]

If your user has direct access to the data at table level, you have no way to control how your user interact with that data. That is why you need to use a form. This allows you to control what the user sees and control how they interact with that data.

 

[Pat Hartman]

In addition to what John said - if you have duplicate data, you should split the table into two. The common data will go in the parent table and the specific data will go into the child table.