SUM query and use of returned value

[MikeLeBen]

mySQL = "SELECT SUM([PrezzoDettaglio]*[Quantita]) " & _
                "FROM Ordini_Prodotti " & _
                "WHERE IDOrdine =" & Me.IDOrdine.Value

I am trying to use a button in a form to run this and display the returned value in a text box connected to the tblOrders.Totals field (that is, the value must be stored in the table AND displayed on the form at the same time, while also possibly allowing the user to modify it manually).

I know I can't use the .rowsource propery for text boxes, but I don't have any clues about any other possible ways to do this.

 

[vbaInet]

You can use the DSum() function in the Control Source of the textbox:

http://www.techonthenet.com/access/functions/domain/dsum.php

By the way, I think you meant Control Source not Row Source. Unless you're referring to a Combo box or List box?

 

[MikeLeBen]

Beautiful, exactly what I was looking for. Thank you very much
Oh and never mind what I said about the .RowSource property.. I was just wondering what the equivalent - I used it for similar purposes in list and combo boxes - for text boxes might be in VB so to use it with my button.

(For any newbie like me who might be interested in the code read below)

Private Sub cmdCalcolaImporto_Click()

Dim myTotal As Currency

myTotal = DSum("[PrezzoDettaglio]*[Quantita]", "Ordini_Prodotti", _
          "[IDOrdine] =" & Me.IDOrdine.Value)

Me.txtImporto = myTotal
Me.txtImporto.Requery

End Sub

 

[vbaInet]

You're welcome.

Just to mention that you can actually put this directly in the Control Source property of the textbox in this format:

= DSum("[PrezzoDettaglio]*[Quantita]", "Ordini_Prodotti", "[IDOrdine] = " & [IDOrdine])

 

[MikeLeBen]

Yes, I'd gotten that from your first reply, though I'd rather use it in the click event: often the value differs by a great amount and in those cases it's better not to populate the field automatically.

 

[vbaInet]

Alrighty!!!