Update DSum field on main form when subform is changed 2

[Stevehewitt]

Hello,

Another newbie question. I have tried looking this up in Access help and TT FAQ/Search but no luck as yet.

I need to get the DSum field in the main form to update/refresh every time something changes in the Subform. Any idea how to do this? At the moment I am having to go to:
"Record - Refresh"
Everytime the subform is changed - which isn't ideal for end users.

Thanks in advance,


Steve.

 

[wemeier]

In the AfterUpdate event of the subform try:

Me.Parent.dsumfieldname.Requery

where, of course, dsumfieldname is the name of the field containing your DSum expression.

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[Stevehewitt]

Hey,

Thanks for the prompt reply. I've tried it but it doesn't seem to work!!! I put the VBA code in the AfterUpdate property of the subform. The code is this:

Me.Parent.gptotal.Requery

Maybe its because I haven't said what the main form is called?

Any more help?

Thanks,


Steve.

 

[wemeier]

Me.Parent refers to the form that contains the subform. You don't need to know what the parent form's name is.

Also, make sure that your DSum statement in your main form is working on the same scope as your subform. In other words, if your subform is linked to your form by a specific field on both forms, make sure your DSum statement limits the scope of the DSum to the value of the field.

For example, assume you have a main form that shows a customer invoice from the Invoices table. It has a subform showing the details of the invoice (what quantities of what items were purchased) from the InvoiceDetails table. The subform is linked to the main form by the invoice number. You want to put a DSum field on the main invoice showing the total number of items ordered.

The LinkChildFields of the subform control should be InvoiceDetails.InvoiceNumber. The LinkParentFields of the subform control should be Invoices.InvoiceNumber.

The DSum statement should be:

DSum("ItemsOrdered","InvoiceDetails","InvoiceNumber = " & Me.InvoiceNumber)

If you add items (or change quantities) on the subform, in the AfterUpdate event of the subform you would then use your Me.Parent.gptotal.Requery.

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[Stevehewitt]

Hey,

Thanks for your help. I have been playing with it. I think I nearly have it working. The only problem is that if I delete a record then it still doesn't update.

This isnt' major though, and I'm more than grateful for your help.

Can I just verify one thing please:

DSum("ItemsOrdered","InvoiceDetails","InvoiceNumber = "& Me.InvoiceNumber)

OK is the following correct:

ItemsOrdered = Name of child field you wish to add up
InvoiceDetails = Name of subform data source (table name) or is it the name of the form? I'm guessing the name of the table.
InvoiceNumber = The primary link/relationship between the two records/tables

Thanks,

Steve.

 

[wemeier]

You're right. InvoiceDetails is the name of the table that contains the details of an invoice. It would be the RecordSource of the subform.

Cruising the Information Superhighway
_{(your mileage may vary)}