Calculate total value of items in a list box (1 Viewer)

[RodShinall]

I am a VBA novice and am trying to create a simple household inventory application for personal use. I have categorized household items into Appliances, Furniture, Housewares and so on. A table called tblItemInfo includes the fields Category, Item, Location, Cost, TaxRate, and DeliveryCost. My form contains an unbound text box called txtTotal with the expression =Sum([Cost]+([Cost]*[TaxRate])+[DeliveryCost]) as its control source which calculates the total value for all records. My form also contains a list box named lstSelectItem which displays item names. I populate this list box by selecting a category name from a combo box called cboSelectCategory. For example, when I select "Furniture" in the combo box the list box RowSource is set to display only items in the category of Furniture. I accomplish this by placing the following code in cboSelectCategory AfterUpdate event: lstSelectItem.RowSource = "SELECT AutoNumber, Item, Category FROM tblItemInfo WHERE (((Category) = ' " & cboSelectCategory.Value & " '))". This all works exactly as desired but when I select a category in cboSelectCategory I want txtTotal to calculate the total value for items only in the selected category and I can't figure out how to write the code that will do this. Any help would be greatly appreciated.

 

[Steve R.]

I assume that you want the total value displayed in a text box on the form that is close to the list box. This will allow you to use the DSUM function, see Access help.

Me.TextBoxName = Dsum(Expr, Domain, Criteria)

 

[smig]

it's totaly unreadable as it's all in one big block of text

 

[Steve R.]

I would assume that your table has several fields, at least one describing the item (sofa) and another field the price of that item ($200). You need to sum the fields containing the dollar value.

 

[RodShinall]

Thanks, Steve R.