Return Query result to textbox

  • Thread starter Thread starter Question Boy
  • Start date Start date
Q

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
 
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.
 
It will work with the correct syntax:
=DSum("inv_item_qty * Inv_Item_unitPrice", "tbl_Invoice_Items",
"Inv_items_id = 1")
 
....it was off the top of my balding head...
Klatuu said:
It will work with the correct syntax:
=DSum("inv_item_qty * Inv_Item_unitPrice", "tbl_Invoice_Items",
"Inv_items_id = 1")
Click to expand...
 
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
 
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.
 
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
 
Very strange. As I said in my last post it worked for me.
Try using vbNullString instead of ""
 
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.
 
I really don't know what to tell you. As I said, the code I posted works as
it should for me.
 
Back
Top