forms and restrictions on adding records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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] & """"
 
Back
Top