Sum of Multiselect Listbox (Access 97/2000)

Topic

New Reply

(Edited by HansV to activate link to post – see Help 19)

You very kindly gave me the coding for my Multiselect Listbox problem in post 250291 but I have progressed this a bit and need further help. I have added an extra column to my listbox column, containing a figure per record. I want to SUM these figures, based on the selections made by the user. (I’ve tried achieving this by using DSUM or DLOOKUP on the SUM field in a query but am obviously not getting the syntax right so would rather not go down this route!) When I use the code you gave me for adding the postcodes together as a string, it does the same for the numbers, e.g. “7702, 6702”, rather than “14404” which is what I need. I assume it needs completely different code to handle numbers, rather than text? Once I have managed to get the sum of figures, I need to store these (yes, I really do!) so that I have a record of figures at that time, not the current situation. I want to be able to record “this particular letter went to these areas, on that date, with a total mailing quantity of X”

Perhaps I’m going about this the wrong way. All the addresses are stored per area, in approx. 130 separate Excel tables. I have one separate Excel file containing a non-blank record count for each address table. This record-count table is linked to a corresponding Access table which is where the figures in the multiselect listbox column come from. This is probably far more complicated than it needs to be but I don’t know how else to get the information. One major drawback is that every time I open the record count Excel file, it takes forever to open as it is linked to all 130 separate tables. I need the record counts to show current information for any new letter records added to the Access Letters table so I have linked, rather than embedded.

Any suggestions for a better way to do this would be appreciated, bearing in mind I’m not a code writer but can just about cut and paste what you give me!

Regards,
Sue

Reply | Quote

Replies

The code in the post you mention concatenates strings, i.e. puts them one behind the other. You can’t use that to add numbers, as you found out. You would just use + to add numbers, instead of & “, ” &, and you would use a variable of type Long (that is, a long integer) instead of a variable of type string, to store the result.

It would be far more efficient to store the addresses in one table, with an extra column to identify the area. In Excel, you would be limited to 65,536 rows; in Access, you can store many more records. if you have one table with all the addresses (plus an area indication), a simple Totals query would return the record count per area; this query could serve as Row Source of the list box, too.

Reply | Quote

Hans

Many thanks for your reply. Have tried various alterations to the code supplied earlier but it doesn’t now work – not surprising as I am just guessing at what I’m doing here! Please, please, could you correct the following so it does work:

Private Sub List91_Exit(Cancel As Integer)
Dim strPC As Integer
Dim i As Integer
‘ Loop through list items
For i = 0 To Me.List91.ListCount – 1
‘ Check if item is selected
If Me.List91.Selected(i) = True Then

strPC = strPC + Me.List91.ItemData(i)
End If
Next i

strPC = Mid(strPC, 1)
‘ Set PostCodes text box
Me.[Test Qty mailed] = strPC

End Sub

The [Test Qty Mailed] field is a number field.

I will give much thought to the option of storing all addresses in Access – I’m much happier with Access than Excel but others in the company are not!

Best regards
Sue

Reply | Quote

Hello Sue,

1. The statement strPC = Mid(strPC, 1) was meant to get rid of an extra comma in the concatenation of strings. You are not concatenating now, so this statement should be removed.

2. strPC is not a good name for two reasons: (a) The “str” prefix is commonly used to denote string variables, and you’re not working with strings now. ( It is always a good idea to give variables a more or less relevant name. The PC in strPC stood for Post Code. You’re calculating a sum now, so I would use something like lngSum declared as a Long. If you stop working on something for a few months, then return to it, or if you have to transfer your work to somebody else, variable names that refer to something completely different than their names indicate are very confusing. The same goes for List91 by the way – such a name does not indicate what it is for.

Here is a modified version of the code:

Private Sub List91_Exit(Cancel As Integer)
Dim lngSum As Long
Dim i As Integer
‘ Loop through list items
For i = 0 To Me.List91.ListCount – 1
‘ Check if item is selected
If Me.List91.Selected(i) = True Then
lngSum = lngSum + Me.List91.ItemData(i)
End If
Next i
‘ Set Quantity text box
Me.[Test Qty mailed] = lngSum
End Sub

Reply | Quote

Hi Hans

Thanks for your reply. I have substituted your code for my version (which is set as an On Exit Event Procedure in the listbox properties) but when I have made my selection and quit the control, I get an error message saying “Invalid use of Null”. The de###### opens up and highlights the following line:

lngSum = lngSum + Me.List91.ItemData(i)

Any clues as to what is wrong now?

Best regards
Sue

Reply | Quote

Try replacing ItemData(i) by Column(1, i)
This assumes that the column holding the record counts is the second column of the list box (the Column function starts counting columns and rows at 0, so 1 is the second column; adapt if necessary).

Reply | Quote

Hi Hans

I adapted your suggestion to “Column(2, i)” and it now works a treat. Thank you very much for all your help. Now the database can go “live” and the fun begins!

Thanks again, regards,
Sue

Reply | Quote