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?