subform limits

  • Thread starter Thread starter Rohan via AccessMonster.com
  • Start date Start date
R

Rohan via AccessMonster.com

I have a form with a subform that shows the entries in a 1-many table that is
related to the main form's table.

How can I limit the number of entries in the subform ?

Thanks in advance,

Rohan.
 
To specify the number of records, or a percentage of records, use a query as
the Record Source of your subform, and specify the Top Values. Example:
SELECT TOP 10 Table1.* FROM Table1 ORDER BY Table1.ID;

Alternatively, to choose just the records form the last 30 days, use a query
with criteria, e.g.:
SELECT Table1.* FROM Table1 WHERE (Table1.InvoiceDate >= Date() - 30);

A third option would be to apply a filter to the subform.
 
Allen said:
To specify the number of records, or a percentage of records, use a query as
the Record Source of your subform, and specify the Top Values. Example:
SELECT TOP 10 Table1.* FROM Table1 ORDER BY Table1.ID;
Click to expand...

Ok I see how this works for limiting how many items are displayed - but I
want to limit the
number of items allowed to be entered.

Rohan.
 
Ah: misunderstood the q.

Cancel the BeforeInsert event of the form it there are already too many
records.

This example assumes the subform is bound to table "MySubTable", based on a
field named "SubID" which matches the main form's primary key (numeric)
named "MainID". It counts the records in the table in case the subform is
filtered on in Data Entry mode.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String 'Criteria.
Dim lngCount As Long 'number already existing.
Const lngcMaxRecords As Long = 10 'maximum count permitted.

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "[SubID] = " & ![MainID]
lngCount = DCount("*", "MySubTable", strWhere)
If lngCount >= lngcMaxRecords Then
MsgBox "You already have " & lngCount & " records." &
vbExclamation, "No more!"
End If
End If
End With
End Sub
 
Allen said:
Ah: misunderstood the q.

Cancel the BeforeInsert event of the form it there are already too many
records.
Click to expand...

Thank you Allen - you've been a great help.

Cheers,

Rohan.
 
Back
Top