Limiting the number of records that can be created

  • Thread starter Thread starter Ant
  • Start date Start date
A

Ant

I have a two tables with a one to many relationship and I would like to
limit the many side of the relationship to a set number of records, lets say
5. Is this possible and how would I go about doing it? I have limited
experience when it comes to VBA so if using VBA is the answer could you
please give examples



Thanks
 
You probably have a main form for the main table, and a subform for this
related table. If so, you can cancel the BeforeInsert event of the subform
if there are already 5 records.

The Event Procedure would look something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim lngCount As Long

If Me.Parent.NewRecord Then
Cancel = True
MsgBox = "Enter a record in the main form first."
Else
strWhere = "[ForeignID] = " & Me.Parent![ID]
lngCount = DCount("*", "MySubformTable", strWhere)
If lngCount >= 5 Then
Cancel = True
MsgBox "You already have " & lngCount & " records."
End If
End If
End Sub

Replace:
- MySubformTable with the name of your subform table.
- ForeignID with the name of the foreign key field (that links the subform
to the main form record).
- ID with the name of the main form's primary key field.

(It is safer to use DCount() to get the number of records from the table, in
case the subform is filtered.)
 
Thanks I 'll give that a go. I was begining think that there wasn't an
answer.

Ant

Allen Browne said:
You probably have a main form for the main table, and a subform for this
related table. If so, you can cancel the BeforeInsert event of the subform
if there are already 5 records.

The Event Procedure would look something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim lngCount As Long

If Me.Parent.NewRecord Then
Cancel = True
MsgBox = "Enter a record in the main form first."
Else
strWhere = "[ForeignID] = " & Me.Parent![ID]
lngCount = DCount("*", "MySubformTable", strWhere)
If lngCount >= 5 Then
Cancel = True
MsgBox "You already have " & lngCount & " records."
End If
End If
End Sub

Replace:
- MySubformTable with the name of your subform table.
- ForeignID with the name of the foreign key field (that links the subform
to the main form record).
- ID with the name of the main form's primary key field.

(It is safer to use DCount() to get the number of records from the table, in
case the subform is filtered.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ant said:
I have a two tables with a one to many relationship and I would like to
limit the many side of the relationship to a set number of records, lets say
5. Is this possible and how would I go about doing it? I have limited
experience when it comes to VBA so if using VBA is the answer could you
please give examples
Click to expand...
Click to expand...
 
Back
Top