use DSum in multi-value field

**jimatqsi** · Jun 10 '10, 10:27 PM

I don't know what a "multi-value field" is. Please explain.

Are you adding multiple fields from one row to plug a value in a text box for that row? Or are you adding values from one column on multiple rows to get a bottom line total?

Jim

**amy1** · Jun 11 '10, 07:45 AM

multi-value field is a lookup column that allow you to select more than one value and store them in one field. I learnt about it from here: http://office.microsoft.com/en-us/ac...001233722.aspx

what I want to do is adding values from the same column on multiple rows and show the total in the textbox. obviously I want to add only the rows that are selected.

any idea on how to do this?

regards,

**MMcCarthy** · Jun 11 '10, 08:31 AM

The multivalue field allowed for in Access 2007 is designed for a specific purpose. It's not intended as something you use to calculate values on and should only ever be used in very limited circumstances. In reality it's just a form of listbox and has been designed specifically for use with sharepoint services to take advantage of some of their features.

However, if you want to preform any action on this list other that store it you can't. It's just not meant for that.

**jimatqsi** · Jun 11 '10, 12:04 PM

Hmmm, I know about that feature but I've never applied it. Couldn't something be done in the after update event to take the selections and do something with them (like add them together)?... it must be possible, otherwise, what's the point? I'm going to go spend 15 minutes trying something with that, if I have some success, I'll be back with the news.

Jim

**jimatqsi** · Jun 11 '10, 01:30 PM

Okay, finally I'm back. Here's how I did it. I put a multi-value listbox (listbox1) on a form, and I added an unbound textbox (txtSum), and a "Go" button. After selecting several rows in the listbox, I clicked the Go button and in the click event of the Go button I put code to add the sum of the selections and put that in the text box.

Note that my listbox is a 2-column list box. The value to be summed is in the 2nd column of each row, i.e column (1,rownumber).

Here's the code ..

Code:

Private Sub cmdGo_Click()
    Dim i As Long, msg As String, Check As String
     
     'Generate a list of the selected items
   On Error GoTo cmdGo_Click_Error
    msg = ""
    Me!txtSum = 0
    With listbox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                msg = msg & .Column(1, i)
                Me!txtSum = Nz(Me!txtSum) + .Column(1, i)
            '    msg = msg & .Column(0, i)  & " " & .Column(1, i) & vbNewLine
            End If
        Next i
    End With
     
    If msg = vbNullString Then
         'If nothing was selected, tell user and let them try again
        MsgBox "Nothing was selected!  Please make a selection!"
        Exit Sub
    Else
         'Ask the user if they are happy with their selection(s)
        MsgBox "You selected:" & vbNewLine & msg & vbNewLine
    End If
     

   On Error GoTo 0
   Exit Sub

cmdGo_Click_Error:
    cswLogError Application.CurrentObjectName, "Error_SortCodeHeader2_Format", Now, Err.Number, Err.Description
    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdGo_Click of VBA Document Form_Form2"
    Resume Next
     
End Sub

Jim

**MMcCarthy** · Jun 11 '10, 03:02 PM

Originally posted by jimatqsi

Okay, finally I'm back. Here's how I did it. I put a multi-value listbox (listbox1) on a form, and I added an unbound textbox (txtSum), and a "Go" button. After selecting several rows in the listbox, I clicked the Go button and in the click event of the Go button I put code to add the sum of the selections and put that in the text box.

Note that my listbox is a 2-column list box. The value to be summed is in the 2nd column of each row, i.e column (1,rownumber).

Here's the code ..

Code:

Private Sub cmdGo_Click()
    Dim i As Long, msg As String, Check As String
     
     'Generate a list of the selected items
   On Error GoTo cmdGo_Click_Error
    msg = ""
    Me!txtSum = 0
    With listbox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                msg = msg & .Column(1, i)
                Me!txtSum = Nz(Me!txtSum) + .Column(1, i)
            '    msg = msg & .Column(0, i)  & " " & .Column(1, i) & vbNewLine
            End If
        Next i
    End With
     
    If msg = vbNullString Then
         'If nothing was selected, tell user and let them try again
        MsgBox "Nothing was selected!  Please make a selection!"
        Exit Sub
    Else
         'Ask the user if they are happy with their selection(s)
        MsgBox "You selected:" & vbNewLine & msg & vbNewLine
    End If
     

   On Error GoTo 0
   Exit Sub

cmdGo_Click_Error:
    cswLogError Application.CurrentObjectName, "Error_SortCodeHeader2_Format", Now, Err.Number, Err.Description
    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdGo_Click of VBA Document Form_Form2"
    Resume Next
     
End Sub

Jim

Nice solution Jim.

So essentially you add the selected values to a total as you send them to the multivalue text field.

**jimatqsi** · Jun 11 '10, 03:12 PM

Originally posted by jimatqsi

Okay, finally I'm back. Here's how I did it. I put a multi-value listbox (listbox1) on a form, and I added an unbound textbox (txtSum), and a "Go" button. After selecting several rows in the listbox, I clicked the Go button and in the click event of the Go button I put code to add the sum of the selections and put that in the text box.

Note that my listbox is a 2-column list box. The value to be summed is in the 2nd column of each row, i.e column (1,rownumber).

Here's the code ..

Code:

Private Sub cmdGo_Click()
    Dim i As Long, msg As String, Check As String
     
     'Generate a list of the selected items
   On Error GoTo cmdGo_Click_Error
    msg = ""
    Me!txtSum = 0
    With listbox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                msg = msg & .Column(1, i)
                Me!txtSum = Nz(Me!txtSum) + .Column(1, i)
            '    msg = msg & .Column(0, i)  & " " & .Column(1, i) & vbNewLine
            End If
        Next i
    End With
     
    If msg = vbNullString Then
         'If nothing was selected, tell user and let them try again
        MsgBox "Nothing was selected!  Please make a selection!"
        Exit Sub
    Else
         'Ask the user if they are happy with their selection(s)
        MsgBox "You selected:" & vbNewLine & msg & vbNewLine
    End If
     

   On Error GoTo 0
   Exit Sub

cmdGo_Click_Error:
    cswLogError Application.CurrentObjectName, "Error_SortCodeHeader2_Format", Now, Err.Number, Err.Description
    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdGo_Click of VBA Document Form_Form2"
    Resume Next
     
End Sub

Jim

I give all the credit to Mr. Google, I simply applied what he helped me find.

I suppose one could put this logic in the After_Update event of the listbox and eliminate the Go button. It would be a little more automatic that way, but you'd want to eliminate the stupid message box.

**amy1** · Jun 15 '10, 01:09 PM

hello,

first thank you for the responses.

I tried the code you posted above, but it keeps giving me the error msg "nothing was selected", although I've selected multiple records.

I tried to play with the code and end up calculating the number of rows selected not the values!.

any suggestions?.

regards,

**jimatqsi** · Jun 15 '10, 01:32 PM

I'd suggest you step through it with the debugger first, to see what's happening. Then if you can't see the problem, I think we're going to have to see the code.

Jim

**jimatqsi** · Jun 15 '10, 01:34 PM

Make sure you don't try to use my error handling verbatim. The function cswLogError is a custom error handler in my system, so you should just eliminate that line and uncomment the Msgbox call; use Msgbox to display any errors you happen to hit.

Jim

**amy1** · Jun 15 '10, 02:34 PM

okay I used two ways and both aren't working.

-the first is the one you suggested with the Go button. every time I select records and press the button, the msg "nothing is selected, please make selection" appears. I don't know why it doesn't take my selections?
here is the code

Code:

Private Sub Command48_Click()

    Dim i As Long, msg As String, Check As String
 
     'Generate a list of the selected items
   On Error GoTo Command48_Click_Error
    msg = ""
    Me!txtSum = 0
    With lookup1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                msg = msg & .Column(1, i)
                Me!txtSum = Nz(Me!txtSum) + .Column(1, i)
                'msg = msg & .Column(0, i) & " " & .Column(1, i) & vbNewLine
            
            End If
        Next i
    End With
 
    If msg = vbNullString Then
         'If nothing was selected, tell user and let them try again
        MsgBox "Nothing was selected!  Please make a selection!"
       Exit Sub
   Else
         'Ask the user if they are happy with their selection(s)
      MsgBox "You selected:" & vbNewLine & msg & vbNewLine
    End If
 
 
   On Error GoTo 0
   Exit Sub
 
Command48_Click_Error:
    'cswLogError Application.CurrentObjectName, "Error_SortCodeHeader2_Format", Now, Err.Number, Err.Description
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command48_Click of VBA Document Form_Form2"
    Resume Next
 
 
End Sub

-the second way is using after_update function with similar code and without the button. this time the code works by adding up the row numbers (i.e. if I selected row #1 and row #5 the value displayed in the textbox would be 6).
here is the code

Code:

Private Sub lookup1_AfterUpdate()

Dim i As Long, msg As String, Check As String
 
     'Generate a list of the selected items
   'On Error GoTo cmdGo_Click_Error
    msg = ""
    Me!txtSum = 0
    With lookup1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                msg = msg & .Column(1, i)
                
                Me!txtSum = Nz(Me!txtSum) + .Column(1, i)
            '    msg = msg & .Column(0, i)  & " " & .Column(1, i) & vbNewLine
            End If
        Next i
    End With
 
    If msg = vbNullString Then
         'If nothing was selected, tell user and let them try again
        MsgBox "Nothing was selected!  Please make a selection!"
        Exit Sub
    Else
         'Ask the user if they are happy with their selection(s)
        MsgBox "You selected:" & vbNewLine & msg & vbNewLine
    End If



On Error GoTo 0
   Exit Sub
 
'cmdGo_Click_Error:
    'cswLogError Application.CurrentObjectName, "Error_SortCodeHeader2_Format", Now, Err.Number, Err.Description
    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdGo_Click of VBA Document Form_Form2"
   Resume Next
 


End Sub

hope I'm not being pushy about it, buy it's my first encounter with Access and any help is highly appreciated.

regards,

**jimatqsi** · Jun 15 '10, 05:57 PM

How many columns are in your listbox? Perhaps you have only one column in the listbox.

Do you realize that .Column(1, i) refers to the second colum of the listbox, not the first? So if your listbox only has 1 column, this will throw an error when you hit the line that builds the msg variable (because it refers to column 1 which does not exist), so nothing ever goes into it.

AND your msgbox routine to display the error is still commented out, so you don't get any error displayed to warn you something is going on.

Jim

**jimatqsi** · Jun 15 '10, 06:00 PM

Now I see you have error handling in the GO button version but not the other.

Have you stepped through with the debugger yet? What happens when you hit that line of code msg = msg & .Column(1, i)?

Jim

**amy1** · Jun 16 '10, 01:16 PM

I have 5 columns in the field, and want to add up the second one which would be column #1.

I went through the debugger and noticed that the lines between 11 and 16 are not processed even after I put breakpoint for "msg = msg & .Column(1, i)" and the line that follows. they just get skipped out!!. I think the system doesn't read these lines for some reason.

now for the After_update version, the same line of code is processed but gives wrong results.

hope it make sense!!

use DSum in multi-value field

use DSum in multi-value field

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment