Results 1 to 2 of 2
  1. #1
    TinaCa is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    87

    Summing two list boxes on a form

    on the AfterUpdate event of a combo box i do a refresh which updates the two listboxes on my form with a single value (a sum from the queries used as their individual recordsources). I need to add those two values together.

    I found in another post that access sees list box values as strings so I have set variable to convert the strings to currency.

    However, when the list boxes get their values (after the refresh) the text box is not updating with sum.

    lstbox1.recordsource = ELECT Sum(([ExtraCost]*[ExtrasQty])) AS ExtraTotal FROM CompanyInformation LEFT JOIN tblCompanyExtras ON CompanyInformation.CompanyAccountNo = tblCompanyExtras.CompanyId WHERE (((tblCompanyExtras.DateBilled) Is Null) And ((CompanyInformation.CompanyBillingDate)=Forms!frm CreateInvoices!cboPrntInv));

    Lstbox2.recordsource = SELECT Sum(CompanyInformation.CompanyContractAmt) AS SumOfCompanyContractAmt FROM CompanyInformation WHERE (((CompanyInformation.CompanyBillingDate)=[Forms]![frmCreateInvoices]![cboPrntInv]));

    I was using


    Dim MAmt as currency
    dim Eamt as currency

    MAmt = me.lstbox1
    EAmt = me.lstbox2


    I need the textbox to =(sum MAmt + EAmt).


    It is not updating after the refresh On what event do I need to put the sum function or is there another way entirely?
    OK part of the problem solved - I need to use me.lstbox1.itemdata.

    Next issue the listboxes store the data as a string. I have tried CInt(lstbox1.itemdata) to set my variable value but I am getting an overflow errror.

    Now on what event to put it?

  2. #2
    TinaCa is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    87
    OK here is the solution.

    First I needed to select the correct data in the box i.e. me.LstBox.ItemData(0).

    Then I needed to convert it to a currency so the calculation for the text box looks like this.

    me.txtTotal = CCur(me.lstBox1.Itemdata(0)) + CCur(me.lstBox2.ItemData(0))

    Hope this helps some others I've been fighting with this for several days.

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

Similar Threads

  1. 2 multi select list boxes on one search form
    By woodey2002 in forum Forms
    Replies: 2
    Last Post: 11-05-2010, 12:44 PM
  2. Search form with list boxes
    By scottay in forum Programming
    Replies: 15
    Last Post: 07-27-2010, 09:28 AM
  3. List Boxes.
    By Willtc in forum Programming
    Replies: 2
    Last Post: 02-12-2010, 04:12 AM
  4. Replies: 1
    Last Post: 07-30-2009, 12:54 PM
  5. List box to populate other list boxes
    By Nathan in forum Forms
    Replies: 0
    Last Post: 03-03-2009, 07:22 PM

Tags for this Thread

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