List Boxes Value is Null 1

[jaanisf]

I have a textbox tb_value and a listbox lb_value. Listbox is bound to a query, it shows the query result. Listbox has only one row, resp. one value. Now, when I make another textbox tb_another, I write in its Control Source: =[tb_value]. Everything's ok, the value of tb_value is shown also in tb_another. But, when I write in tb_another Control Source: =[lb_value], it gives Null. But in listbox there IS a query result value.

The problem is, I have many listboxes, and one textbox, which is supposed to sum all values in listboxes.

How should I continue?

 

[RickSpr]

The value of a list box control is the value of the selected row. If no row is selected, the list box's Value property returns Null.

Select an item in the list box and see if the text box becomes evaluated. If it does, the above statement is the key to your solution. You just need to figure out how to ensure that a value is selected in the list box.

(I get the impression your list box's query returns only a single value. If that's correct, you're going about this in a very awkward way. A better way would be to use a DLookup() function call within the text box's Control Source property.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[jaanisf]

Yes, the query returns a single value. At first I tried text boxes, bet they did't return any value from a query.
How should I do this with text box?
Example:
text boxes name = tbox1
queries name = query1
queries SQL view is like this:

SELECT Sum([quantity]*[price]*[multiplier]) AS Supplied
FROM table1 LEFT JOIN table2 ON table1.id = table2.name
WHERE (((table1.date)>=[forms]![firstform]![tbox2]-7 And (table1.date)<=[forms]![firstform]![tbox2]-1));

It gives a single value.

Should I write this DLookUp() function in tbox1 Control Source? How must this function look like? Or is it to be written elsewhere?
Thanks in advance!

 

[hkaing79]

Using a listbox is a clever solution.

If you'd like to use textbox, there's two approach you can take.

Option 1: Create a query with your select function. Then set your textbox = DLookup("Supplied","query1","date>=#" & tbox2 - 7 & "# AND date<=#" & tbox2 -1)

Option 2: Use DAO to create a recordset. Then, assign textbox = rs.Field("Supplied").

I prefer Option 2 because it's more flexible.

 

[hkaing79]

Sorry, I just read your first entry. I'm a bit confused.

You have many listboxes but only one textbox? The textbox is supposed to sum all the listboxes? Is it possible to just include all those values into 1 listbox and then have the textbox sum the values in that 1 listbox?

 

[jaanisf]

Actually, I made it all with listboxes. But, there I stopped, because, now I need a textbox, which sums all the values in listboxes. But the value in textbox looks like this 1178.582768.355837.45, resp, it manages listboxes values as text, not as curency.
Record Source for this textbox looks like this: =listbox1.ItemData(0) + listbox2.ItemData(0) + listbox3.ItemData(0)

Well?

 

[jaanisf]

Erm.. I just read your second reply

You see, I have many queries. I allways run all these queries and then write down results in excell to calculate. Now, I would like to make a form, which would calculate it fast with a single click
It all must look like this:

Listboxlabel1: 100$ Listboxlabel4: 50$
Listboxlabel2: 100$ Listboxlabel5: 50$
Listboxlabel3: 100$ Listboxlabel6: 50$
----------------------------------------
Textboxlabel1: 300$ Textboxlabel2: 150$

Following me?..

Every listbox takes its value from a different querry. And those two textboxes just sums listboxes in a column.

In my form, this Textboxlabel1: 300$ looks like Textboxlabel1: 100$100$100$

 

[hkaing79]

Try using Val(listbox1.itemdata(0))+...+Val(listbox3.itemdata(0)).

There might be an easier solution.

Step 1: Create a union query. "SELECT field1 FROM table1 UNION SELECT field1 FROM table2 UNION SELECT field1 FROM table3 ..."

If the field names are different then field1 AS Result in first select, field2 AS Result in following union select, field3 AS Result in following union select, ...

Step 2: Aggregate using the sum function.

There's no need for listboxes at all. Although if you wish to see each individual value, then you can set the rowsource property of the listbox to your union query.

 

[jaanisf]

Thanks, this Val(listbox1.itemdata(0))+...+Val(listbox3.itemdata(0)) helped.