Displaying Subform Totals in a Main Form in Microsoft Access but using master detail

[sireesha]

In the master form sale_id. depending on the sale_id collection records changed(in subform)(child form). 1 record,2 records or 0 records etc., when changing sale_id.



so I want, In the subform i have a field collection_amt. i want to sum collection amt and shown in main form.

i tried but whole collection amt is displayed. but i need only if saleid=8 collection amts like 334433,555,533 i need these sum only.
those sum is shown in mainform,
how
please help me

[June7]

The subform is in Continuous view? Expression in textbox: Sum(IIf(saleid=8,collection_amt,0))

[sireesha]

The subform is in Continuous view? Expression in textbox: Sum(IIf(saleid=8,collection_amt,0))

The subform is in datasheet view. when changing saleid corresponding collection_amt s total displayed in mainform's textbox. how?

Please help me.

i have list of saleid is in listbox when select from listbox the sale_id is shown in text box. Using this sale_id textbox collection records shown in subform and i want sum of the collection amts.

Please reply as early as possible

[June7]

Not going to work with subform in Datasheet view. Use Single or Continuous view so the form footer section is available. Put textbox in footer with the Sum calculation. Then textbox on main form can refer to the subform textbox.

[sireesha]

yes i had seen that method. but the subform shown in datasheet view only. so i write a code for that. its working.

i have one more problem. how to count records in datasheet view depending on saleid.

query="select * from tablename where saleid=" & saleid
i set this query to recordset.
recordset.recordcount returns 1

but i have more records. i need to count the records depending on saleid.

please help me.

[msoffice.vidyarthi]

To get the count of records, modify the query as:
query="select count(*) from tablename where saleid=" & saleid

[June7]

The subform can be Single or Continuous view. Controls in Continuous view can be arranged to appear as Datasheet. If the controls are grouped in a Layout, select all and right click Layout>Remove. Then the controls can be moved and resized as desired.