If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SomeHelpWithSubform
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 ! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#2
|
|||
|
|||
SomeHelpWithSubform
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) -- Daryl S "alawagi71 via AccessMonster.com" wrote: 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 ! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
#3
|
|||
|
|||
SomeHelpWithSubform
Daryl S wrote:
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! -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
SomeHelpWithSubform
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 via AccessMonster.com" wrote: Daryl S wrote: 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! -- Message posted via http://www.accessmonster.com . |
#5
|
|||
|
|||
SomeHelpWithSubform
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 Daryl S wrote: 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! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#6
|
|||
|
|||
SomeHelpWithSubform
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 "alawagi71 via AccessMonster.com" wrote: 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 Daryl S wrote: 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! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
#7
|
|||
|
|||
SomeHelpWithSubform
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! Daryl S wrote: 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] thanks! -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
SomeHelpWithSubform
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? Daryl S wrote: 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] thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
Thread Tools | |
Display Modes | |
|
|