SQL control source 1

[JaredTaylor74]

I have a form that i need to show several different values, some are calculations, others are just stored data. because of this i can not use a query as my control source for my form so i am trying to use sql for each of my text boxes.
i have tried several different approaches, here is an example of a couple of the attempts on one of my text boxes.

Dim SaveBalance As String    
SaveBalance = "Saving"
    
sqlSavingBalance = "SELECT Sum(Amount) FROM tblTransactionAccountBalance WHERE ((([Account Type])=""" & SaveBalance & """));"
Me.txtSavingBalance.ControlSource = sqlSavingBalance

Dim SaveBalance As String    
SaveBalance = "Saving"
sqlSavingBalance = "SELECT (Sum(IIf([Account Type]=""" & SaveBalance & """,[Amount]))) FROM tblTransactionAccountBalance;"
Me.txtSavingBalance.ControlSource = sqlSavingBalance

with both of these when i step through it, the value of sqlSavingBalance is null. and my text box shows #NAME?

i'm sure once i get one to work, the others will be cake, but i can't seem to pinpoint my problem.

can someone help me out with this?
(using office 2000, windows xp)

Thank you,
Jared

 

[Frink]

Hallo,
You probably want to use the Domain function, DSum
Me!txtSavingBalance.ControlSource:
=DSum("Amount","tblTransactionAccountBalance","[Account Type]=""Saving""")

Alternatively you could put the calculation in the oncurrent (or onopen) event of the form.

- Frink

 

[JaredTaylor74]

hey Frink, thanks for the quick reply

i tried your suggestion, but it is still pulling a null value. i know there is data, because i can put my sql in a query and it works just fine.

and i have my calculations in the on load event. which is bettter? the on load, on current, or the on open?

Thanks,
Jared

 

[Crowley16]

eh, can I suggest that you use single quotes for any quotes that's inside your sql statement...

e.g. "select ...accType = '" & blah & "';"

Procrastinate Now!

 

[JaredTaylor74]

Crowley, i tried that too... still gives an error.

i tried to simplify it a little...

i tried to pull a field from a table with only one record,

Me.txtAmountAF.ControlSource = "select amountbalance from tbltransactiondata;"
[

even this one gives me a null value. is there a reference i need to have checked?

 

[Frink]

Hallo,

I was never really sure about the OnOpen/OnLoad question.
The sequence of Events for a bound form is OnOpen, OnLoad, OnCurrent, then you get the OnCurrent event every time you change the displayed record, whether you use the Navigation Buttons, change the RecordSource or use GoToRecord. Sometimes you get the OnCurrent more than once though.
You do not get the OnCurrent event if you change the recordsource to something which doesn't return any rows (I don't think).

You say that the SQL works when you put it in a query. Once you have a working query, switch to SQL View and copy and paste the important bits into the DSum statement.

- Frink

 

[JaredTaylor74]

thank you Frink. i ended up just creating a recordset and using that... a little more code than i wanted, but it works. but your information about the events is greatly appreciated... i always assumed onload came before onopen..

Thank you
Jared