Can anyone tell me how I can limit the number of records that can be entered into a table?
I have a database that contains employee names: the employees grouped by categories and by location. I want to be able to set a limit to the number of entries in my table and be prompted when the maximum for each category and location has been reached.
E.g. say I want to enter a maximum 12 teachers in the state of Maryland and 90 Physicians Texas, is there a way of configuring the table so that I am prompted when the 13th teacher-name or the 91st physician name is about to be entered into the table?
depends on if you are using JET (default for Access) or a server to store your data, if the former then you have to implement such rules in forms JET doesn't natively support such actions SQL server and the like can and do
So assuming that you are using Access / JET
depends how you are capturing your data
If you are using forms then not a problem, if you allow table edits then it can't be done
what you could do is place some code in your form in a function the function should identify what the current record type is (eg doctor / teacher / systems developer etc) and then run a SQL aggregate query (ie count()) to find how many of the sepcified type exists, compare that to your systems limits and take appropriate action
place a call to your function in the forms on current and before update events, and the controls on change events
It depends on user proposed user interface as to how you handle the return from the function
you could in the forms on current event turn on / off the addnew records property of the form.
likewise you could in the controls event - the potential problem is going to be if your user decides to change someones occupation, and in changing that occupation would break your limits. that would require a bit more handling. you could of course let the user make what ever changes they wanted to and then validate if that user record would break your occupational limits - but that seems a clatty design and a design which is going to hack off your users
BTW where do you get your limits from - presumably a table, so maybe you should extend your occupation table to identify the limits, or create a new table which identifies the number of each occupation by geographical region