Limit Number of Records

Hello,

I am trying to limit the number of records allowed in a
table. The best solution I have found is to create a
DELETE Query that deletes any records that go beyond the
specified limit.

This solution works okay. It allows the user to add
records, but then deletes them when they perform some
other action that triggers the DELETE Query.

Is there another/better way to set a limit on number of
records . . . possibly one that gives an error message
when they have exceeded the limit?

Any help would be greatly appreciated. Thanks in
advance!!! DV

On Mon, 2 Aug 2004 05:59:22 -0700, "Vernon"
wrote:

Is there another/better way to set a limit on number of
records . . . possibly one that gives an error message
when they have exceeded the limit?

One sneaky way is to use an Integer field as a Primary Key; set that
field's Validation Rule to

0 AND = 100

to limit the table to 100 records. The Primary Key constraint won't
let you add duplicates, and the validation rule won't let you add
values outside the range 1 to 100.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

John,

Thanks for your response. I have assigned the primary
key to an ID field with the type set to AUTONUMBER . . .
the Validation Rule is not available for autonumber for
some reason. I want this value assigned
automatically . . . is there a way to make an "Integer
field" behave like an autonumber (increment)?

Thanks again. DV

-----Original Message-----
On Mon, 2 Aug 2004 05:59:22 -0700, "Vernon"
wrote:

Is there another/better way to set a limit on number of
records . . . possibly one that gives an error message
when they have exceeded the limit?

One sneaky way is to use an Integer field as a Primary
Key; set that
field's Validation Rule to

0 AND = 100

to limit the table to 100 records. The Primary Key
constraint won't
let you add duplicates, and the validation rule won't
let you add
values outside the range 1 to 100.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
.

On Mon, 2 Aug 2004 06:41:31 -0700, "Vernon"
wrote:

John,

Thanks for your response. I have assigned the primary
key to an ID field with the type set to AUTONUMBER . . .
the Validation Rule is not available for autonumber for
some reason. I want this value assigned
automatically . . . is there a way to make an "Integer
field" behave like an autonumber (increment)?

You can, by using a Form to do your data entry. In the Form's
BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[ID]", "[yourtable]")) + 1
If iNext 100 Then
MsgBox "The table is full. Go away.", vbOKOnly
Cancel = True
Else
Me.txtID = iNext
End If
End Sub

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public