Limiting subform entries based on mainform field value

[Anika V. Bristalli]

Hi,

I am sure that someone has already asked (and got an answer) to my
question, but I was not able to find the exact answer by searching
through the previous topics. Big part of it is probably the fact that
I am fairly new to Access, and probably was not searching for the
right key words. But at any rate, here is my question.

I am trying to build a form (with a subform) that allows consultants
within a company to record their client consultations. So, here are
the details.

I have a mainform with the following fields:
Clients (ClientID, Client Name),
Client's membership status (Status)
Number of days of consulting (ConsultNo)

The membership status determines the number of days of consulting, but
both fields are present on the form for reference.

I also have a subform, which is based on a table of records of actual
consulting appointments for each client. This subform includes the
following fields:
Date
Consultant Name
Subject

What I am trying to figure out is how to limit the number of entries
per client (which is in the subform) based on teh number of consulting
appointments available to them (which is in the main form). So, in
general, I am trying to figure out how to limit a number of particular
entries in a subform. Note: I am not trying to limit the total number
of entries in the subform table.

I am not sure whether this description captures what I am trying to
do, but I would really appreciate any tips or suggestions.

Thank you very much in advance!

 

[Allen Browne]

Use the BeforeInsert event procedure of the subform.
Cancel the event if there are already the full number of records.

You could get the number of records from the subform with:
Me.RecordsetClone.RecordCount
But that would not work if the subform were filtered, so it might be better
to use:
DCount("*", "MySubformTable", "MyForeignID = " & Nz(Me.Parent![ID],0))