Continuous Subform Count field

[Al Camp]

Main form (frmConfrences from tblConferences) ONE
Sub Form (frmScheduledEvents from tblScheduledEvents) MANY
Link = ConferenceID (Long)

The subform lists all events at a conference (A-1, B-3, D14, etc...), and
in another table (tblAttendeeWorkshops), these available vents will be
assigned to Attendees. Perhaps 50 attendees will sign up for workshop A-1,
and maybe 40 will sign up for B-3.

In my frmScheduledEvents subform I'd like to have a column that shows how
many booking there are against each workshop vs. the WorkshopCapacity field.
As of now this is an unbound field, so Dlookup doesn't work with the
continous form.
I tried attaching a "totals" pass thru query to my subform query, (to try
to a "bound" filed, but that made the sub records "un-updateable" no matter
how I tried to link the totals
I even tried a small "field sized" sub-subform, but the subform itself is
continuous, so no deal.

Can anyone just describe how they've handled this problem in the past.
"Aircode" would be fine, or just a verbal description of the process.

Thanks in advance,
Al Camp

 

[Ken Snell [MVP]]

You can put a textbox on the subform, and set its control source to a
"=DLookup" or "=DSum" or other domain function that uses a field in that
subform as part of the criterion expression:

=DLookup("MyField","MyTable","MyIDField=" & [IDField])

 

[Al Camp]

Well... I'll be darned. I tried that before, and got the same count in
each subform record. So... I thought that was because the field was
unbound... like an entered value in an unbound field showing across all
records.

After your suggestion, I tried a DCount against the tblAttendeeCourses
table again... and it worked just fine!

=DCount("[ConfEvent]","tblAttendeeCourses","ConfEvent =
Forms!frmConferences!frmScheduledEvents.Form!ConfEvent")

By the by...is there any other way to refer to the ConfEvent value on the
subform in the DCount Where argument by using a simpler addressing scheme...
like...
....."ConfEvent = " & Me.ConfEvent

Thanks a lot Ken. I've got lay off those 12 hour "fingerboning"
sessions... I'm losin' it!

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

You can put a textbox on the subform, and set its control source to a
"=DLookup" or "=DSum" or other domain function that uses a field in that
subform as part of the criterion expression:

=DLookup("MyField","MyTable","MyIDField=" & [IDField])

--

Ken Snell
<MS ACCESS MVP>

Main form (frmConfrences from tblConferences) ONE
Sub Form (frmScheduledEvents from tblScheduledEvents) MANY
Link = ConferenceID (Long)

The subform lists all events at a conference (A-1, B-3, D14, etc...),
and in another table (tblAttendeeWorkshops), these available vents will
be assigned to Attendees. Perhaps 50 attendees will sign up for workshop
A-1, and maybe 40 will sign up for B-3.

In my frmScheduledEvents subform I'd like to have a column that shows
how many booking there are against each workshop vs. the WorkshopCapacity
field.
As of now this is an unbound field, so Dlookup doesn't work with the
continous form.
I tried attaching a "totals" pass thru query to my subform query, (to
try to a "bound" filed, but that made the sub records "un-updateable" no
matter how I tried to link the totals
I even tried a small "field sized" sub-subform, but the subform itself
is continuous, so no deal.

Can anyone just describe how they've handled this problem in the past.
"Aircode" would be fine, or just a verbal description of the process.

Thanks in advance,
Al Camp

 

[Ken Snell [MVP]]

You can omit all the form references if you want to use a field/control that
on the same form as the textbox whose control source uses it:

=DCount("[ConfEvent]","tblAttendeeCourses","ConfEvent =" & [ConfEvent])

--

Ken Snell
<MS ACCESS MVP>

Well... I'll be darned. I tried that before, and got the same count in
each subform record. So... I thought that was because the field was
unbound... like an entered value in an unbound field showing across all
records.

After your suggestion, I tried a DCount against the tblAttendeeCourses
table again... and it worked just fine!

=DCount("[ConfEvent]","tblAttendeeCourses","ConfEvent =
Forms!frmConferences!frmScheduledEvents.Form!ConfEvent")

By the by...is there any other way to refer to the ConfEvent value on the
subform in the DCount Where argument by using a simpler addressing
scheme... like...
...."ConfEvent = " & Me.ConfEvent

Thanks a lot Ken. I've got lay off those 12 hour "fingerboning"
sessions... I'm losin' it!

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

You can put a textbox on the subform, and set its control source to a
"=DLookup" or "=DSum" or other domain function that uses a field in that
subform as part of the criterion expression:

=DLookup("MyField","MyTable","MyIDField=" & [IDField])

--

Ken Snell
<MS ACCESS MVP>

Main form (frmConfrences from tblConferences) ONE
Sub Form (frmScheduledEvents from tblScheduledEvents) MANY
Link = ConferenceID (Long)

The subform lists all events at a conference (A-1, B-3, D14, etc...),
and in another table (tblAttendeeWorkshops), these available vents will
be assigned to Attendees. Perhaps 50 attendees will sign up for
workshop A-1, and maybe 40 will sign up for B-3.

In my frmScheduledEvents subform I'd like to have a column that shows
how many booking there are against each workshop vs. the
WorkshopCapacity field.
As of now this is an unbound field, so Dlookup doesn't work with the
continous form.
I tried attaching a "totals" pass thru query to my subform query, (to
try to a "bound" filed, but that made the sub records "un-updateable" no
matter how I tried to link the totals
I even tried a small "field sized" sub-subform, but the subform itself
is continuous, so no deal.

Can anyone just describe how they've handled this problem in the past.
"Aircode" would be fine, or just a verbal description of the process.

Thanks in advance,
Al Camp

 

[Al Camp]

Well I'll be darned again! I thought for sure I had tried dropping the Me.

I think I'll take a little "nappy" now, assume a fetal position, and turn
the electric blanket up to 9!

Great Ken... thanks for the help.
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

You can omit all the form references if you want to use a field/control
that on the same form as the textbox whose control source uses it:

=DCount("[ConfEvent]","tblAttendeeCourses","ConfEvent =" & [ConfEvent])

--

Ken Snell
<MS ACCESS MVP>

Well... I'll be darned. I tried that before, and got the same count in
each subform record. So... I thought that was because the field was
unbound... like an entered value in an unbound field showing across all
records.

After your suggestion, I tried a DCount against the tblAttendeeCourses
table again... and it worked just fine!

=DCount("[ConfEvent]","tblAttendeeCourses","ConfEvent =
Forms!frmConferences!frmScheduledEvents.Form!ConfEvent")

By the by...is there any other way to refer to the ConfEvent value on
the subform in the DCount Where argument by using a simpler addressing
scheme... like...
...."ConfEvent = " & Me.ConfEvent

Thanks a lot Ken. I've got lay off those 12 hour "fingerboning"
sessions... I'm losin' it!

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

You can put a textbox on the subform, and set its control source to a
"=DLookup" or "=DSum" or other domain function that uses a field in that
subform as part of the criterion expression:

=DLookup("MyField","MyTable","MyIDField=" & [IDField])

--

Ken Snell
<MS ACCESS MVP>

Main form (frmConfrences from tblConferences) ONE
Sub Form (frmScheduledEvents from tblScheduledEvents) MANY
Link = ConferenceID (Long)

The subform lists all events at a conference (A-1, B-3, D14, etc...),
and in another table (tblAttendeeWorkshops), these available vents will
be assigned to Attendees. Perhaps 50 attendees will sign up for
workshop A-1, and maybe 40 will sign up for B-3.

In my frmScheduledEvents subform I'd like to have a column that shows
how many booking there are against each workshop vs. the
WorkshopCapacity field.
As of now this is an unbound field, so Dlookup doesn't work with the
continous form.
I tried attaching a "totals" pass thru query to my subform query, (to
try to a "bound" filed, but that made the sub records "un-updateable"
no matter how I tried to link the totals
I even tried a small "field sized" sub-subform, but the subform
itself is continuous, so no deal.

Can anyone just describe how they've handled this problem in the
past. "Aircode" would be fine, or just a verbal description of the
process.

Thanks in advance,
Al Camp