Results 1 to 4 of 4
  1. #1
    ExcessionOCP is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    May 2023

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

    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.



  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Kelowna, BC, Canada
    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.

    Vlad Cucinschi
    MS Access Developer

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    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.

    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).
    Attached Files Attached Files

  4. #4
    ExcessionOCP is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    May 2023
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-18-2023, 06:20 PM
  2. Calculated Column
    By Tuckejam in forum Queries
    Replies: 3
    Last Post: 11-08-2020, 09:44 AM
  3. Referencing specific column in combo box
    By Glenn_Suggs in forum Forms
    Replies: 3
    Last Post: 10-30-2018, 05:59 AM
  4. HELP Referencing Columns in a List Box
    By PorscheMan in forum Queries
    Replies: 4
    Last Post: 07-22-2013, 09:31 AM
  5. Referencing drop down list from form
    By akyramid in forum Queries
    Replies: 1
    Last Post: 03-01-2011, 05:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums