Populate Forms textbox fields from query

[jaanisf]

I have a query like this:
SELECT Sum([daudz]*[cena]*[reizinatajs]) AS Summa
FROM tpnos LEFT JOIN tap ON tpnos.npk = tap.nos
WHERE (((tap.dat)>=[apd1] And (tap.dat)<=[apd2]));
It runs nice, asking me apd1 and apd1, which is two dates.

All I want is, to create a form, where is a textbox with a date, let's call it tb_date, and a textbox, let's call it tb_sum, which uses the query. Dates apd1 and apd2 would be tb_date - 7 and tb_date - 1.

How can I make this? I put this SQL statement everywhere, but all I got is errors.

 

[Crowley16]

eh, so you want only the textbox tb_sum to show the result of the query?

firstly, the sql statement would need to be changed a little...
[apd1] => #" & tb_date & "# (same for apd2)

then you need to put the sql statement into the recordsource property of tb_sum

--------------------
Procrastinate Now!

 

[jaanisf]

Erm... well,
at first.. what is a recordsource property of tb_sum? I find only Control Source property. And, this SQL statement is taken from query's SQL view. It works fine. Query's name is bq_ap.
As far as I understand, I need to put this statement in a tb_sum Control Source? But it gives me an error anyway. Can you, please, write for me this statement. How does it look like
Thanks for helping!

 

[Crowley16]

what is the exact error message you are getting?

--------------------
Procrastinate Now!

 

[jaanisf]

It's sintax error.
And when I put the statement like this:

=(SELECT Sum([daudz]*[cena]*[reizinatajs]) AS Summa FROM tpnos LEFT JOIN tap ON tpnos.npk = tap.nos WHERE (((tap.dat)>=#" & tb_date & "# And (tap.dat)<=#" & tb_date & "#)))

it gives no error, but it also gives no result. Textbox tb_sum displays #Name? but I need it to display a sum.

 

[Crowley16]

You say that you already have this saved in a query, and that query works?

well, why not just bound this form to the results of that query then?

put the query name in the control source, and in the query, refer to the value of tb_date

you can do this by:

tap.dat >= forms!frmName!tbxName AND ...

--------------------
Procrastinate Now!

 

[jaanisf]

Yes, that is what I wanted. Well, I tried this with listboxes and it works nice, but not with textboxes. I must hold on to listboxes then, it seems..
Thanks for help

 

[jaanisf]

Hm.. another problem. It works nice with a default date set in a textbox tb_date. But when I change the date, listbox lb_sum stays the same. How can I refresh it?

 

[Crowley16]

lb_sum.requery...

in the onUpdate/OnLostFocus (or something) event of tb_date...



--------------------
Procrastinate Now!

 

[jaanisf]

I managed with the requery. No need to answer, thanks

 

[jaanisf]

Well, I have another problem with this.
After I made several listboxes that are bound to queries, I made one, that must sum the values of all listboxes. But it doesn't work. When I write in lb_sumall Control Source = [tb_date], it works correctly. But when I write there, form example, =[lb_sum], it doesn't show any value. How could I manage this?