Restrict number of records 
Author Message
 Restrict number of records

Hello,

I have a scenario where I wish to restrict my users from entering lots of
records. This is to act as a demo database to demonstrate the features of
the app. Is there anyway of testing to see if a table has more than a
specified number of records and denying the user to create any more if so?

Idealy this would be at a table level (but I think this can only be done
with SQL Server), but any solution could be adapted.

I'm using Access 97 and 2000.

TIA

Mike



Mon, 28 Mar 2005 00:18:51 GMT  
 Restrict number of records
You might try something like this:

1.  Add an AutoNumber field to your table, say named "Field1".

2.  Set the AutoNumber field's properties as follows:

a)  Field Size = Long Integer
b)  New Values = Increment
c)  Indexed = Yes (No Duplicates)

3.  Set the Validation Rule property for the *table* (via the Table
Properties window, which you can display in table design view by choosing
Properties from the View menu) to an expression like:

[Field1] Between 1 And 100

if you wanted to limit the number of records to no more than 100.

You might also want to set the Validation Text property of the table to
something like "No more than 100 records allowed in demonstration version".

This approach relies on an AutoNumber, and so is subject AutoNumber behavior
that may limit the number of records to less than 100.  For example, when a
user begins adding a new record, the AutoNumber gets incremented.  If the
user cancels before finishing, the AutoNumber value created for the aborted
record is lost.  To avoid this, you might instead use a straight Long
Integer field to which either your application or your user assigns a value.

Either way, this type of limitation is easily circumvented unless you
properly secure your database.

Quote:

> Hello,

> I have a scenario where I wish to restrict my users from entering lots of
> records. This is to act as a demo database to demonstrate the features of
> the app. Is there anyway of testing to see if a table has more than a
> specified number of records and denying the user to create any more if so?

> Idealy this would be at a table level (but I think this can only be done
> with SQL Server), but any solution could be adapted.

> I'm using Access 97 and 2000.

> TIA

> Mike



Mon, 28 Mar 2005 03:52:12 GMT  
 Restrict number of records
Thanks Brian,

I'll give it a go.

Mike


Quote:
> You might try something like this:

> 1.  Add an AutoNumber field to your table, say named "Field1".

> 2.  Set the AutoNumber field's properties as follows:

> a)  Field Size = Long Integer
> b)  New Values = Increment
> c)  Indexed = Yes (No Duplicates)

> 3.  Set the Validation Rule property for the *table* (via the Table
> Properties window, which you can display in table design view by choosing
> Properties from the View menu) to an expression like:

> [Field1] Between 1 And 100

> if you wanted to limit the number of records to no more than 100.

> You might also want to set the Validation Text property of the table to
> something like "No more than 100 records allowed in demonstration
version".

> This approach relies on an AutoNumber, and so is subject AutoNumber
behavior
> that may limit the number of records to less than 100.  For example, when
a
> user begins adding a new record, the AutoNumber gets incremented.  If the
> user cancels before finishing, the AutoNumber value created for the
aborted
> record is lost.  To avoid this, you might instead use a straight Long
> Integer field to which either your application or your user assigns a
value.

> Either way, this type of limitation is easily circumvented unless you
> properly secure your database.


> > Hello,

> > I have a scenario where I wish to restrict my users from entering lots
of
> > records. This is to act as a demo database to demonstrate the features
of
> > the app. Is there anyway of testing to see if a table has more than a
> > specified number of records and denying the user to create any more if
so?

> > Idealy this would be at a table level (but I think this can only be done
> > with SQL Server), but any solution could be adapted.

> > I'm using Access 97 and 2000.

> > TIA

> > Mike



Mon, 28 Mar 2005 15:57:10 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. How to restrict the number of records in a data grid

2. Restricting the number of records.

3. Restricting the number of lines in a userform textbox

4. Multiuser Issues - Restricting the Number of Simultaneous Logins

5. Restricting number of instances of ActiveX Control

6. treeview - restrict number of sub nodes

7. Restrict textbox to numbers only

8. Restricting the number of clients connected to ORACLE Database

9. restrict the set of record return

10. Restricting Database Access by Record

11. Restricting # of records returned by report

12. I am getting duplicate records, I mean everything is duplicate even Access record number

 

 
Powered by phpBB® Forum Software