Reading value of listbox

[louishong]

I have a list box that is populated by SQL. I then have nother list
box that combines the value from the first listbox plus a SQL
statement. It's something like this:

select rate + forms.subform.test.list32 from testtable

I know you can't pull a value from the listbox unless you click on it
(or it reads null) but is there a way to do it through VB or a macro
command? TIA.

 

[Douglas J Steele]

What exactly are you looking for?

Are you asking to add the selected item from the listbox? As long as you
haven't got Multiselect enabled, you can simply refer to the listbox
control. How you're trying to do it is wrong, though. Take a look at
http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" for an
overview of how you refer to controls on forms and subforms. (Note that
where it says Subform1 or Subform2, that refers to the name of the subform
control on the form that holds the subform. That may or may not be the same
name as the form that's being used as the subform: it depends on how you
created the subform whether they are the same name)

 

[louishong]

Let me be clearer and please don't look at the syntax of referencing
the value of a listbox. I wrote it quickly to give an idea of what i'm
trying to do and for purpose it's quite clear.

One list box pulls the rate for a given record by SQL.
Second box pulls another value based on SQL then addes the value of the
first list box.

So, if listbox 1 rate = 2 and the second listbox returns 3 from the SQL
it would add the value 2 from the first listbox to get the final answer
5.

Access doesn't read the value of a listbox by just referencing it. If
I click on the first listbox then refresh the form the second listbox
works perfectly fine. If i don't click on the first box I get a null
in the second list box, not 5 even though the form is refreshed.

Is there a way to code it in VB or does one of the canned macro command
enable reading the value of the first listbox? Thanks.

 

[Douglas J. Steele]

I'm having a problem understanding what you're doing.

Is there any connection between the two list boxes? Does the value in list
box 2 depend on what you selected in list box 1? Is there code for any of
the events for the first list box that causes the behaviour to be different?

 

[louishong]

Let me try again.

1) Create a form
2) put two list boxes
3) put a simple sql to select a numeric field from any table
4) in the second list box write a sql that will pull a numeric value
from another field + add the value you got in the first list box.

how do you do this without getting a null in the second list box?

 

[Douglas J. Steele]

Still not enough of an explanation.

A list box has multiple values in it, but you're saying "add the value you
got in the first list box". Are you selecting a value from the first list
box?

List boxes need to have a row source associated with them that determines
what data is in them. What's the row source for your two list boxes?

Is the form bound or unbound? If it's bound, what's the recordsource for it?

You say "put a simple sql to select a numeric field from any table". What
does that mean? You can't just use SQL statements in forms: you have to open
a recordset and read the value from it.

 

[louishong]

let me make it even simpler.

same as i stated in my previous email but add the following

create a table with a single record. two columns. column A has a
value 2 column B has a value 3.

in the first listbox you enter select A from tbl_test. you'll get 2 to
show on the listbox.
in the seond enter select B + (reference column A) from tbl_test.

~don't worry about that list box can return multiple records.
~they don't have to be bounded, it doesn't matter.
~we don't need the records source. we just want to be able to read the
displayed value in listbox 1.

we don't want to make it more complicated than it needs to be. this is
a micky mouse scenario that should be easy to understand.

so, going back to my original question, how do we reference a value
that is being displyaed in listbox 1 to listbox 2 as part of the
aggregate calculation in listbox 2?

is there anyone else out there? i've seen similar posts on google but
the answers were vague. thanks.

 

[louishong]

For those that are following silently I figured it out. I can't
believe it's this simple.

enter:
[form].[formname].[listbox name].value in your SQL.

In VB I know .value is required but I didn't think it would work in
SQL. Duh! I can't believe I've been spinning my wheel on this.

 

[louishong]

it looks like i spoke too soon. the box i was referencing was already
clicked with my mouse so it's returned the selected the value. still
in square one. if anyone knows a way around this problem please share.
thanks.

 

[louishong]

For those that are following silently I used the long inefficient way.
I just can't seem to get my original idea to work. Here's the SQL for
the long way:

SELECT Count(*) + (SELECT Sum(lh_Qry_ProjectActualForecast.[FTE Count])
AS [SumOfFTE Count] FROM lh_Qry_ProjectActualForecast WHERE
(((lh_Qry_ProjectActualForecast.[Resource Type])<>2) And
((lh_Qry_ProjectActualForecast.[Project Type])=1 Or
(lh_Qry_ProjectActualForecast.[Project Type]) Is Null) And
((lh_Qry_ProjectActualForecast.FiscalYear)=Forms!Test!FisYr) And
((lh_Qry_ProjectActualForecast.StartDate)<=Forms!Test!WeekOf) And
((lh_Qry_ProjectActualForecast.ProjectName)=Forms!Test!ProjName))) FROM
(SELECT DISTINCT ([lh_Qry_ProjectActualForecast].[ResourceID]) FROM
lh_Qry_ProjectActualForecast LEFT JOIN ProjectResource ON
lh_Qry_ProjectActualForecast.ResourceID = ProjectResource.[Resource ID]
WHERE (((lh_Qry_ProjectActualForecast.[Resource Type])<>2) AND
((lh_Qry_ProjectActualForecast.[Project Type])=1 Or
(lh_Qry_ProjectActualForecast.[Project Type]) Is Null) AND
((lh_Qry_ProjectActualForecast.FiscalYear)=[Forms]![Test]![FisYr]) AND
((lh_Qry_ProjectActualForecast.ProjectName)=[Forms]![Test]![ProjName])
AND ((ProjectResource.[End Date])<[Forms]![Test]![WeekOf])))