How Can 'One' Limit the Amount Records In a Table?

[simon.legg]

Hello Nice People
I would like to no how I can first of all limit how many records can be
enter in to a table. I would like to limit it to 20.
This is where I imagine it gets a bit harder, I need it so when the
21st record is entered the 1st will then be deleted, also I would like
it so that all of the data that the 1st record has a relation with is
deleted as well.
Is this possible and if so how would 'one' do it? I guess I will
probably take some VBA, which I have some knowledge of.
All suggestions are welcome

Look forward to hearing from you!
Simon

 

[jahoobob via AccessMonster.com]

Create a Make Table query that lists the Last 20 records of the table. Run
it through code in whch you susequently delete the original table and rename
the new table to the old table's name.
Why not keep adding records and view the data via a Last 20 query?

 

[John Vinson]

Hello Nice People
I would like to no how I can first of all limit how many records can be
enter in to a table. I would like to limit it to 20.
This is where I imagine it gets a bit harder, I need it so when the
21st record is entered the 1st will then be deleted, also I would like
it so that all of the data that the 1st record has a relation with is
deleted as well.
Is this possible and if so how would 'one' do it? I guess I will
probably take some VBA, which I have some knowledge of.
All suggestions are welcome

Look forward to hearing from you!
Simon

This is a very unusual thing to want to do! What entity does this
table represent? Do you in fact want to permanently and irrevokably
discard data? If so... *why*?

You can limit the data to 20 records by using an Integer primary key
field, with a Validation Rule

0 AND <= 20

so that only twenty records can exist.

To delete a record when a new record is added will be more complex.
You could force all data entry to be done using a Form, and put code
in the Form's BeforeUpdate event to run a Delete query. You'll need to
set Cascade Deletes in all relationships to this table (and to
"grandchild" tables as well), and have some sort of criterion based on
a field in the table to identify which is the "first" or "oldest"
record.

John W. Vinson[MVP]