Subform record count warning message

[FJM]

I would like to show a Message box to an operator when a certain number of records have been reached in a subform e.g when 36 records have been entered I want a message to appear telling the operator that a certain report can then be raised. Can anyone offer some advice as to whether this is possible

[rpeare]

you can do this a couple of ways, using a dcount function is probably the most common

on your main form have a text box that is updated every time you add or remove a record from your subform. The syntax for the count query would be something like:

[FieldOnMainForm] = Dcount("*", "SubFormTableName", "[MainFormFK] = " & [MainFormPK])

if the PK on your main form is a number or

[FieldOnMainForm] = Dcount("*", "SubFormTableName", "[MainFormFK] = '" & [MainFormPK] & "'")

if the PK on your main form is a text value

[FJM]

Not quite clear what MainFormFK and MainFormPK stand for ?
Also, how do I then get the message to appear once the count reaches a specific number ?
Thanks for the reply

[rpeare]

PK is Primary Key, FK is foreign key.

So let's say your main form is called Frm_Main, Your subform is called Frm_Sub, the table holding your data for the subform is Tbl_Sub, the foreign key of that table is Main_FK (this is the field that would be linking your main and subforms), and the field containing the primary key to your main form is Main_PK your formula would look like

[FieldonMainForm] = Dcount("*", "Tbl_Sub", "[Main_FK] = " & [MainPK])

[FJM]

OK I understand that - where do I add this code ? and how do I then link it to a message box?

[rpeare]

if you're doing data entry in your subform itself you can put it in the code in your AFTER UPDATE event or ON EXIT of the last field of your subform. If you're using some other method to add data to your subform (an update or append query instead of doing data entry on the subform) you would add it to the code that does the append/update. You don't need to link it to anything if you put it in your code, it would be an unbound text box.