SomeHelpWithSubform

[alawagi71 via AccessMonster.com]

Hey!

I have form with sub form each row in the main form belong to many rows in
the child form
i want to know how many rows in the subform belong to each row in the main
form?

thanks alot !

 

[alawagi71 via AccessMonster.com]

Alawagi71 -

There are a couple ways to do this, depending on your design and needs. You
can always put the count of records in the header or footer of the subform
(though it is visible in the record navigators on the subform also). This
will change as the main form record changes (and the subform records change).


You can also do a DCOUNT from the main form to count the matching records
from the subform's recordsource. Just put a field on the main form to hold
this, and when a record is selected for the main form, update this field,
something like this:
Me.txtRecCount = DCOUNT("[KeyFieldName]", "[TableName]", "[KeyFieldName] = "
& Me.ControlWithKeyFieldName)

Hey!

[quoted text clipped - 4 lines]

thanks alot !

Hey Daryl

i want to know how to do this because it the first time for me to work with
Access so it's difficult to me

my main form Workshops and the subform is Participants?

thanks!

 

[Daryl S]

Alawagi71 -

To make this work, put a text box on your main form, and rename this text
box to be txtRecCount by putting that in the Name property of the text box.
Now you have a control (a text box) named txtRecCount on your form. That
means anywhere in the code for the form Me.txtRecCount can be used to
reference that text box.

Now to put the record count into that field, you need to know the name of
the table that your subform is based on. Change the TableName in the
statement below to be your table name. Also change the KeyFieldName to be
the name of a field in that table that is always populated - we usually use
the field that is the key to the table, but for this purpose I would use the
name of the field that you used to join the form to the subform. Finally,
change the Me.ControlWithKeyFieldName to be the control on the main form that
contains this field.

Me.txtRecCount = DCOUNT("[KeyFieldName]", "[TableName]", "[KeyFieldName] = "
& Me.ControlWithKeyFieldName)

If you want more help, you have to provide more details - what are the table
names and the main field names for the tables, and what are the names of the
controls on your form where the key value is and where you want the record
count to go.

--
Daryl S

Alawagi71 -

There are a couple ways to do this, depending on your design and needs. You
can always put the count of records in the header or footer of the subform
(though it is visible in the record navigators on the subform also). This
will change as the main form record changes (and the subform records change).


You can also do a DCOUNT from the main form to count the matching records
from the subform's recordsource. Just put a field on the main form to hold
this, and when a record is selected for the main form, update this field,
something like this:
Me.txtRecCount = DCOUNT("[KeyFieldName]", "[TableName]", "[KeyFieldName] = "
& Me.ControlWithKeyFieldName)

Hey!

[quoted text clipped - 4 lines]

thanks alot !

Hey Daryl

i want to know how to do this because it the first time for me to work with
Access so it's difficult to me

my main form Workshops and the subform is Participants?

thanks!

 

[alawagi71 via AccessMonster.com]

Hey Friend!

i have table called Workshops which contains Workshops_ID as primary Key and
Workshops Name my child table is Participants which contains Participants_ID
and Participant Name an Gender.
each workshop have many participant in it.i have main form based on Workshops
and subform base on Participants.What i want to know is how many Participants
in each workshop.Also if you can i want to know how many males and female in
each one!
thank you so much

Alawagi71 -

To make this work, put a text box on your main form, and rename this text
box to be txtRecCount by putting that in the Name property of the text box.
Now you have a control (a text box) named txtRecCount on your form. That
means anywhere in the code for the form Me.txtRecCount can be used to
reference that text box.

Now to put the record count into that field, you need to know the name of
the table that your subform is based on. Change the TableName in the
statement below to be your table name. Also change the KeyFieldName to be
the name of a field in that table that is always populated - we usually use
the field that is the key to the table, but for this purpose I would use the
name of the field that you used to join the form to the subform. Finally,
change the Me.ControlWithKeyFieldName to be the control on the main form that
contains this field.

Me.txtRecCount = DCOUNT("[KeyFieldName]", "[TableName]", "[KeyFieldName] = "
& Me.ControlWithKeyFieldName)

If you want more help, you have to provide more details - what are the table
names and the main field names for the tables, and what are the names of the
controls on your form where the key value is and where you want the record
count to go.
[quoted text clipped - 24 lines]

thanks!

 

[Daryl S]

Alawagi71 -

On your form, if you want three totals, then you need three unbound text
boxes. I have called them txtParticipants, txtMales, and txtFemales, but you
can call them anything - you will need to adjust the control names below. I
assume you have the field [Workshop_ID] in the Participants table, as that
would tell us which workshop they are in. If not, then we need to know how
the Workshops and Participants tables are linked. If so, then these should
work, assuming [Workshops_ID] is an autonumber (or least an integer) field,
and also that [Gender] is a text field with the string 'Male' or 'Female' in
them.

Me.txtParticipants = DCOUNT("[Participants_ID]", "[Participants]",
"[Workshops_ID] = "
& Me.WorkshopID)

Me.txtMales = DCOUNT("[Participants_ID]", "[Participants]", "[Workshops_ID]
= "
& Me.WorkshopID " AND [Gender] = 'Male'")

Me.txtFemales = DCOUNT("[Participants_ID]", "[Participants]",
"[Workshops_ID] = "
& Me.WorkshopID " AND [Gender] = 'Female'")

Let us know...

--
Daryl S

Hey Friend!

i have table called Workshops which contains Workshops_ID as primary Key and
Workshops Name my child table is Participants which contains Participants_ID
and Participant Name an Gender.
each workshop have many participant in it.i have main form based on Workshops
and subform base on Participants.What i want to know is how many Participants
in each workshop.Also if you can i want to know how many males and female in
each one!
thank you so much

Alawagi71 -

To make this work, put a text box on your main form, and rename this text
box to be txtRecCount by putting that in the Name property of the text box.
Now you have a control (a text box) named txtRecCount on your form. That
means anywhere in the code for the form Me.txtRecCount can be used to
reference that text box.

Now to put the record count into that field, you need to know the name of
the table that your subform is based on. Change the TableName in the
statement below to be your table name. Also change the KeyFieldName to be
the name of a field in that table that is always populated - we usually use
the field that is the key to the table, but for this purpose I would use the
name of the field that you used to join the form to the subform. Finally,
change the Me.ControlWithKeyFieldName to be the control on the main form that
contains this field.

Me.txtRecCount = DCOUNT("[KeyFieldName]", "[TableName]", "[KeyFieldName] = "
& Me.ControlWithKeyFieldName)

If you want more help, you have to provide more details - what are the table
names and the main field names for the tables, and what are the names of the
controls on your form where the key value is and where you want the record
count to go.

Alawagi71 -

[quoted text clipped - 24 lines]

thanks!

--



.

 

[alawagi71 via AccessMonster.com]

Hi

every thing as you guessed it has a workshop_id and Participant_ID and i did
what you said but it give me a question mark?
i don't know why
Please give me a way to send you my database!

Alawagi71 -

On your form, if you want three totals, then you need three unbound text
boxes. I have called them txtParticipants, txtMales, and txtFemales, but you
can call them anything - you will need to adjust the control names below. I
assume you have the field [Workshop_ID] in the Participants table, as that
would tell us which workshop they are in. If not, then we need to know how
the Workshops and Participants tables are linked. If so, then these should
work, assuming [Workshops_ID] is an autonumber (or least an integer) field,
and also that [Gender] is a text field with the string 'Male' or 'Female' in
them.

Me.txtParticipants = DCOUNT("[Participants_ID]", "[Participants]",
"[Workshops_ID] = "
& Me.WorkshopID)

Me.txtMales = DCOUNT("[Participants_ID]", "[Participants]", "[Workshops_ID]
= "
& Me.WorkshopID " AND [Gender] = 'Male'")

Me.txtFemales = DCOUNT("[Participants_ID]", "[Participants]",
"[Workshops_ID] = "
& Me.WorkshopID " AND [Gender] = 'Female'")

Let us know...

Hey Friend!

[quoted text clipped - 36 lines]

 

[alawagi71 via AccessMonster.com]

Where should i put the three unbound text in the main form or in the subform?
if in the main form i did but it didn't work?

Alawagi71 -

On your form, if you want three totals, then you need three unbound text
boxes. I have called them txtParticipants, txtMales, and txtFemales, but you
can call them anything - you will need to adjust the control names below. I
assume you have the field [Workshop_ID] in the Participants table, as that
would tell us which workshop they are in. If not, then we need to know how
the Workshops and Participants tables are linked. If so, then these should
work, assuming [Workshops_ID] is an autonumber (or least an integer) field,
and also that [Gender] is a text field with the string 'Male' or 'Female' in
them.

Me.txtParticipants = DCOUNT("[Participants_ID]", "[Participants]",
"[Workshops_ID] = "
& Me.WorkshopID)

Me.txtMales = DCOUNT("[Participants_ID]", "[Participants]", "[Workshops_ID]
= "
& Me.WorkshopID " AND [Gender] = 'Male'")

Me.txtFemales = DCOUNT("[Participants_ID]", "[Participants]",
"[Workshops_ID] = "
& Me.WorkshopID " AND [Gender] = 'Female'")

Let us know...

Hey Friend!

[quoted text clipped - 36 lines]