Solved

limit max records

Posted on 2006-07-11
6
1,158 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
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 92

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 92

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 75 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

770 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