Self-referencing list box with calculated column - is it possible?

[ExcessionOCP]

Hi All,

I'm not sure if what I want to do is possible, so I'm hoping one of you experts here can help.

I have a list box that shows a list of purchasing categories and their associated budgets. I have a separate table tracking the purchases that are made within each category. What I would like to do is have a third column in my list box that is a calculated column, that shows the sum of all spending within that category. I have written a query that returns the information that I need but I don't know how (or if it is possible) to get the listbox to show this as a separate column.

Additionally, the query that I have written uses hardcoded parameters as a test, but what I would like to do is use the returned value of the listbox (the bound column) as a parameter within the second query. So as the listbox is being built, it should return the first two columns of the query, then use the value of the first column in the query that subsequently populates the third column.



Am I mad for even thinking I can do this? If so, is there a far more sensible solution that I am missing?

Thanks all for any input you are able to offer.

Cheers,

Phil

[Gicu]

Should be very easy if you join the two queries on the purchasing categories field (you should probably make the join a left outer join with the arrow pointing towards the query that shows the sum of spending). Save this as a third query and use it as the row source for your listbox.

Cheers,

[kd2017]

See attached example.

Notice the column count and column widths property values for the listbox. This is how you set how many columns your listbox displays. The first column of the underlying query is the category ID field but I set the width to zero to hide it from the user. The listbox is bound to column 1 (the hidden column) so that when a user selects a row in the listbox the value of the listbox control will be the category ID value for that row. You can easily reference this value for other queries or in your code.

Study the aggregate queries used as the row source for the listbox and pay attention to the join properties.

[EDIT]
Regarding self referencing listbox. I don't think it's possible in the way that you describe (a control's row source referencing the same control's value).

[ExcessionOCP]

Thank you both!! That was exactly what I was looking for. I hadn't thought of creating separate queries and then a third query to query the two constituent queries. This has opened up a whole new world of possibilities for me!

Regarding the self-referencing bit, I don't need to do that, now that the query is essentially doing that for me.

Thanks again to the both of you, you are brilliant.