Limiting the number of records

[sharontodd]

I am setting up a demo version of a database and want to limit the number of
records that can be entered. I have one table that uses a one record per
form entry and another that uses a subform that appears as a table. Each
record is numbered.

On the subform, I am checking the number to see if they have exceeded the
allowed amount. I have a message box come up and set the count back one, but
how do I eliminate the row they are typing in?

For the form showing one entire record, can I check to see that they have
reached the last allowed record and not allow a new form to come up. How can
I keep a new form from coming up and where would I place the test for this.

This is my first experience programming in Access but I do have other
programming experience - not familiar with the commands available here.

Thanks for any help.

Sharontodd

 

[Ken Snell \(MVP\)]

The way to prevent a record from being saved is to use the Form's
BeforeUpdate event to run the Me.Undo action; it clears the record's
entries.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If "SomeCondition" = True Then
Cancel = True
Me.Undo
End If
End Sub

 

[Tom van Stiphout]

On Sat, 5 Jul 2008 20:17:03 -0700, sharontodd

Record numbering is not needed to count them, and is also often a bad
idea for other reasons.
Ideally you would use a cancellable event for this test: some event
procedures like the following allow Cancel argument to be set to True
so the action does not occur.

Private Sub Form_BeforeInsert(Cancel As Integer)
Const TRESHOLD = 10
If Me.RecordsetClone.RecordCount >= TRESHOLD Then
MsgBox "Yo! Demo version does not allow more than " & TRESHOLD & "
records.", vbCritical
Cancel = True
End If
End Sub

-Tom.

 

[Marshall Barton]

I am setting up a demo version of a database and want to limit the number of
records that can be entered. I have one table that uses a one record per
form entry and another that uses a subform that appears as a table. Each
record is numbered.

On the subform, I am checking the number to see if they have exceeded the
allowed amount. I have a message box come up and set the count back one, but
how do I eliminate the row they are typing in?

For the form showing one entire record, can I check to see that they have
reached the last allowed record and not allow a new form to come up. How can
I keep a new form from coming up and where would I place the test for this.

This is my first experience programming in Access but I do have other
programming experience - not familiar with the commands available here.

Also check VBA Help on the AllowAdditions property. You
could use a little code in a form's Open event to check the
number of records in a table and set the property
accordingly:

Me.AllowAdditions = (DCount("*", "tablename") < maxrecords)

Of course, that will only limit the ability to use the form
to create records. If your table/queries are not securely
locked down, then the form can be bypassed and if your app
is not an MDE, then any code you use could be
modified/deleted.