limit number of records in a table

[Zeroanarchy]

Hey all, what I am trying to do is limit the number of records in a table. The number of records will be determined by a field value.

Field.value = (say 3)

then limit records on Table (details) to 3
and lock number of records to that value.

Anyone got any ideas.


Thanks Zero

 

[JimHorton]

zero,
At the TAble level, this doesn't make sense from a dynamic sense, since each new record starts with a blank value for "field" value, so if you have 10 records, and in the 11'th you put a 9 in "field", then do you not allow that record? Or if you put a 12 in that field do you allow the record but what about the 12th record?
In other words, which record's "field" value do you use for this expression? The first, second, 5th, or the current?

For example, if you put a Table validation rule saying (lets assume autonumber is the key and we're not deleting records), and the autonumer field is RecID and the limit field is Limit. The table validation rule (In table properties in design view ) would be:
Recid <= Limit
But, this is only for the *current* record's Limit value. So you could hardcode the 'limit', by saying
Recid <= 10
However...................at the Form level all things are possible, so depending on where the 'Limit' value comes from you can just count records and if it's at the limit, disallow the insert.
--Jim

 

[lameid]

If you want to limit the number of records in a table then I would add an autonumber field and set the validation rule to make it be less than or equal to the number of records you want to limit to.

If what you want to do is limit the number of records associated with a key, then you'll need code on the before update event of all your controls with the key field in the detail that uses the text property of the control for the criteria of a dlookup to validate the entry.

 

[Zeroanarchy]

my aplolgies jim I do not think I explained myself properly.

(Form1)
controlsourse: Table A
Field.value = (say 3)


Set value need to limit table(details)

the idea behind this is that I am going to create a serial number for the product and the last diget of the serial number will be sent to [field] on the serial number form.
This is the number of staff allowed to have access to the database. ie limit the number of users to 3. If they want to have more than 3 people wanting to use the product them they pay additional money.

I hope this makes sense

thanks Zero

 

[JimHorton]

zero,
So table A is a single record table, that differs per .mdb? ie, a customer buys your .mdb and pays for 4 users, so you put 4 in TableA's [Field] value, and tableA has some other field [serialnumber]? And some other table (Users) is limited to 4 rows?

If this is so, then you can do as I said above and hardcode the '4' in the Table Validation rule, however, DON'T use Autonumber--I said Autonumber for clarity earlier, but you really don't want to use autonumber for this type of Validation, since if you delete records, then you get into that whole mess where you have 3 records, but the first record is, say, #27 or whatever and it already violates. (that's a whole other discussion)
Let me know if this is on the right track,
--Jim