Access subform one-to-many realationship

[accesss2009]

Hello,

I am fairly new to access and I hope I am posting this in the right forum.
I created a form that has a subform that is one-to-many relationship.
It works well. I change the ID on the main form and the related fields on the
subform change. I need to allow the user to add two serial numbers in the
subform per the mainform ID. Only two of these type serial numbers can be
related
to one ID. The form works but when I tab down through the subform it will
allow
the user to enter more then two serial numbers. How can I limit the subform
to only
allow two serial numbers to one ID? Thanks!!!

 

[Allen Browne]

Cancel the Before Insert event of the subform if you find there are already
2 related records.

The example below counts related records in the subform's table, so the user
can't get around it just by filtering the subform.

In the example, we assume:
- primary key of the main form's table is named MainID (numeric field.)
- subform's table is named Table2, and the foreign key is named SubID.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Const lngcAllowed As Long = 2

Dim lngHowMany As Long
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
strWhere = "[SubID] = " & ![MainID]
lngHowMany = DCount("SubID", "Table2", strWhere)
If lngHowMany >= lngcAllowed Then
Cancel = True
MsgBox "You already have " & lngHowMany & " entries." & _
vbCrLf & "Only " & lngcAllowed & " allowed."
End If
End If
End With
End Sub

This won't be bombproof if multiple users are entering new records at the
same time.

An alternative would be an integer field in the table, with a Validation
Rule of:
1 or 2
and a unique index on this field + the foreign key field in combination.