Query Result to Text Box

[gsgriffin]

I imagine this is as simple a question as it gets, but since I've never had any formal training, I don't know how to do this.

I have a query called "AmountDue". It has only one result in a field called "Total". The query is designed to sum up all values involved in a subform. When a value is changed in this subform, I would like to have the associated code place result of query on the master form (called Contacts) and into the textbox called "AmountDue".

To easy?

Thanks,

GG

 

[jrbarnett]

Hi,

Just set the textbox to

=Sum(Subformname.Form!Fieldname)

rather than execute the query each time.

John

 

[gsgriffin]

The subform values and the textbox on the master form are not in a one-to-one relationship.

The subform is a datasheet that displays several values. When one of these values is changed, I would like to have the sum of all the values in subform presented on the master form. I made a query called AmountDue that adds all the values I desire to total from the subform. This query has a single result in a single field called "Total". I want this result to be displayed on the master form (called Contacts).

Does that make sense?

Thanks for helping!

GG

 

[jrbarnett]

Hi,

It makes perfect sense. I use the technique I describe above many times in this situation and it works perfectly.

You could use:

=DLookup("Total", "QueryName&quot

as an alternative which will execute your query, but my method is faster.

John

 

[BSman]

You need to set the total textbox equal to the value of the query, for example:
me.txtTotal = DSUM("[Total]","[MySummingQuery]&quot

This code belongs in the OnCurrent property of the form. It also needs to be executed when a change is made on the subform. The simplest way to do this is to use the AfterUpdate property of the master form to execute the same code. Perhaps requery might also work:

me.txtTotal.requery

If you have a place on the subform when AfterUpdate would be appropriate to force a recalculation you have to refer to the master form as Forms!MyMasterForm!txtTotal.

 

[gsgriffin]

I think I see what your saying, but where does this function reside? In the default value for the text box? Do I need to have code that put this sum function into the value of the textbox.

I believe you that the function works, I just don't know how to apply this to the text box.

Thanks again!

GG

 

[BSman]

You could either put the function (DSum()) as the source for the text box, or include it in VBA code as I described. If you include it in the data source of the text box, then you must force it to be recalculated when data is changed (on the subform) with the me.txtTotal.Requery VBA code that you associate with the appropriate property, as I described.