use DSum in multi-value field

[amy1]

Hello everyone,

I'm new here and new to Access2007 as well!

I have a multi-value field in a form, and would like to calculate the total of the selected values in this field and place the total in a textbox.

what I know is that should use DSum function. but I have no clue on how to use it or even where (in the textbox or multi-value field)?!!

so a detailed answer would be very appreciated.

thanks in advance

[jimatqsi]

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]

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]

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]

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]

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 ..

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]

@jimatqsi
Nice solution Jim.

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

[jimatqsi]

@jimatqsi
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]

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]

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]

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]

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

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

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]

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]

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]

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!!

[jimatqsi]

Those lines get skipped because the IF test is failing. Listcount-1 is less than 0 so it goes to the end of the IF/THEN test.

So let's try some creative problem solving. Add this code at line 10, right before the first line that does not execute.

me!lookup1.backcolor = vbred
msgbox me!lookup1.listcount & vbcrlf & _
me!lookup1.rowsource & vbcrlf & _
me!lookup1.Column(1,0)& vbcrlf & _
me!lookup1.Column(1,1)& vbcrlf 

plus whatever else you think it might be interesting to look in this message box.

You don't really need the msgbox. Do you know how to use the immediate window? You can type control/g when you are stopped in the debugger and you'll open the immediage window. There, type ?i to see the value of the i variable, or ?me!lookup1.column(1,0) to see the value of that column and so on.

The vbred line is just to turn that listbox red so you can verify it is the particular listbox you mean.

In Line 9 I would change lookup1 to me!lookup1. I'm not sure if it is required or not.

Also, please change line 36 so that is no longer a comment. If you are hitting an error you'll never see the msgbox display telling you hit an error. That's probably the first, best thing you can do to solve this.

Jim