Help with a calculation on a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cox - 18 Apr 2005 01:28 GMT

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.

Reply to this Message

Graham Mandeno - 18 Apr 2005 02:44 GMT

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?

Signature

Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

> Howdy all,
> I have a form with the following objects and sql:
[quoted text clipped - 23 lines]
> Thanks in Advance,
> Bob W.

Reply to this Message

cox - 18 Apr 2005 13:01 GMT

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
>
[quoted text clipped - 33 lines]
> > Thanks in Advance,
> > Bob W.

Reply to this Message

Graham Mandeno - 19 Apr 2005 00:02 GMT

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 :-)

Signature

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
[quoted text clipped - 53 lines]
>> > Thanks in Advance,
>> > Bob W.

Reply to this Message

cox - 20 Apr 2005 13:00 GMT

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

> Hi Bob
>
[quoted text clipped - 87 lines]
> >> > Thanks in Advance,
> >> > Bob W.

Reply to this Message