• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1171
  • Last Modified:

limit max records

Hi

Have a form (continuous) based on single table.
How do I limit max number of records that can be inserted into this table. Want it to hold max 5 records.

Thank you.
0
ohgee
Asked:
ohgee
1 Solution
 
jefftwilleyCommented:
How are you inserting? You can loop through an insert SQL statement to accomplish what you want if that's how you're doing it. If it's an insert query go to properties and select Top5. Any other criteria?
0
 
Patrick MatthewsCommented:
Hi ohgee,

Here is one way.  Use this code for your form's BeforeInsert event:


Private Sub Form_BeforeInsert(Cancel As Integer)

    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
   
    Const RecLimit As Long = 7 'max records allowed in table
   
    Set cnn = CurrentProject.Connection
    rs.ActiveConnection = cnn
   
    rs.Open "SELECT Count(*) AS NumRecs FROM YourTable"  'update as needed
   
    If rs!NumRecs >= RecLimit Then
        MsgBox "Cannot add more records without violating limit"
        Cancel = True
    End If
   
    Set rs = Nothing
    Set cnn = Nothing
   
End Sub


Regards,

Patrick
0
 
Patrick MatthewsCommented:
ohgee,
> How do I limit max number of records that can be inserted into this table. Want it to hold max 5 records.

Please keep in mind that my suggestion should keep people from entering new records *using the form* once the
limit's been reached (or exceeded).

However, this will not prevent someone from just opening the table and directly entering new records.

Regards,

Patrick
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ohgeeAuthor Commented:
Thanks Patrick, form limitation is sufficient.
Will check it out tomorrow evening.

A top value might work too, form is based on a query. But am more interested in preventing data entry, rather than allowing unlimited entry but only showing 5
Thanks
0
 
nico5038Commented:
In a case like this I use in the OnCurrent:

Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 5 Then
   Me.AllowAdditions = False
Else
   Me.AllowAdditions = True
End If
End Sub

This stops the possibility to add rows.

Nic;o)
0
 
ohgeeAuthor Commented:
Will use Nico's solution, works fine as is.
Thanks for all the help.
Regards
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now