Limit Recordset to 3 products

[DD]

I have a main form which describes a product. I have a
subform on the main form which allows a user to log
product options. I want to limit the user to only be able
to enter 3 records into the subform (and underlying table)
for each product. How can I achieve this?

 

[John Vinson]

I have a main form which describes a product. I have a
subform on the main form which allows a user to log
product options. I want to limit the user to only be able
to enter 3 records into the subform (and underlying table)
for each product. How can I achieve this?

Put code in the BeforeInsert event of the Subform; use DCount() to
count how many records have been added, and cancel the insert with a
polite message if they've already added three. E.g.

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "tblOptions", "[ProductID] = " & Parent!ProductID _
& " AND CustomerID = " & Parent!CustomerID) >= 3 Then
MsgBox "Please only enter three options", vbOKOnly
Cancel = True
End If
End Sub