Set maximum count of records

[mkfloque]

Hello All,

In a table/form, i'd like to make sure that each study ID has no more than 3 questionnaires. So, basically, in this particular table/form, the max count for each study ID should be 3. Any ideas on how to do? Thank you!

[Bob Fitz]

I don't think it can be done at the table level but should be easy enough if done at the form level. Any data being added to tables or existing data being edited should with a form anyway.

[Micron]

Further to Bob's assertion regarding forms:
If you're using a temp table like I was, you can check the number of records. This uses the form textbox control txtNPno as criteria for the count, and runs on the BeforeInsert event.

If DCount("*", "tempNPitems", [NPno] = Me.txtNPno) > 6 Then
  MsgBox "Only six records allowed!", vbOKOnly
  Cancel = True
End If

I guess you could do the same thing against your form or subform if it's a datasheet, or against your main table using your study ID as criteria.