The potential difficulty with reading a form to determine how many records
it has at any time is this -- the record count of the form's Recordset may
not be accurate with respect to the total number of records unless the form
has been forced to load all the records. In ACCESS, a form will usually load
only enough records for what is to be displayed, and then loads more records
when you navigate through the records.
It's possible to read the RecordCount from the form's RecordsetClone, which
tends to be more accurate than the RecordCount from the form's Recordset,
but in either case it's best to "force" the form to load all the records
before reading the RecordCount. It's not possible to force this load by a
single "expression" in a textbox's ControlSource -- one should use VBA code
to force the reading of all the records and then to read the RecordCount
value. You could do this by using a function as the ControlSource of a
textbox, and have that function do the record loading and the read of the
record count.
From your description, though, I still think it's possible to read the data
directly from the table instead of reading from the form? You can use a
DCount function to count the number of records in a table that match a
specific filtering criterion. For example, suppose I want to count the
number of records in a table named "MyTable" where the "ID" field contains a
value between 5 and 15. This is the function syntax that you would use:
DCount("*", "MyTable", "[ID] Between 5 And 15")
You can have multiple criteria. For example, suppose I also want to filter
based on a "MyName" field that starts with "Ken":
DCount("*", "MyTable", "[ID] Between 5 And 15 And [MyName] Like 'Ken*'")
Will this work for your setup?
--
Ken Snell
<MS ACCESS MVP>
Chester1 said:
--
Regards Clair
Ken Snell (MVP) said:
So what you seek is a textbox on one form and have that textbox display a
count that is based on another form's and that other form's subform's
data?
Let's first get information about the form with the subform. This form
and
subform are being used to add attendees to courses, correct? I assume
that
this form and its subform are bound to the appropriate tables so that the
data are being saved when you fill in the form and subform? This form
(with
its subform) is open when you want to see the "count" value on the other
form?
What is the purpose of the second form? Is there a reason why the count
cannot be displayed on the first form?
Normally, my recommendation is that you have a textbox that reads the
record
count from the tables directly, based on some filtering criteria
expression.
That is because you're not at the mercy of a form with the "records" on
it
having to be open, you can read the actual data stored in the tables, and
you can see data that may not be displayed on the other form at that
time.
Let's talk a bit about why you're using this setup and design. Then, it
will
become more obvious what the best solution will be for your database.
(Note: I am going to be out of town for a few days, so I won't see your
reply until I get back.)
--
Ken Snell
<MS ACCESS MVP>
The purpose of the second form is that when somebody rings up for course
dates I can go to the course instances form to check all the available
dates for all Courses without having to open the attendees form. The
courses each have 8 places. I want the field on this form so that I know
when there are any places available on specific courses so to only give
dates for courses that have places left.
It would then be useful to be able to click on the relevant date for a
course with free places enabling me to go straight to the attendees
booking
form for that course.
Chester1 said:
Dear Ken
I know I am probably not making myself very clear with this but I am
in
one
single form which hold all course instances. I want to add a field to
this
that will look at the course bookings form which has details of the
courses
and a subform which holds all attendees for that specific course and I
want
to actually count no of attendees on that course so that when I look at
the
course instances form I know how many places are left on a particular
course.
Basically, I want to use a field on a form to calculate attendees on a
totally different form.
--
Regards Clair
:
Forms and subforms in ACCESS are designed to allow the easy
entry/viewing
of
related data -- the main form holds the "parent" data, and the subform
holds
the "related" data. So, for example, if your main form has a date
field
in
its record, if you link the subform correctly through the date value,
you
can have the subform show data related to the date that is in the
textbox
for the date. This is done by the LinkMasterFields (the field(s)
and/or
control(s) on the main form that have the linking data values) and the
LinkChildFields (the field(s) and/or control(s) on the sub form that
have
the linking data values) properties of the subform control (the
control
that
holds the subform object).
To show in a main form's textbox the number of subform records in a
subform,
I typically use this expression as the control source of a textbox on
the
main form:
=[NameOfSubformControl].Form.RecordsetClone.RecordCount
where NameOfSubformControl is the name of the subform control (the
control
that holds the subform object).
--
Ken Snell
<MS ACCESS MVP>
Dear Ken
I am self taught and am not quite sure what my level of access
knowledge
would be is I am ok with creating forms reports queries
relationnships
and
all the general stuff. My problems seem to be when I need to use
event,
code
or macro builder. I have done some basic macros ie open form etc.
I have a course instances for which holds all the dates of
scheduled
courses.
I have a second form that has the course details and a subform that
displays
the attendees for a specific course.
When I open the course instances form to list the dates of courses.
I
want
to see how many places are left using information from the course
bookings
subform so that I know wether or not it is worth opening up that
course
to
book some one on.
The only thing I have tried is to insert a new text box on the
course
Instances form and type the follopwing in to the control Source
option
=Count([Course Attendees Subform]!Surname) this just comes up with
#ERROR
so
as you can see I am not sure if I am on the right track at all.
Hope this makes some sort of sense to you.
--
Regards Clair
:
What is your experience level with ACCESS right now? Your questions
are a
bit broad and I'd prefer to not jump right into all kinds of
suggestions
without some idea of what you know, what you've tried, etc.
--
Ken Snell
<MS ACCESS MVP>
-- Hi
I would be very grateful if you could help me with a couple of
issues I
have
with access forms. If you can help me please can you explain
what I
need
to
do in a basic language as I have never done VB.
1. I have created a database for course attendance. I have
one
form
that
holds all course instances ie dates and other course details. I
want
to
be
able to go into this form click on the date field and by doing
this
it
will
automatically open up the other form that I have got that is for
booking
people on to the relevant date that ii have clicked on from the
course
Instance form.
2. I would also like the course instances form to have a field
that
tells
me what numbers of places are left on a each course listed in the
form.
Waiting in antcipation
Regards Clair
