Counting has become difficult

[Trial & Error]

I have a DB that collects lesson registrations, client info, etc.
On one form, the user is required to enter the date and time that the client
wishes to take their lesson. With the nature of our business we have lesson
limits on various days & hours. A subform on that form displays all limits
placed on the specific day that the user has selected ( this is aimed at
allowing them to pick the best hour for the clients lesson ).
My problem is that although the user can see the limit that has been set...
they are unable to tell how many lessons have already been booked.. and
therefore, whether or not they have crossed the limit.

Can anyone dream up a reasonably simple way of returning a "count" of the
number of entries made? It would need to reference the main form and retrieve
the date being entered by the user, and then show how many lessons have been
booked at each hour that day.

I will describe my Access awareness as moderate... take it easy on me if
this is far more simple than I have worked it up to be!

Thanks All


Cheers

 

[Allen Browne]

Use DCount() to count the number of lessons in for the person in the hour.

The 3rd argument will be the interesting one to get right. For example to
refer to the LessonDate field in the main form, you would use something
like:
=DCount("*", "MySubformTable", "[LessonDate] = " &
Format(Nz([Form].[Parent].[LessonDate], #1/1/1900#), "\#mm\/dd\/yyyy\#"))

 

[KARL DEWEY]

You need to add a datetime field with default of =Now() so as to collect the
information as to when the entry was made.
Then use a totals query and datepart to count entries.

 

[Lilliabeth]

Create a query that Groups by the Date field and Counts the a field tha
is unique for each lesson - likely the Primary Key.

Make a form based on this query. Set the Record Selector, Recor
Navigation, and Dividing Lines properties all to No. Resize the for
if necessary so it is as small as it can be.

Drag this new form into the old form so it is a subform of the ol
form.

In the Property sheet of the subform, set the Link Master Field an
Link Child Field properties to the Date field.

If you dont want the Date field displayed twice on the screen, go ahea
and delete it from the subform.