forms and restrictions on adding records

P

Patrick Stubbin

I have a form with a sub form embedded. Based on information on the main
form I add data to a table within the subform. I can add upto 15 records to
the table in the subform. I can restrict this to 15. but once I hit the 15th
record the form still displays the 'add new record' bar (datasheet mode for
the subform).

Once there is 15 records in the subform, how do i stop any more records from
being added to the table. (bear in mind that there is hundreds of actual
recrods in the underlying subform table). I just wish to not allow more than
15 records that relate to the data in the main form to be added
 
A

Allen Browne

Cancel the BeforeInsert event of the subform if there are already 15 related
records.

The event procedure will look something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "[MyFKID] = " & ![MyMainID]
If DCount("*", "MySubformTable", strWhere) >= 15 Then
Cancel = True
MsgBox "No more than 15 related records."
End If
End If
End With
End Sub

Replace:
- MyFKID with the name of the subform's foreign key field.
- MyMainID with the matching field name from the main form.
- MySubformTable with the name of the subform's table.

If MyFKID is a Text type field, you need extra quotes:
strWhere = "[MyFKID] = """ & ![MyMainID] & """"
 
Top