Hi thank you for using JustAnswer.com. It will be my pleasure to assist you today. I will re-create your list above and post back with the solution. PS: I know the books don't offer much for multivalue fields and they are tricky but a little vba should do the trick
Can you provide that exact table names and Field names, this way I can write a custom fuction whithout you having to change them. Also the Bound column in your multi-value lookup field would be helpful
I don't want to make assumptions.
Also just an FYI, Access doesn't suppot multi-value fields in calculated field expressions, so the solution is to trigger a function in vba and populate the totals.
I saw phone request but it wouldn't let me accept. I am finalizing the function now should have result shortly
I proposed same offer Offer proposed: Live discussion via phone (We can have a live discussion via phone to make it easier and faster to get all the information you need. 1-***-***-****), $10
See code below: I will also provide a sample db with the code for you.
Dim dbs As DatabaseDim sqlTable As StringDim rstTable As RecordsetDim rstMultiValueField As RecordsetDim sqlPartPrice As StringDim rstPartPrice As RecordsetDim InventoryID As LongDim TotalPrice As Double
Set dbs = CurrentDb 'Selects All Fields of table that contains Multi-Value Field sqlTable = "SELECT * FROM [Build Sheet]" Set rstTable = dbs.OpenRecordset(sqlTable, dbOpenDynaset)'Loops through all records of table that contains Multi-Value Field Do While Not rstTable.EOF'Sets Multi-Value Field equal to a recordset(list of prices selected) Set rstMultiValueField = rstTable("Parts List").Value'Initialize Total Price (in case no items selected) TotalPrice = 0'Loops through Selected Values of Multi=Value Field Do While Not rstMultiValueField.EOFInventoryID = rstMultiValueField.Fields(0).Value sqlPartPrice = "SELECT Inventory.Price " & _ "FROM Inventory " & _ "WHERE (Inventory.[Part Number]=" & InventoryID & ")" Set rstPartPrice = dbs.OpenRecordset(sqlPartPrice, dbOpenDynaset)If rstPartPrice.RecordCount > 0 Then TotalPrice = TotalPrice + rstPartPrice("Price").Value End IfrstMultiValueField.MoveNext Loop'Edits Total Field rstTable.Edit rstTable("Total Price").Value = TotalPrice rstTable.UpdaterstTable.MoveNext Loop
Also here is a link to the database i mocked up. The macro calls the function that calculates total price. See Link Below