Limit Number of Records

[Vernon]

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

 

[John Vinson]

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.

 

[Vernon]

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

 

[John Vinson]

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