Limit the number of records in a table

[Guest]

i have a table in a Access2003 database which if for employee details. I dont
want the table to have more then 5 records.

Is there a way to limit the number of records a table can have by using vb
code?

If a user has a certain access level then they can have up to 5 employees or
if the user has another level of access they can have up to 10 employess.

Thanks to anyone who looks into this!

 

[Rick Brandt]

i have a table in a Access2003 database which if for employee
details. I dont want the table to have more then 5 records.

Is there a way to limit the number of records a table can have by
using vb code?

If a user has a certain access level then they can have up to 5
employees or if the user has another level of access they can have up
to 10 employess.

Thanks to anyone who looks into this!

In the BeforeInsert event count the number of existing rows. Display a message
and cancel the event if the desired maximimum already exists.

 

[Guest]

Thanks for the quick reply Rick

Please verify the following:

1: In the BeforeInsert event - Is this the before update event of the form
that is linked to the table?

2: Count the number of existing rows - What code would i use to count
tatbles. Never done this before.

 

[Rick Brandt]

Thanks for the quick reply Rick

Please verify the following:

1: In the BeforeInsert event - Is this the before update event of the
form that is linked to the table?
Yes.

2: Count the number of existing rows - What code would i use to count
tatbles. Never done this before.

If DCount("*", TableName) = 5 Then...

 

[Guest]

Many thanks

If DCount("*", TableName) = 5 Then...

 

[David Cox]

add a field with type integer and have a validation rule between 1 and 5

 

[Steve Schapel]

StuJol,

1: In the BeforeInsert event - Is this the before update event of the form
that is linked to the table?

No. The form has a Before Insert property and a Before Update property,
and they are quite distinct from each other. As Rick mentioned, Before
Insert is the way to go.

2: Count the number of existing rows - What code would i use to count
tatbles. Never done this before.

DCount("*","YourTable") will give you the total number of records in
the table. In your case, however, you will need to count the number of
records in a query, based on the user's access level. In order to do
this, it will help if somewhere in your database you also have recorded
the number of allowed employees per access level. Do you already have
an AccessLevels (or whatever you have named it) table? If not, you can
easily make one now, with at least fields for AccessLevel and Employees.
How you proceed from there will depend on this... at th point where a
new record is about to be entered, how can we know the User (and hence
the user's access level)?

 

[Jamie Collins]

add a field with type integer and have a validation rule between 1 and 5

You may want to add a UNIQUE constraint to that one ;-)

FWIW you can use a Jet 4.0 table-level CHECK constraint e.g.

ALTER TABLE Test ADD
CONSTRAINT max_five_rows
CHECK(5 >= (
SELECT COUNT(*)
FROM Test AS T2)
);

Jamie.

--