468,539 Members | 1,473 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

use DSum in multi-value field

5
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
Jun 10 '10 #1
15 4559
jimatqsi
1,255 Expert 1GB
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
Jun 10 '10 #2
amy1
5
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,
Jun 11 '10 #3
MMcCarthy
14,534 Expert Mod 8TB
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.
Jun 11 '10 #4
jimatqsi
1,255 Expert 1GB
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
Jun 11 '10 #5
jimatqsi
1,255 Expert 1GB
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 ..

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGo_Click()
  2.     Dim i As Long, msg As String, Check As String
  3.  
  4.      'Generate a list of the selected items
  5.    On Error GoTo cmdGo_Click_Error
  6.     msg = ""
  7.     Me!txtSum = 0
  8.     With listbox1
  9.         For i = 0 To .ListCount - 1
  10.             If .Selected(i) Then
  11.                 msg = msg & .Column(1, i)
  12.                 Me!txtSum = Nz(Me!txtSum) + .Column(1, i)
  13.             '    msg = msg & .Column(0, i)  & " " & .Column(1, i) & vbNewLine
  14.             End If
  15.         Next i
  16.     End With
  17.  
  18.     If msg = vbNullString Then
  19.          'If nothing was selected, tell user and let them try again
  20.         MsgBox "Nothing was selected!  Please make a selection!"
  21.         Exit Sub
  22.     Else
  23.          'Ask the user if they are happy with their selection(s)
  24.         MsgBox "You selected:" & vbNewLine & msg & vbNewLine
  25.     End If
  26.  
  27.  
  28.    On Error GoTo 0
  29.    Exit Sub
  30.  
  31. cmdGo_Click_Error:
  32.     cswLogError Application.CurrentObjectName, "Error_SortCodeHeader2_Format", Now, Err.Number, Err.Description
  33.     'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdGo_Click of VBA Document Form_Form2"
  34.     Resume Next
  35.  
  36. End Sub
  37.  
Jim
Jun 11 '10 #6
MMcCarthy
14,534 Expert Mod 8TB
@jimatqsi
Nice solution Jim.

So essentially you add the selected values to a total as you send them to the multivalue text field.
Jun 11 '10 #7
jimatqsi
1,255 Expert 1GB
@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.
Jun 11 '10 #8
amy1
5
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,
Jun 15 '10 #9
jimatqsi
1,255 Expert 1GB
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
Jun 15 '10 #10
jimatqsi
1,255 Expert 1GB
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
Jun 15 '10 #11
amy1
5
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command48_Click()
  2.  
  3.     Dim i As Long, msg As String, Check As String
  4.  
  5.      'Generate a list of the selected items
  6.    On Error GoTo Command48_Click_Error
  7.     msg = ""
  8.     Me!txtSum = 0
  9.     With lookup1
  10.         For i = 0 To .ListCount - 1
  11.             If .Selected(i) Then
  12.                 msg = msg & .Column(1, i)
  13.                 Me!txtSum = Nz(Me!txtSum) + .Column(1, i)
  14.                 'msg = msg & .Column(0, i) & " " & .Column(1, i) & vbNewLine
  15.  
  16.             End If
  17.         Next i
  18.     End With
  19.  
  20.     If msg = vbNullString Then
  21.          'If nothing was selected, tell user and let them try again
  22.         MsgBox "Nothing was selected!  Please make a selection!"
  23.        Exit Sub
  24.    Else
  25.          'Ask the user if they are happy with their selection(s)
  26.       MsgBox "You selected:" & vbNewLine & msg & vbNewLine
  27.     End If
  28.  
  29.  
  30.    On Error GoTo 0
  31.    Exit Sub
  32.  
  33. Command48_Click_Error:
  34.     'cswLogError Application.CurrentObjectName, "Error_SortCodeHeader2_Format", Now, Err.Number, Err.Description
  35.     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command48_Click of VBA Document Form_Form2"
  36.     Resume Next
  37.  
  38.  
  39. 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
Expand|Select|Wrap|Line Numbers
  1. Private Sub lookup1_AfterUpdate()
  2.  
  3. Dim i As Long, msg As String, Check As String
  4.  
  5.      'Generate a list of the selected items
  6.    'On Error GoTo cmdGo_Click_Error
  7.     msg = ""
  8.     Me!txtSum = 0
  9.     With lookup1
  10.         For i = 0 To .ListCount - 1
  11.             If .Selected(i) Then
  12.                 msg = msg & .Column(1, i)
  13.  
  14.                 Me!txtSum = Nz(Me!txtSum) + .Column(1, i)
  15.             '    msg = msg & .Column(0, i)  & " " & .Column(1, i) & vbNewLine
  16.             End If
  17.         Next i
  18.     End With
  19.  
  20.     If msg = vbNullString Then
  21.          'If nothing was selected, tell user and let them try again
  22.         MsgBox "Nothing was selected!  Please make a selection!"
  23.         Exit Sub
  24.     Else
  25.          'Ask the user if they are happy with their selection(s)
  26.         MsgBox "You selected:" & vbNewLine & msg & vbNewLine
  27.     End If
  28.  
  29.  
  30.  
  31. On Error GoTo 0
  32.    Exit Sub
  33.  
  34. 'cmdGo_Click_Error:
  35.     'cswLogError Application.CurrentObjectName, "Error_SortCodeHeader2_Format", Now, Err.Number, Err.Description
  36.     'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdGo_Click of VBA Document Form_Form2"
  37.    Resume Next
  38.  
  39.  
  40.  
  41. End Sub
  42.  

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

regards,
Jun 15 '10 #12
jimatqsi
1,255 Expert 1GB
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
Jun 15 '10 #13
jimatqsi
1,255 Expert 1GB
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
Jun 15 '10 #14
amy1
5
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!!
Jun 16 '10 #15
jimatqsi
1,255 Expert 1GB
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.
Expand|Select|Wrap|Line Numbers
  1. me!lookup1.backcolor = vbred
  2. msgbox me!lookup1.listcount & vbcrlf & _
  3. me!lookup1.rowsource & vbcrlf & _
  4. me!lookup1.Column(1,0)& vbcrlf & _
  5. 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
Jun 16 '10 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Rolan | last post: by
4 posts views Thread by eliffman | last post: by
1 post views Thread by gbb0330 | last post: by
1 post views Thread by Sheldon Mopes | last post: by
6 posts views Thread by pixie | last post: by
2 posts views Thread by esmith7912 | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.