?
Solved

limit max records

Posted on 2006-07-11
6
Medium Priority
?
1,168 Views
Last Modified: 2008-02-01
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
Comment
Question by:ohgee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17087311
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 17087384
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 17087442
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
Independent Software Vendors: 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!

 

Author Comment

by:ohgee
ID: 17087486
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 300 total points
ID: 17088109
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
 

Author Comment

by:ohgee
ID: 17089429
Will use Nico's solution, works fine as is.
Thanks for all the help.
Regards
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question