How do you limit the number of times a value can be submitted in Access?

Question

For example, I am making a database to keep track of trainings and the details of who/when individuals within the business attend those trainings. However, once a training is created I want to restrict how many people can sign-up for that training.

Still haven't found thte answer I'm looking for - I have a field that collects the maximum number allowed. My question is now how do I do a count of the entries and compare that to the maximum entered. What is the expression I would need to enter for the validation? Please be specific.

Answer 1

I don't know what you have or don't have in your database, but this is the way I would do it . . .

There would be a table containing information about the various trainings that are offered. The Primary Key would be a training identifier. In addition to things like training name and description, there would also be a field for maximum number of participants allowed.

When a new training is added, the "max_size" field would be populated at the same time.

There would be a form for users to select the particular training they want to receive. Then, when the user selects a training, code would check the number of existing enrollees against the "max_size" field.

If enrollees < max_size then the user is presented with the enrollment form for that training. Otherwise, a message is displayed, informing the user that the class is already filled.

§

Answer 2

Create a lookup table for the class instance and create a field with the class limit. select the current number of enrollments and compare against on this table first to make sure the count is < the allowed limit. If the class hits it's limit, you can display a FULL note. (or similar)

Answer 3

I am not sure how you could do it through the table designer or the wizards, but I know what you could do : use ADO to open a recordset to the table, and count the number of records returned. If 20 or more are returned, tell the user the class is full; if not, allow them to sign up for it. If you are not familiar with ADO, check this out :

http://msdn2.microsoft.com/en-us/library/ms681025.aspx

Good luck : )

Answer 4

Get rid of access??? Microsoft makes its biggest profit with office and access is used by 80 percent of all businesses. Microsoft has NEVER said get rid of access. I'm a Microsoft certified developer in Access, SQL, and .NET. Just wondering where someone would get that information?

Answer 5

First of all, get rid of Access. Even Microsoft says so. Get SQL Server Express (free).

Use the COUNT() function. (This will work in Access or SQL) If you want to limit it, you'll be wanting to check the value of COUNT() so that if COUNT() > (the number of registrations you will allow), you won't get any more.

This is a programatic statement, you can't do this at design-time...you have to do it at run-time, programmatically.