Return Query result to textbox

[Question Boy]

I have the following SQL from a query

SELECT Sum(Inv_Item_Qty*Inv_Item_UnitPrice) AS SubTotal
FROM tbl_Invoice_Items
WHERE Inv_Items_Id=1;

How can I, using vba, execute the SQl and return the value of the SubTotal
field value to a textbox (Text1) on my form.

QB

 

[dch3]

Use the DSum() function as in

=DSum("inv_item_qty"*"Inv_Item_unitPrice", "tbl_Invoice_Items",
"Inv_items_id = 1")

May have to play with it as I've never multiplied two fields in a DSUM()
before. Worst case scenario, you could save the query as a query and a do a
DLookup() on the query as in

=DLookUp("SubTotal",myQueryName)

I *wouldn't* recommend it though as its technically executing a query on a
query. Play with the DSUM() for starters.

 

[Klatuu]

It will work with the correct syntax:
=DSum("inv_item_qty * Inv_Item_unitPrice", "tbl_Invoice_Items",
"Inv_items_id = 1")

 

[dch3]

....it was off the top of my balding head...

It will work with the correct syntax:
=DSum("inv_item_qty * Inv_Item_unitPrice", "tbl_Invoice_Items",
"Inv_items_id = 1")

 

[Klatuu]

Yeah, I know about that

 

[Question Boy]

Your suggestion works like a charm. Now I have a new question.

I am controlling the textbox using vba ...ControlSource = "...."

How can I specify a blank control source (unbound)?

I tried

..ControlSource = ""
..ControlSource = Empty
..ControlSource = Null

Nothing seems to work???

QB

 

[Klatuu]

If the control is on the current form, then
Me.ControlName.ControlSource = ""

Should work. I just tried it. I usually use
Me.ControlName.ControlSource = vbNullString

But, they are basically the same and I tried it both ways.

 

[Question Boy]

For instance

Me.tbl_Invoice_Items_subform.Form.sSubTotal.ControlSource = "=Dlookup(....)"

works fine to set the ControlSource

but

Me.tbl_Invoice_Items_subform.Form.sSubTotal.ControlSource = ""

Does not work when I wish to unbound my control? It returns

Run-time error '2455'
You entered an expression that has an invalid reference to the property
From/Report

I am lost! It works to set it but then tells me it is wrong to unset it?!
What is going on?

QB

 

[Question Boy]

In this case it is on the subform. Does that make a difference?

 

[Klatuu]

Very strange. As I said in my last post it worked for me.
Try using vbNullString instead of ""

 

[Question Boy]

I did, it returns the same error?!

Very strange. As I said in my last post it worked for me.
Try using vbNullString instead of ""

 

[dch3]

Try using bangs as in
Me!tbl_Invoice_Items_subform.Form!sSubTotal.ControlSource =

I've run into the same error (Access 2003) several times (for no apparent
reason) and pulled out what was left of my hair over it. I think that my
solution was to use an object variable set to the parent form.

 

[Klatuu]

I really don't know what to tell you. As I said, the code I posted works as
it should for me.