how to limit the record number

[Jon]

Greeting

I have a form for employees over time which has employee ID , Name and the
number of over time limit. This form has a subform for over times . what I
want to do is limiting the number of records as per each employee. For
example. The number of record should not excess 4 records like this:

SQ Employee ID overtime
1 1 3
2 1 2
3 1 2
4 1 3

If the time keeper tried to add 1 more record, the database does not accept
this transaction by display message. How can I do that please?

 

[akphidelt]

Here is something you can try, now you might have to tweak it so it fits your
needs, but basically you want to set up a before update event. You can set
this on the Employee ID text box on the form. So you would right click, go to
properties, Event, Before Update... and go to build a code.

Type in the code

Dim ID_Count as Integer

ID_Count = DCount("[employee ID]","tblName","[employee ID]=" &
Me.[EmpIdTextBox])

If ID_Count > 3 Then
MsgBox "You can not enter more the 4 records for an employee", vbCritical
Me.Undo
End If

Things to keep in mind. If your employee ID is not set to a numerical data
type this will not work.

 

[Ken Sheridan]

You could prevent more than four rows being entered in the subform per
employee by putting the following in the subform's Current event procedure:

Me.AllowAdditions = Me.RecordsetClone.RecordCount < 4

Once the fourth row had been inserted there would be no empty 'new record'
row in the subform at all. The process is reversible, so if one of the rows
is deleted for any reason a 'new record' row would become available again.

Ken Sheridan
Stafford, England