Limit Record Creation

[Scientific]

Hello all,

I have a sample database and I want to be able to limit the number of new
records that can be created. The database is a demo that will allow the user
to see that everything works. Is there a way to only allow 3 records to be
created, then have a popup say the maximum number of 3 records have been
reached?

-S

 

[bhicks11 via AccessMonster.com]

How about this:

When the record count reaches three, change the form property ALLOW ADDITIONS
to NO.

In the AFTER UPDATE event I would count the records and when it reaches 3 -
change the property above.

OR:

In the BEFORE UPDATE event, if the count is more than 3, do your POPUP and
clear the form.

Bonnie

 

[Scientific]

How about this:

When the record count reaches three, change the form property ALLOW ADDITIONS
to NO.

In the AFTER UPDATE event I would count the records and when it reaches 3 -
change the property above.

OR:

In the BEFORE UPDATE event, if the count is more than 3, do your POPUP and
clear the form.

Bonnie

Yea, that sounds like a good idea. Now I just have to figure out how to
code it.
Thanks for leading me in the right direction though

-S

 

[Daniel Pineault]

You could

Add a footer to the subform. It doesn't need to be visible on the main form.
Add a control to the footer. Give the control some meaningful name such as
RecCount. The control source should be:
=Count(*)
In the BeforeInsert event of the subform, check the RecCount.

If Me.RecCount > 9 Then
MsgBox "No more records may be added", vbOKOnly
Cancel = True
Me.Undo
End If

OR


Paste the following into your form

Public Sub LimitRecords()

Const conRecLimit = 1

With Me.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.AllowAdditions = (.RecordCount < conRecLimit)
End With

End Sub
'----- end of public function code for form -----

Then create an event procedure for the Current event of that form, and call
the above function from there:

'----- start of code for form's Current event -----
Private Sub Form_Current()

LimitRecords

End Sub

Both technics work and were provided a while back by other contributers from
this forum (there exact author is unknown).
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Scientific]

Daniel,

Wow, that looks like just what the doctor ordered. I'll give a try and
reply back to this thread. Thanks!

-S

You could

Add a footer to the subform. It doesn't need to be visible on the main form.
Add a control to the footer. Give the control some meaningful name such as
RecCount. The control source should be:
=Count(*)
In the BeforeInsert event of the subform, check the RecCount.

If Me.RecCount > 9 Then
MsgBox "No more records may be added", vbOKOnly
Cancel = True
Me.Undo
End If

OR


Paste the following into your form

Public Sub LimitRecords()

Const conRecLimit = 1

With Me.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.AllowAdditions = (.RecordCount < conRecLimit)
End With

End Sub
'----- end of public function code for form -----

Then create an event procedure for the Current event of that form, and call
the above function from there:

'----- start of code for form's Current event -----
Private Sub Form_Current()

LimitRecords

End Sub

Both technics work and were provided a while back by other contributers from
this forum (there exact author is unknown).
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

Yea, that sounds like a good idea. Now I just have to figure out how to
code it.
Thanks for leading me in the right direction though

-S

 

[Marshall Barton]

I have a sample database and I want to be able to limit the number of new
records that can be created. The database is a demo that will allow the user
to see that everything works. Is there a way to only allow 3 records to be
created, then have a popup say the maximum number of 3 records have been
reached?

If the form's recordset is not filtered. you could use the
form's Current event to disable additions:

If Me.Recordset.RecordCount >= 3 Then
Me.AllowAdditions = False
End If

You probably should put the same code in the form's
AfterInsert Event.

 

[Scientific]

Marshall,

Wow, that sounds good too. I'll try that one as well. I really want to
extend my appreciation to all responders to this thread. Without all of you
who give of yourselves without asking anything in return I honestly salute
you. Man, I love the Microsoft forums

-S