restrict data entries in an access database

[Guest]

Hi,

I am currently developing an access database for a video library. I have a
table called videos rented with three attributes Rental ID, Video ID and copy
number. I want to be able restrict the number of videos taken out by each
member to 3. Is this possible and how would i go about it.

Thanks
Shannon

 

[Pat Hartman\(MVP\)]

I assume that you have a main form for the member and a subform for rentals.
Add a footer to the rental subform and add a control with the following
ControlSource -
=Count(*)

In the BeforeInsert event of the subform, check the count field in the
footer. If it is > 2, cancel the Insert and display a message that n videos
are already on loan.

If Me.CountField > 2 Then
Cancel = True
MsgBox "You already have " & Me.Countfield & " videos on loan. Please
return one before taking out a new one.", vbOKOnly
End If