String limit? (1 Viewer)

[KeithIT]

Anyone know why, when I run this code, it will only create a string of a limited number of email addresses? Anytime I select more than five items in my list box and ask it to create the string and pass the variable to my procedure (see end of code snipet) it only gives me the username part of the first email address and nothing else, but when I select five or fewer it puts the full email addresses in separated by a comma (as the code tells it to). I can't find a limiting variable anywhere in my code but perhaps I'm looking past something?

Dim strList As String
Dim varItem As Variant
   
   ' Request to edit items selected in the list box
   ' If no items selected, then nothing to do
   If Me!lboNameList.ItemsSelected.Count = 0 Then Exit Sub

   ' Loop through the items selected collection
   For Each varItem In Me.lboNameList.ItemsSelected
      'Grab the email column (second column) for each selected item
      strList = strList & "," & Me.lboNameList.Column(1, varItem)
   Next varItem
    strList = Mid(strList, 2)

CreateMailandAttachFileAdr (strList)

Any help would be greately appreciated. Thanx!

 

[modest]

    For intCurrentRow = 0 To Me.lboNameList.ListCount - 1
        If Me.lboNameList.Selected(intCurrentRow) Then
            If Not strList = "" Then
                strList = strList & "," & Me.lboNameList.Column(1, intCurrentRow)
            Else
                strList = Me.lboNameList.Column(1, intCurrentRow)
        End If
    Next intCurrentRow

For starters, I'd condition the string first so you don't start off with a ","

**Edit:**
I'm guessing you were using strList = Mid(strList, 2) to get rid of it. Either way would work. Just how long are your email addresses?

 

[Mile-O]

A string variable can hold up to 255 characters.

Public Function AddString()
    Dim s As String
    Dim i As Integer
    Dim z As Long
    s = "****************************************************************************************************"
    For i = 1 To 25000
        s = s & s
        z = Len(s)
        Debug.Print s
    Next
    
End Function

It finally produces an error at about 104,857,600 asterisks in the string.

 

[KeithIT]

Okay, so now that we know it isn't the length of the string (Each email address is between 7 and 40 characters long, there are around 100 email addresses so no more than 4000 characters in the string, plus 99 commas and spaces, so figure no more than 5000 character in the string at any one time, and if the string can hold over 104 million...)

Any ideas what might be causing the problem?

 

[modest]

SJ, you are correct. That was a brain fart on my end. I was thinking Text type for a table. I did realize the mistake, however around lunch time my company must run reports because the network gets really slow.

I think a fixed length string holds up to 65536 (or around there 2^16) and a variable length string holds over 2 billion (2^31)... (I may have this switched around).

Regardless, an overflow should not have been the problem.

 

[modest]

Keith, post your database. The error is either in the selected value, or the loop itself.

 

[simongallop]

sounds like problem with the data. Try cleaning it up before adding to the string so something like:

strList = strList & "," & Trim(Me.lboNameList.Column(1, varItem))

 

[KeithIT]

I'll be happy to post the database itself, however a lot of the information is very sensitive, so I have to spend some time weeding out what can and can not be included.

I have been having another issue (wow, that was poor English) with the same form and listbox, which may help to discover the problem with the email addresses. In the same form, the same box I have a selectall button that uses the following code to, well, select all

    Dim i As Long
    
    If lboNameList.ListCount > 0 Then
        For i = 0 To Me.lboNameList - 1
            If Me.lboNameList.Selected(i) = True Then
            Else
            Me.lboNameList.Selected(i) = True
            End If
        Next
    End If

Now everytime I try to use this button, reglardless of whether or not something is selected already in the box, I get a run-time error '94', invalid use of Null. I've tried to debug this and I get the error on the "For i=0 To Me.lboNameList - 1" line. When I let the mouse hover over the Me.lboNameList portion of the line I get that little pop-up message that says "Me.lboNameList = Null".

Any thoughts?

Would it be helpful if I just posted the database without the data or is it possibly a problem in the data itself?

 

[modest]

Me.lboNameList.ItemsSelected - 1 or
Me.lboNameList.ListCount - 1

Choose the one that applies

 

[simongallop]

or

Me.lboNameList.ListCount - 1

 

[KeithIT]

Okay, now I'm getting an error on the .ItemsSelected portion that says "argument not optional".

 

[KeithIT]

Thank you all so much for your help so far. This darn form is giving me more problems that it's probably worth. I tried to use the Trim() to clean up the data, and I'm still having the same problem with the email addresses. It only seems to be hanging up on certain addresses, but only if they are part of a very large string, if they are part of a smaller string or by themselves, they work just fine.

 

[modest]

Keith,

Go back and look at the second post of this thread (it is my own). It shows you the the loop that you're trying to do.

secondly, you dont need:
If lboNameList.ListCount > 0 Then
The For loop takes care of this.

 

[KeithIT]

Modest,
Still getting the same error messages. I'm going to post the database later this evening, so hopefully something I'm either not posting here, or something I think i did that I didn't do will hit someone and it'll start to work right.

I do appreciate everyone's patience, I know it's frustrating to keep trying to help someone and not have it work, and not it make any sense because it all should be working.

It's probably either data or form structure that's causing me problems, but either way I'll post the db a little later on.

Thanks all!

 

[modest]

That's because I posted the wrong code. Should be: Me.lboNameList.ItemsSelected.Count - 1

but this only returns a count of the number of items selected. you probably want the listcount (count of all the items in the list)

It looks like you're just trying to select all the items. If that's the case I've edited your code to this below:

'selects all
    Dim i As Long
    
    For i = 0 To Me.lboNameListoNameList.ListCount - 1
        If Me.lboNameList.Selected(i) Then
        Else
            Me.lboNameList.Selected(i) = True
        End If
    Next

However to be more efficient you should just query your table. But to make "your" code better I'd also rearrange the if-statement.

'selects all
    Dim i As Long
    
    For i = 0 To Me.lboNameListoNameList.ListCount - 1
        If Not Me.lboNameList.Selected(i) Then
            Me.lboNameList.Selected(i) = True
        End If
    Next

 

[Rich]

What exactly does your code do, why are you not just selecting the PK from your table?

 

[KeithIT]

Well, for two reasons:

one is I'm not that sophisticated with my code yet, so I wasn't able to find a way to query the table and then create the string off of the email addresses located there, and also because I still haven't begun to think about the simplest way to do things with the database (i'm still getting used to what is possible and figuring how to do it, without finding a way to reduce the steps necessary to accomplish the task).

Looks like in addition to learning more about code I need to start thinking differently about my designs and the way the features of the db will function.

My code scans through a listbox that is populated based on two drop down boxes (date and location) and then creates a string from the email addresses of the participants selected from the populated listbox. It then takes that string and passes it to a procedure that sets up a lotus notes email and puts the string in the Bcc: line.

I'm going to post the db tonight once I get home. I just have to remove some information on grants and budgets (which needs to go in a new table anyway) and then I'll put it up here.

 

[KeithIT]

BTW: the select all works, thank you!!!

 

[modest]

Glad to hear you got what you want. I'm also glad to see you getting into things.

You'll soon find there are many ways to get something done and not necessarily a "right" way. That being said, as a programmer you should consider a few things:
1 - Which way is easiest/most time efficient
2 - Which way is most resource efficient
3 - Which way is easier to understand
--------------------------------------------
That being said there is no right way. As you become a better programmer, your become quicker in producing "harder/slower" code. If you make scripts that you can re-use, you'll find that taking time to do it right the first time will be better in the long run.

You'll also see that the most resource efficient way isn't always best, as it would take too long to get an ad-hoc or quick-fix to something you need right away. If resources were the main concern, we would all be doing this in assembly or even binary =). For small databases, resources aren't even a factor (but you will notice a difference in timing with larger databases/programs when you don't choose the most resource-efficient path).

The "easier to understand" way is just that, it's writing our your code to make it easy to comprehend - it is the way you've written your code so that you can understand it logically (not having goto's all over the place). This is very helpful when you always go back and tweak code that you have done before. It is especially helpful when others look at what you have written (you'll understand when you go back to what others have written). This can save you time when you need to make changes.

 

[KeithIT]

Alright, in order to paste the database for you to see if my data is the problem, I would, obviously, need to leave most of the data in place. However, the data that I have is all confidential, which leaves me in quite the bind. I can not paste the database with the existing data, but I need to post something in order for you to take a look at it. Any suggestions?