Limit number of records stored in a table 3

[whill1]

Hi, Is there a way to limit how many records can be stored in a table? I have a users table and I want to be able to limit the table so that only 5 users can be entered.
Thanks,
whill1

 

[PHV]

A validation rule (<=5) on the AutoNumber field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Sorry, disregard my previous post as you may want to delete and recreate users...

 

[RoyVidar]

I would have though not with the table itself, but perhaps PHV's idea, using a byte field and a validation rule of <5 (0-4), or here's another (clumsy) workaround.

Create an extra table, with, for instance a numeric primary key, and a text field. Enter

1 - USER1
2 - USER2
3 - USER3
4 - USER4
5 - USER5

Then relate it one to one with your user table. I e, primary key of the user table, also numeric, then with both having unique indexes, you shouldn't be able to enter more than the same number of records in your table.

Roy-Vidar

 

[whill1]

Thanks All!!! You guys are great!!
whill1

 

[whill1]

Hi, Hey Roy-Vidar I tried that and it worked. The only problem is that when I try to add a record >5 I get a generic message that says "You cannot add or change a record because a related record is required in table 'Limiter'"
I have been trying to replace this with a MsgBox that says "this database is limited to 5 users"
I've written the code for the MSgBox but I cannot figure out where to put it so that it will get caught.
I've tried The OnChange, LostFocus, AfterUpdate, and other events on the form and nothing seems to work.
Thanks again,
whill1

 

[PHV]

Try the Error event procedure of the Form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

PHV is right, trap the error and display whatever message YOU want to show.

Ken S.

 

[whill1]

Hey guys that worked except when I close my MsgBox and the form closes, the old one pops back up.
Here is the code I used.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
MsgBox ("This version is limited to 3 users")
DoCmd.Close
End Sub
Thanks,
whill1

 

[RoyVidar]

Add the following:

[tt]response = acdataerrcontinue[/tt]

which tells Access not to show the default message.

Roy-Vidar

 

[whill1]

Thanks again!!!! It works perfectly!!!
whill1