Re: Expression Builder basic question on syntax / Or / how to incorporate a SQL query into a textbox control?
- From: Minton M <jamesbeswick@xxxxxxxxx>
- Date: Fri, 28 Dec 2007 10:44:13 -0800 (PST)
On Dec 28, 9:56 am, raylopez99 <raylope...@xxxxxxxxx> wrote:
I would like to add a textbox on my control that is not linked to a
database (like the other textboxes are, which are linked to fields in
the database), that takes the sum of all the textboxes in the form.
How to do this using "Expression Builder"? (Or otherwise?). I'm
having problems with the syntax, since I keep getting the "#Name?"
runtime error on the textbox. **See below, I also would like to know
using Visual Basic if possible.
Concrete example:
Field #1 (linked to a database): Quantity
Field #2 (" ") : Price
Textbox #3 (this I want to compute from fields #1 and #2, in a textbox
control on my Access 2003 form): = Quantity * Price
Any way appreciated. What I did was run a SQL query ("SELECT
Sum(Subform1.Quantity*Subform1.PricePerShare) AS SumTotal
FROM AccountStocks INNER JOIN Subform1 ON
AccountStocks.AccountStockID=StkTransactions.Account_StockID
GROUP BY AccountStocks.AccountStockID;) <--two tables, linked by a
key, the query being called "Query1" having alias SumTotal
This SQL query works (when run seperately), giving a sum for each
record, and just for learning purposes I'd like to incorporate this
query using the "Expression builder" for the textbox #3.
However, I'll take any easy way of doing the above.
Thank you
RL
PS--I tried various things, and nothing seems to work. For example I
took the name of the Textbox, Textbox3, and set it equal to ("Query1!
SumTotal"). Then....(various other things tried deleted)...
UPDATE: Ok, surfing the net before I hit enter (sorry I'm on a dialup
modem right now, so surfing is painful) I see that a lot of people
don't use the Expression Builder, instead they like to use Visual
Basic in an [event procedure] That's fine, I'd like to know how to
incorporate the SQL query above...do I use it in an event procedure
(and which one? There are so many, in the past for other stuff I use
"OnGotFocus" but I doubt that's the best for this query since the user
would have to click on the box to get an answer).
Also, do I set the SQL query as a "Control Source" under the
Properties tab of the textbox #3? (Just tried that, using an equal
sign in front, and without it, and it doesn't work, still gives the
#Name? scope problem again).
Any ideas? This question is very basic so please don't think too
hard...(I think).
RL
Hi,
Here's a cheap and cheerful way to get an SQL result into a textbox.
Put the following code in a module:
Public Function GetResult(sSQL As String) As Variant
On Error GoTo err
Dim rs As Recordset
InitDB
Set rs = db.OpenRecordset(sSQL)
If Not rs.EOF Then
GetResult = rs(0)
rs.Close
End If
Set rs = Nothing
Exit Function
err:
Debug.Print "GetResult error: SQL=" & sSQL & ". Error: " & Error$
End Function
And then you can either set the control source of the textbox to:
=GetResult("select count(*) from SomeTable")
or use VBA to call the function and set the textbox's value to the
result.
Hope this helps,
James
.
- Follow-Ups:
- References:
- Prev by Date: Re: Sending E-Mail
- Next by Date: Re: Subform Problem
- Previous by thread: Expression Builder basic question on syntax / Or / how to incorporate a SQL query into a textbox control?
- Next by thread: Re: Expression Builder basic question on syntax / Or / how to incorporate a SQL query into a textbox control?
- Index(es):
Relevant Pages
|
