Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005

    Unanswered: Limit Table Entry

    Hi All,

    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?

    Your help will be greatly appreciated
    Last edited by utcheyangel; 12-10-05 at 07:54.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts