Help with a calculation on a form

[cox]

Howdy all,
I have a form with the following objects and sql:
3 unbound listboxes
lst1 has the following sql in the row source
SELECT Count([Tag ID]) AS recordcount FROM [pre ships] WHERE ((([pre
ships].[Inv Date]) Between (select date1 from date_ranges where
date_ranges.name = "hd") And (select date2 from date_ranges where
date_ranges2.name = "hd")));

lst2 has the following sql in the row source
SELECT Count([Tag ID]) AS recordcount FROM [hd hits] WHERE ((([hd hits].[Inv
Date]) Between (select date1 from date_ranges where date_ranges.name = "hd")
And (select date2 from date_ranges where date_ranges.name = "hd")));

lst3 has the following sql in the row source
=[lst2]/[lst1]

My issue is that lst3 does not populate with data unless I click on the lst1
and lst2 control on the form. How can I get this to work without always
clicking on the list boxes to get the 3rd lstbox, the calculation, to work?

Thanks in Advance,
Bob W.

 

[Graham Mandeno]

Hi Bob

The problem is that the value of an unbound listbox will be Null until you
select an item from the list. To "select" the first item in a listbox using
code, you can say:
lst1 = lst1.Itemdata(0)

However, I fail to see why you are using listboxes here. There can never be
more than one item in each list, according to your rowsources, so why use a
listbox at all?

 

[cox]

Graham,
I am using the list box to run the sql statement and also to do the
calculation of the values returned from lst1 and lst1. Basically I have a 2
queries that count records and displays the 2 value of the queries, then I
have a listbox that divides the value of lst2 by lst1. My issue is that
lst1 and lst2 both display the query value when running the form. lst3 does
not display a value until I click on lst1 and lst2. I want to eliminate the
steps of clicking on lst1 and lst2 to get the value to appear in lst3.

Thanks,
Bob W.

Hi Bob

The problem is that the value of an unbound listbox will be Null until you
select an item from the list. To "select" the first item in a listbox using
code, you can say:
lst1 = lst1.Itemdata(0)

However, I fail to see why you are using listboxes here. There can never be
more than one item in each list, according to your rowsources, so why use a
listbox at all?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Howdy all,
I have a form with the following objects and sql:
3 unbound listboxes
lst1 has the following sql in the row source
SELECT Count([Tag ID]) AS recordcount FROM [pre ships] WHERE ((([pre
ships].[Inv Date]) Between (select date1 from date_ranges where
date_ranges.name = "hd") And (select date2 from date_ranges where
date_ranges2.name = "hd")));

lst2 has the following sql in the row source
SELECT Count([Tag ID]) AS recordcount FROM [hd hits] WHERE ((([hd
hits].[Inv
Date]) Between (select date1 from date_ranges where date_ranges.name =
"hd")
And (select date2 from date_ranges where date_ranges.name = "hd")));

lst3 has the following sql in the row source
=[lst2]/[lst1]

My issue is that lst3 does not populate with data unless I click on the
lst1
and lst2 control on the form. How can I get this to work without always
clicking on the list boxes to get the 3rd lstbox, the calculation, to
work?

Thanks in Advance,
Bob W.

 

[Graham Mandeno]

Hi Bob

That's a very interesting technique. I have never before heard of anyone
using a listbox purely to execute a SQL statement returning a single value.

However, I can't see how you are getting a result displayed in a third
listbox. If you set the RowSource of a listbox to an expression such as
=[lst2]/[lst1]
you will get an error saying that the record source does not exist.

Are you sure your third control is not a *textbox* and its *ControlSource*
(not RowSource) is the expression you give?
In any case, as I said yesterday, you can refer to the first (in this case
the only) value in a listbox using the ItemData property:
lst1 = lst1.ItemData(0)
will set the value of the listbox to the value of the first item, thereby
effectively selecting the first item.

You can also use the ItemData property as part of a ControlSource
expression. For example, you could have a textbox txt1 with ControlSource:
=[lst1].ItemData(0)

This will always display the first value in lst1, whether or not it is
selected.

Now, if you do the same for txt2: =[lst2].Itemdata(0)

Then you can have a third textbox calculating the quotient:
txt3: =[txt2]/[txt1]

Then make lst1 and lst2 invisible, since they are only being used to execute
the SQL.

I hope I've understood you a little better now
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I am using the list box to run the sql statement and also to do the
calculation of the values returned from lst1 and lst1. Basically I have a
2
queries that count records and displays the 2 value of the queries, then I
have a listbox that divides the value of lst2 by lst1. My issue is that
lst1 and lst2 both display the query value when running the form. lst3
does
not display a value until I click on lst1 and lst2. I want to eliminate
the
steps of clicking on lst1 and lst2 to get the value to appear in lst3.

Thanks,
Bob W.

Hi Bob

The problem is that the value of an unbound listbox will be Null until
you
select an item from the list. To "select" the first item in a listbox using
code, you can say:
lst1 = lst1.Itemdata(0)

However, I fail to see why you are using listboxes here. There can never be
more than one item in each list, according to your rowsources, so why use a
listbox at all?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Howdy all,
I have a form with the following objects and sql:
3 unbound listboxes
lst1 has the following sql in the row source
SELECT Count([Tag ID]) AS recordcount FROM [pre ships] WHERE ((([pre
ships].[Inv Date]) Between (select date1 from date_ranges where
date_ranges.name = "hd") And (select date2 from date_ranges where
date_ranges2.name = "hd")));

lst2 has the following sql in the row source
SELECT Count([Tag ID]) AS recordcount FROM [hd hits] WHERE ((([hd
hits].[Inv
Date]) Between (select date1 from date_ranges where date_ranges.name =
"hd")
And (select date2 from date_ranges where date_ranges.name = "hd")));

lst3 has the following sql in the row source
=[lst2]/[lst1]

My issue is that lst3 does not populate with data unless I click on the
lst1
and lst2 control on the form. How can I get this to work without
always
clicking on the list boxes to get the 3rd lstbox, the calculation, to
work?

Thanks in Advance,
Bob W.

 

[cox]

Graham,
Your solution worked for me!!!
Thank you very much for your time,
Bob W.

Hi Bob

That's a very interesting technique. I have never before heard of anyone
using a listbox purely to execute a SQL statement returning a single value.

However, I can't see how you are getting a result displayed in a third
listbox. If you set the RowSource of a listbox to an expression such as
=[lst2]/[lst1]
you will get an error saying that the record source does not exist.

Are you sure your third control is not a *textbox* and its *ControlSource*
(not RowSource) is the expression you give?
In any case, as I said yesterday, you can refer to the first (in this case
the only) value in a listbox using the ItemData property:
lst1 = lst1.ItemData(0)
will set the value of the listbox to the value of the first item, thereby
effectively selecting the first item.

You can also use the ItemData property as part of a ControlSource
expression. For example, you could have a textbox txt1 with ControlSource:
=[lst1].ItemData(0)

This will always display the first value in lst1, whether or not it is
selected.

Now, if you do the same for txt2: =[lst2].Itemdata(0)

Then you can have a third textbox calculating the quotient:
txt3: =[txt2]/[txt1]

Then make lst1 and lst2 invisible, since they are only being used to execute
the SQL.

I hope I've understood you a little better now
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I am using the list box to run the sql statement and also to do the
calculation of the values returned from lst1 and lst1. Basically I have a
2
queries that count records and displays the 2 value of the queries, then I
have a listbox that divides the value of lst2 by lst1. My issue is that
lst1 and lst2 both display the query value when running the form. lst3
does
not display a value until I click on lst1 and lst2. I want to eliminate
the
steps of clicking on lst1 and lst2 to get the value to appear in lst3.

Thanks,
Bob W.

Hi Bob

The problem is that the value of an unbound listbox will be Null until
you
select an item from the list. To "select" the first item in a listbox using
code, you can say:
lst1 = lst1.Itemdata(0)

However, I fail to see why you are using listboxes here. There can

never
be

more than one item in each list, according to your rowsources, so why

use
a

listbox at all?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Howdy all,
I have a form with the following objects and sql:
3 unbound listboxes
lst1 has the following sql in the row source
SELECT Count([Tag ID]) AS recordcount FROM [pre ships] WHERE ((([pre
ships].[Inv Date]) Between (select date1 from date_ranges where
date_ranges.name = "hd") And (select date2 from date_ranges where
date_ranges2.name = "hd")));

lst2 has the following sql in the row source
SELECT Count([Tag ID]) AS recordcount FROM [hd hits] WHERE ((([hd
hits].[Inv
Date]) Between (select date1 from date_ranges where date_ranges.name =
"hd")
And (select date2 from date_ranges where date_ranges.name = "hd")));

lst3 has the following sql in the row source
=[lst2]/[lst1]

My issue is that lst3 does not populate with data unless I click on the
lst1
and lst2 control on the form. How can I get this to work without
always
clicking on the list boxes to get the 3rd lstbox, the calculation, to
work?

Thanks in Advance,
Bob W.