Limit Table Records

[Andy]

Hi;

Have been searching for an answer to these two questions for some time.

Need to limit a sub-form, (displayed as datasheet), to 1 record only, and in
another form need to limit the table it's based on to 5 records.

Any suggestions?

Andy

 

[Allen Browne]

Cancel the BeforeInsert event of the form if it already has the maximum
number of records.

Here is an example Event Procedure for the form's BeforeInsert.
It checks that there is a record in the main form first.

Then it asks for the count of related records directly from the subform's
table (in case the subform is filtered). In the example, the primary key on
the main form named "ID", and the matching field field in the subform is
named "MyFK". Both are assumed to be Number fields:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
If DCount("*", "MyTable", "[MyFK] = " & Me.Parent![ID]) >= 5 Then
Cancel = True
MsgBox "No more records!"
End If
End If
End Sub

 

[Andy]

Allen;

Thank You.

Andy

Cancel the BeforeInsert event of the form if it already has the maximum
number of records.

Here is an example Event Procedure for the form's BeforeInsert.
It checks that there is a record in the main form first.

Then it asks for the count of related records directly from the subform's
table (in case the subform is filtered). In the example, the primary key on
the main form named "ID", and the matching field field in the subform is
named "MyFK". Both are assumed to be Number fields:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
If DCount("*", "MyTable", "[MyFK] = " & Me.Parent![ID]) >= 5 Then
Cancel = True
MsgBox "No more records!"
End If
End If
End Sub

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

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

Have been searching for an answer to these two questions for some time.

Need to limit a sub-form, (displayed as datasheet), to 1 record only,

and

in
another form need to limit the table it's based on to 5 records.